[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • 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
     
    Beep
    MsgBox "UDL Metrics has been exported to Excel", vbOKOnly, ""
     
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.CreateTextFile(Replace(ExportedFile, ".XLS", ".TXT"), True)
    ts.Close
    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.Columns.AutoFit
 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
0
zimmer9
Asked:
zimmer9
1 Solution
 
stevbeCommented:
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
xlApp.Quit
End Sub
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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