Error on Range("A1").SpecialCells(xlLastCell))

[pastelrain]

Hi,
This line of code will give me a "Method 'Range' of object '_Global' failed" error about 50% of the time I try to run the below code. Debug takes me to this line:
Set rng = xlWS.Range(Range("A1"), xlWS.Range("A1").SpecialCells(xlLastCell))

Can anyone help with this problem?? Thanks.


Private Sub Command48_Click()
'On Error Resume Next
Dim Filename As String
Dim month1 As String
Dim year1 As Integer

Dim startTime As Date
startTime = Now

Dim strDirectoryPath As String
Filename = strDirectoryPath & "\" & "QI_GAP_REPORT_2_ " & Format$(Now(), "mm-dd-yyyy") & ".xls"
DoCmd.OpenQuery "QI_GAP_REPORT_FOR_EXCEL"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "QI_GAP_REPORT_FOR_EXCEL", Filename, False, "Summary"
DoCmd.Close acQuery, "QI_GAP_REPORT_FOR_EXCEL"

'///****Format excel workbook****////
' Late binding to avoid reference:
Dim xlApp As Object 'Excel.Application
Dim xlWB As Object 'Workbook
Dim xlWS As Object 'Worksheet
Dim GetBook As String



' Create the instance of Excel that we will use to open the temp book
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(Filename)
Set xlWS = xlWB.Worksheets("Summary")


' Format our temp sheet
' ***************************************************************************

xlApp.Range("A1").Select

Const xlLandscape As Long = 2
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
Const xlContext As Integer = -5002
Const xlDown As Integer = -4121
Const xlContinuous As Integer = 1
Const xlThin As Integer = 2
Const xlLastCell As Long = 11
Const xlYes As Long = 1


With xlWS
With .UsedRange
.borders.LineStyle = xlContinuous
.borders.ColorIndex = 0
.borders.TintAndShade = 0
.borders.Weight = xlThin
End With

'format header 90 degree
With .Range("i1:y1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
.UsedRange.Rows.RowHeight = 15
.UsedRange.Columns.AutoFit

Dim tbl As ListObject
Dim rng As Range

Set rng = xlWS.Range(Range("A1"), xlWS.Range("A1").SpecialCells(xlLastCell))
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleMedium2"
tbl.ShowTotals = True

 

[pbaldy]

I'm surprised it works at all. Normally with late binding I haven't been able to use constants like xlLastCell. Try to find the numeric equivalent. I'll be on a computer later if you can't find it. I have code with it there.

 

[pastelrain]

I'm pretty new to this so just kinda piece mealing some code together to make it work If you can supply me with the correct code I would really appreciate it!!

 

[pbaldy]

That computer is working on a long process, but this was the first result of a google search:

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.constants.aspx

 

[pastelrain]

Thanks! So in place of xlLastCell I put 11? (I tried that and I'm still having the same issue)

 

[pastelrain]

I ended up getting it to work with this:

With xlWB.Sheets("Summary")
Set rng = .Cells(1, 1).CurrentRegion
End With
Set tbl = xlWS.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleMedium2"
tbl.ShowTotals = True

 

[pbaldy]

Glad you got it working.