Using Excel template results in 'hiden' workbook

[Alc]

I have a an Excel workbook with two worksheets. One contains two charts/graphs and the other contains the data used as the basis for the charts. Opening this file displays both worksheets and allows editing without problems.

I wrote some VBA to make a copy of the workbook under a new name, then update the data. This works perfectly BUT when the new file is opened, the relevant workbook is hidden. I can unhide it manually and everything has been updated correctly, I just don't know why it's being hidden in the first place.

By a process of elimination, I know the actual work done inside the open sheet isn't causing the problem, so I've cut out those lines of code. Something in the following is the issue, but I'll be damned if I can see what.

I've used this code, or some version thereof, on many occasions with Office 2003 and not had any problems. I'm now using 2007, if that makes any difference. Any ideas?

Private Sub Command49_Click()
   Dim strSQL As String
   Dim Rst As Recordset
   Dim xlApp As Excel.Application
   Dim xlBook As Excel.Workbook
   Dim xlSheet As Excel.Worksheet
   Dim strCell As String
   Dim strSource As String
   Dim strDisposition As String
   Dim liValue As Integer
   Dim llPct As Long
   Dim strMsg As String
   Dim strTitle As String
   Dim strResponse As String
   Dim strReportTemplate As String

   DoCmd.SetWarnings False

   strCellRefVal = ""
   strCellRefPct = ""

   strReportTemplate = CurrentProject.Path & "\Source_and_Disposition_Charts.xlsx"
   strFileName = CurrentProject.Path & "\Source and Disposition Report - " & Format(Date, "YYYY-MMM-DD") & ".xlsx"
   If FileExists(strFileName) Then
       Kill strFileName
   End If
   FileCopy strReportTemplate, strFileName
   Set xlApp = CreateObject("Excel.Application")
   Set xlBook = GetObject(strFileName)
   Set xlSheet = xlBook.Sheets("Data")

{other code goes here, but even if I comment it all out the same problem occurs}

   xlBook.Save
   xlBook.Close
   Set xlApp = Nothing
   Set xlBook = Nothing
   Set xlSheet = Nothing
   strMsg = "File " & strFileName & " created"
   strTitle = "Process complete"
   strResponse = MsgBox(strMsg, vbOKOnly, strTitle)

   DoCmd.SetWarnings True
End Sub

 

[MarkK]

xlApp is never made visible, nor is it's Application.Quit method ever called. So yes, looking at this code, I would expect it to leave a open a hidden instance of Excel. Note that

Set xlApp = Nothing

does not quit the application, it only sets the pointer variable, xlApp, to no longer point to the object.

 

[Alc]

Thanks for the help.
I changed the code as follows with no difference.
Am I just setting toe properties in the wrong places?

Forgive my ignorance, but don't understand why this happens now and when I've used it in the past, the new workbook displayed the various sheets when it was opened? Did something change in v2007?

 Private Sub Command49_Click()
    Dim strSQL As String
    Dim Rst As Recordset
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim strCell As String
    Dim strSource As String
    Dim strDisposition As String
    Dim liValue As Integer
    Dim llPct As Long
    Dim strMsg As String
    Dim strTitle As String
    Dim strResponse As String
    Dim strReportTemplate As String
    
    DoCmd.SetWarnings False
    
    strCellRefVal = ""
    strCellRefPct = ""
    
    strReportTemplate = CurrentProject.Path & "\Source_and_Disposition_Charts.xlsx"
    strFileName = CurrentProject.Path & "\Source and Disposition Report - " & Format(Date, "YYYY-MMM-DD") & ".xlsx"
    If Dir(strFileName) <> vbNullString Then
        Kill strFileName
    End If
    FileCopy strReportTemplate, strFileName
     Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlBook = GetObject(strFileName)
    Set xlSheet = xlBook.Sheets("Data")

    xlBook.Save
    xlBook.Close
    Set xlSheet = Nothing
    Set xlBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
     strMsg = "File " & strFileName & " created"
    strTitle = "Process complete"
    strResponse = MsgBox(strMsg, vbOKOnly, strTitle)
    
    DoCmd.SetWarnings True
End Sub

 

[MarkK]

Here's the part I don't get, where you create a new Excel instance, but you don't use it to open the workbook . . .

     Set xlApp = CreateObject("Excel.Application")
     Set xlBook = GetObject(strFileName)

I would expect to see this . . .

     Set xlApp = CreateObject("Excel.Application")
     Set xlBook = xlApp.Workbooks.Open(strFileName)

So your code creates an instance of Excel at xlApp, but never explicitly uses it, and since it's easy to have multiple instances of Excel open on one machine, therefore your GetObject() call is ambiguous. Which instance does it use? So if your user has Excel open at the time your code runs, what happens??? What happens to the hidden application at xlApp?

 

[Alc]

Short answer: I've only tested this while Excel wasn't already open, so it never left an instance 'hanging'. I'm sure it would have done at some point.

I changed this line and all now works:
Set xlBook = xlApp.Workbooks.Open(strFileName)

Thanks very much for the help. So much clearer having it explained by someone who knows what they're talking about.

Full code underneath, in case it helps anybody else.

     Dim strSQL As String
    Dim Rst As Recordset
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim strCell As String
    Dim strSource As String
    Dim strDisposition As String
    Dim liValue As Integer
    Dim llPct As Long
    Dim strMsg As String
    Dim strTitle As String
    Dim strResponse As String
    Dim strReportTemplate As String
    
    DoCmd.SetWarnings False
    
    strReportTemplate = CurrentProject.Path & "\Source_and_Disposition_Charts.xlsx"
    strFileName = CurrentProject.Path & "\Source and Disposition Report - " & Format(Date, "YYYY-MMM-DD") & ".xlsx"
    If Dir(strFileName) <> vbNullString Then
        Kill strFileName
    End If
    FileCopy strReportTemplate, strFileName
     Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(strFileName)
    Set xlSheet = xlBook.Sheets("Data")

     {formatting etc. done here}
 
    xlBook.Save
    xlBook.Close
    Set xlSheet = Nothing
    Set xlBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
     strMsg = "File " & strFileName & " created"
    strTitle = "Process complete"
    strResponse = MsgBox(strMsg, vbOKOnly, strTitle)
    
    DoCmd.SetWarnings True

 

[MarkK]

Cool, glad you got it figured out!