Sub is affecting the wrong Excel workbook

Question

I wrote this VBA code to generate a report from data in an Access table and dump it into Excel with user friendly formatting.

The code works great the first time. But if I run the code again while the first generated Excel sheet is open, one of my subroutines affects the first workbook instead of the newly generated one.

Why? How can I fix this?

I think the issue is where I pass my worksheet and recordset to the subroutine called GetHeaders that prints the columns, but I'm not sure.

Sub testROWReport()

DoCmd.Hourglass True

'local declarations
Dim strSQL As String
Dim rs1 As Recordset
'excel assests
Dim xlapp As excel.Application
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim tempWS As Worksheet
'report workbook dimentions
Dim intColumnCounter As Integer
Dim lngRowCounter As Long

'initialize SQL container
strSQL = ""
'BEGIN: construct SQL statement {
--this is a bunch of code that makes the SQL Statement
'END: SQL construction}

'Debug.Print (strSQL) '***DEBUG***
Set rs1 = CurrentDb.OpenRecordset(strSQL)

'BEGIN: excel export {
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Visible = False
    xlapp.ScreenUpdating = False
    xlapp.DisplayAlerts = False

    'xlapp.Visible = True '***DEBUG***
    'xlapp.ScreenUpdating = True '***DEBUG***
    'xlapp.DisplayAlerts = True '***DEBUG***

    Set wb1 = xlapp.Workbooks.Add
    wb1.Activate
    Set ws1 = wb1.Sheets(1)
    xlapp.Calculation = xlCalculationManual
    'xlapp.Calculation = xlCalculationAutomatic '***DEBUG***

    'BEGIN: Construct Report
    ws1.Cells.Borders.Color = vbWhite
    Call GetHeaders(ws1, rs1) 'Pastes and formats headers
    ws1.Range("A2").CopyFromRecordset rs1 'Inserts query data
    Call FreezePaneFormatting(xlapp, ws1, 1) 'autofit formatting, freezing 1 row,0 columns
    ws1.Name = "ROW Extract"
        'Special Formating
        'Add borders
        'Header background to LaSenza Pink
        'Fix Comment column width
        'Wrap Comment text

        'grey out blank columns
    'END: Report Construction

    'release assets
    xlapp.ScreenUpdating = True
    xlapp.DisplayAlerts = True
    xlapp.Calculation = xlCalculationAutomatic
    xlapp.Visible = True

    Set wb1 = Nothing
    Set ws1 = Nothing
    Set xlapp = Nothing
    DoCmd.Hourglass False
'END: excel export}

 End Sub

    Sub GetHeaders(ws As Worksheet, rs As Recordset, Optional startCell As Range)

    ws.Activate 'this is to ensure selection can occur w/o error

    If startCell Is Nothing Then
    Set startCell = ws.Range("A1")
    End If

    'Paste column headers into columns starting at the startCell
    For i = 0 To rs.Fields.Count - 1
    startCell.Offset(0, i).Select
    Selection.Value = rs.Fields(i).Name
    Next

    'Format Bold Text
    ws.Range(startCell, startCell.Offset(0, rs.Fields.Count)).Font.Bold = True

End Sub
Sub FreezePaneFormatting(xlapp As excel.Application, ws As Worksheet, Optional lngRowFreeze As Long = 0, Optional lngColumnFreeze As Long = 0)


  Cells.WrapText = False
  Columns.AutoFit

    ws.Activate
    With xlapp.ActiveWindow
        .SplitColumn = lngColumnFreeze
        .SplitRow = lngRowFreeze
    End With
    xlapp.ActiveWindow.FreezePanes = True

End Sub

Hard to tell at first glance, but what would really help you is to make a minimal reproducible example, trying it out, and sharing it with us here. This would make the issue easier and quicker to diagnose. — Jean-François Corbett
I agree, I apologize for not giving a more verifiable example, but I would need to provide a lot of assets including a database with my SQL statement. Thanks for taking a look though:-) — ArcherBird
No, you wouldn't. That's the "minimal" part of "minimal, complete, and verifiable". Peel off the things that don't cause the error. Only keep what does in your example. — Jean-François Corbett
Oh yes, you are definitely right on there. I thought you were touching on the lack of completeness in my example. I' usually have a hard time balance the minimal part and the complete part... — ArcherBird

Florent B. Florent B. · Accepted Answer · 2016-03-16T14:10:01

When Cells and Columns are used alone, they refer to ActiveSheet.Cells and ActiveSheet.Columns. Try to prefix them with the targeted sheet:

Sub FreezePaneFormatting(xlapp As Excel.Application, ws As Worksheet, Optional lngRowFreeze As Long = 0, Optional lngColumnFreeze As Long = 0)

    ws.Cells.WrapText = False
    ws.Columns.AutoFit
    ...

End Sub

Sub is affecting the wrong Excel workbook

2 Answers