So I use the code below to import a range based on the last cell with data in column A.
When I do I believe this line of code "set xl = CreateObject("Excel.Application")" keeps creating the error message "invalid outside procedure"
I am pretty new at VBA. Do I just stick this code in a module in access and run it? Everytime I attempt to do so it asks me to create a macro. Could this be the problem?
Dim xl as object
Dim wb as object
Dim sht as object
Dim lngLastRow as long
set xl = CreateObject("Excel.Application")
set wb =xl.Workbooks.Open(strfilename)
set sht = wb.Worksheets(1) 'or wb.Worksheets("SheetName")
lngLastRow = sht.Range("A65536").End(xlup).row
set xl = nothing
DoCmd.TransferSpreadsheet acImport, , _
"Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.xlsx", True, Range("A1:U" & lngLastRow)