asked on
Solving Run Time error 3011 with DoCmd.TransferSpreadsheet
Can anyone see what is causing the run time error in my code? I realize it may be difficult without having the spreadsheet, but I am confident the naming is consistent between my code and the spreadsheet. I have tried multiple files and locations, but I keep getting the error. When I hover my arrow over MyRange, it says empty. Thanks!
Function ImportExcelFile()
Dim MyDir As String
Dim MyFile As String
Dim MyYear As String
Dim MyMonth As String
Dim MyDay As String
Dim MyDate As String
Dim FileToGet As String
Dim shtName As String
MyDir = "//AvailableSummaries/"
MyYear = Format(Date, "yyyy")
MyMonth = Format(Date, "mm")
MyDay = Format(Date, "dd")
MyDate = MyYear & " " & MyMonth & "-" & MyDay
MyFile = MyDate & " Contractor Available Stock" & ".xls"
shtName = "'Contractor Available Inventory'!"
MyRange = "A1:D10000"
FileToGet = MyDir & MyFile
DoCmd.TransferSpreadsheet acImport, 8, "tblContractorAvailableInventory", FileToGet, True, shtName & MyRange
End Function
VB ScriptMicrosoft ExcelMicrosoft Access
Last Comment
mbizup
ASKER
I still have the error. However, the range is no longer empty.
Could there be any issues with excel version file types? The Excel file is a Microsoft Excel Worksheet and I am using Access 2010.
Could there be any issues with excel version file types? The Excel file is a Microsoft Excel Worksheet and I am using Access 2010.
try
changing 8 to 9
DoCmd.TransferSpreadsheet acImport, 9, "tblContractorAvailableInv
changing 8 to 9
DoCmd.TransferSpreadsheet acImport, 9, "tblContractorAvailableInv
ASKER
Same error. When I hover over acImport, I see it = 0.
What does this mean?
What does this mean?
are sure you are getting the correct excel file ?
FileToGet = MyDir & MyFile
debug.print FileToGet 'Add this lines and verify if the file and path is correct.
if dir(MyDir & MyFile) <> "" then
msgbox "File Exists"
else
msgbox " File Not found"
end if
FileToGet = MyDir & MyFile
debug.print FileToGet 'Add this lines and verify if the file and path is correct.
if dir(MyDir & MyFile) <> "" then
msgbox "File Exists"
else
msgbox " File Not found"
end if
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
DoCmd.TransferSpreadsheet acImport, 8, "tblContractorAvailableInv