troubleshooting Question

Solving Run Time error 3011 with DoCmd.TransferSpreadsheet

Avatar of Dominator1025
Dominator1025 asked on
Microsoft AccessMicrosoft ExcelVB Script
6 Comments1 Solution3070 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros