Excel VBA not referencing range object correctly

Question

I am generating an Excel spreadsheet from within MS Access using VBA.

My code is:

Private Sub btn_Excel_NG_Click()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim qdfnew As DAO.QueryDef
Dim RecordCount As String
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

' ***********************
' ** CREATE QUERY
' ***********************

strSQL = "SELECT Event, DateStart, Suburb, FirstName, Name, Home, DOB, FROM SAT INNER JOIN tbl_records_emailed ON Event = Event WHERE (tbl_records_emailed.NGEmailed Is Null);"

' ***********************
' ** EXPORT TO EXCEL FILE
' ***********************

Set qdfnew = CurrentDb.CreateQueryDef("excelQuery", strSQL)
FileName = "S:\Hub\Processed\Email\" & Format(Now, "ddmmyyyy_hhmm") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, 10, "excelQuery", FileName, True
DoCmd.Close acQuery, "excelQuery"
CurrentDb.QueryDefs.Delete qdfnew.Name

' ********************
' ** FORMAT EXCEL FILE
' ********************

Set xl = New Excel.Application
Set wb = xl.Workbooks.Open(FileName)
Set ws = wb.Worksheets(qdfnew.Name)

With wb.Sheets(qdfnew.Name)
    .rows("1:1").Font.Bold = True
    .Columns("A:Z").AutoFit
End With

Dim tbl As ListObject
Dim rng As Range

wb.Worksheets(qdfnew.Name).Activate
Set rng = ws.Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))

Set tbl = ws.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStylemedium2"

Set tbl = Nothing
wb.Save
wb.Close
Set wb = Nothing
xl.Quit
Set xl = Nothing

End Sub

The line

Set rng = ws.Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))

Is causing the error:

Run-time error '1004': Method 'Range' of object '_Global' failed

I understand that this is because I have not fully qualified my reference to which sheet the range is on - I thought including 'ws' would have fully qualified it?

Answer 1

Try:

Set rng = ws.Range(ws.Range("A1"), ws.Range("A1").SpecialCells(xlLastCell))

You need to fully qualify your Range objects.