Method 'Range' of objet '_Global' failed on second run

[didactics]

I have reseached this problem for 5 days to no avail. Lots of great suggestions but nothing has worked. I am fairly new to VBA and running Office 2010 and am dumping information from a query into an excel file and perform formatting via VBA. The first time I run the code it works perfectly. The 2nd time it fails with this error. After I end and run the code again, it works. I have modified this code in several ways and this is the final code where I have given up. Any suggestions?

Public Function FormatRprt()
On Err GoTo ErrorHndle

'dump query from access to excel file
    DoCmd.OutputTo acOutputQuery, "Q_Issues_Summary", "ExcelWorkbook(*.xlsx)", "C:\Users\CMDLJ\Documents\Q_Issues_Summary.xlsx", False, "", , acExportQualityPrint

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSH As Excel.Worksheet
'open excel file
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("C:\Users\CMDLJ\Documents\Q_Issues_Summary.xlsx")
    Set xlSH = xlWB.Sheets("Q_Issues_Summary")
    xlApp.Visible = True
  
<B> 'this is where the code breaks
    xlSH.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = _
        "Table1"</B>
    
'clear defult formatting
    Range("Table1[#all]").ClearFormats
    

    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight15"
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Columns("D:D").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
'adjust date/time format
    Columns("A:A").Select
    With Selection
        Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
    End With
    Columns("I:I").Select
    With Selection
        Selection.NumberFormat = "[$-409]m/d/yy"
    End With
    xlApp.Application.ScreenUpdating = True

'save and clean up
    xlWB.Save
    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSH = Nothing

Exit_error:
  Exit Function
ErrorHndle:
  MsgBox Err.Number & Err.Description
  Resume Exit_error

    
End Function
[end code]




Thanks in advance.

 

[Norie]

You have no worksheet, or workbook reference, for Range("A1").CurrentRegion.

Try changing it to xlSH.Range("A1").CurrentRegion.

 

[didactics]

You have no worksheet, or workbook reference, for Range("A1").CurrentRegion.

Try changing it to xlSH.Range("A1").CurrentRegion.

Thanks
I changed the code as suggested

ActiveSheet.ListObjects.Add(xlSH.Range("A1").CurrentRegion, , xlYes).Name = _
        "Table1"

Now get a Type Mismatch error.
Also I assumed that referencing the "activesheet" would qualify as a valid reference.

 

[Norie]

Why are you using ActiveSheet?

To refer to the Excel worksheet you should use the reference xlSH throughout - ActiveSheet doesn't really mean anything without at least a reference to the Excel application.

Actually that reminds me, you need to add worksheet references for this section of the code.

'clear defult formatting
 Range("Table1[#all]").ClearFormats


 Range("Table1[#All]").Select
 ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight15"
 With ActiveWindow
 .SplitColumn = 0
 .SplitRow = 1
 End With
 ActiveWindow.FreezePanes = True
 Columns("D:D").Select
 With Selection
 .HorizontalAlignment = xlGeneral
 .VerticalAlignment = xlBottom
 .WrapText = True
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 .ReadingOrder = xlContext
 .MergeCells = False
 End With

 'adjust date/time format
 Columns("A:A").Select
 With Selection
 Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
 End With
 Columns("I:I").Select
 With Selection
 Selection.NumberFormat = "[$-409]m/d/yy"
 End With

That should be straightforward if you use With xlSH, something like this.

With xlSh

    .Range("Table1[#all]").ClearFormats
    .ListObjects("Table1").TableStyle = "TableStyleLight15"

    With .Columns("D:D")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    'adjust date/time format
    .Columns("A:A").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

    .Columns("I:I").NumberFormat = "[$-409]m/d/yy"

End With

Note, I've not included the code to split/freeze the window, that would require the reference, xlApp, to the Excel application.