Excel VBA未正确引用范围对象

Question

我正在使用VBA从MS Access中生成一个Excel电子表格。

我的代码是：

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

线

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

导致错误：

运行时错误“1004”：对象“_Global”的方法“范围”失败

我知道这是因为我还没有完全限定我的参考范围是哪一张--我认为包括'ws‘在内会完全符合条件吗？

Answer 1

尝试：

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

您需要完全限定您的Range对象。