How to prevent an Excel file that gets created from displaying on the screen ?

Posted on 2007-07-20
Last Modified: 2013-12-05
I am using Access to develop an Application using Access as the front end and SQL Server as the back end database. In the following VBA code, I export an Access table to Excel and the application opens the Excel file.

As part of the process, a subroutine named StartMetrics is called to modify the spreadsheet to format some columns.

Is there a way to modify this code to have the Excel file just get created WITHOUT opening the Excel file
on the screen ?

ExportedFile = "\\nydfs1\root\lib\CONTROLLERS\IIG\CASH_CONTROL\ccshared\AbandonedProperty\UDL\Access\Reconcile\UDL.METRICS.TALLINT1.TALLINT1" & "." & intYearSP & "." & Format(Now, "mmddhhnnss") & ".ARD.OUT.XLS"
    DoCmd.TransferSpreadsheet acExport, 8, "dbo.tblUDLMetrics", ExportedFile, True, ""
    If isFileExist(ExportedFile) Then StartMetrics ExportedFile
    strFile = Left(ExportedFile, Len(ExportedFile) - 4)
    FileCopy ExportedFile, strFile
    strFile = Left(ExportedFile, Len(ExportedFile) - 8)
    intFile = VBA.FreeFile
    Open strFile For Append Access Write Lock Write As #intFile
    Close #intFile
    strFile = Left(ExportedFile, Len(ExportedFile) - 8) & ".IND"
    intFile = VBA.FreeFile
    Open strFile For Append Access Write Lock Write As #intFile
    Print #intFile, "CODEPAGE:850"
    Print #intFile, "GROUP_FIELD_NAME:REPTID"
    Close #intFile
    MsgBox "UDL Metrics has been exported to Excel", vbOKOnly, ""
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.CreateTextFile(Replace(ExportedFile, ".XLS", ".TXT"), True)
    Set ts = Nothing
    Set fso = Nothing
    DoCmd.Hourglass False

Private Sub StartMetrics(filename)
 Dim xlApp As Excel.Application
 Dim xlWB As Excel.Workbook
 Dim xlWS As Excel.Worksheet

 'open excel template
 Set xlApp = New Excel.Application
 xlApp.Visible = True
 Set xlWB = xlApp.Workbooks.Open(filename)
 Set xlWS = xlWB.Worksheets(1)
 xlWS.Range("B2:B65535").NumberFormat = "#,##0"
 xlWS.Range("C2:C65535").NumberFormat = "#,##0.00"
 xlWS.Range("C2:C65535").Value = xlWS.Range("C2:C65535").Value
 xlWS.Rows("1:2").Insert Shift:=xlDown
 xlWS.Range("A1") = "UDL Metrics Report For the Month Ending " & Format(DateSerial(Year(Now()), Month(Now()), 1) - 1, "MM/DD/YYYY")
 xlApp.ScreenUpdating = True
End Sub
Question by:zimmer9
    1 Comment
    LVL 39

    Accepted Solution

    if you do not tell excel it is visible, it won't be ... drop this line of code ...

    xlApp.Visible = True

    you then need to save and close the file and .quit the excel app object ... add this to the very end of your procedure ...

    xlWB.Close True
    Set xlWB = Nothing
    End Sub

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now