[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2230
  • Last Modified:

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

Open in new window

0
Dominator1025
Asked:
Dominator1025
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this


DoCmd.TransferSpreadsheet acImport, 8, "tblContractorAvailableInventory", FileToGet, True, shtName & "!" & MyRange
0
 
Dominator1025Author Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
try

changing 8 to 9


DoCmd.TransferSpreadsheet acImport, 9, "tblContractorAvailableInventory", FileToGet, True, shtName & "!" & MyRange
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Dominator1025Author Commented:
Same error.  When I hover over acImport, I see it = 0.

What does this mean?
0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
mbizupCommented:
<<
MyDir = "//AvailableSummaries/"

>>

Double -check this.  

If you are dealing with a network folder, shouldn't the slashes be going in the opposite direction?


MyDir = "\\AvailableSummaries\"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now