• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

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

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
1 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now