?
Solved

Solving Run Time error 3011 with DoCmd.TransferSpreadsheet

Posted on 2013-12-18
6
Medium Priority
?
2,071 Views
Last Modified: 2014-02-20
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
Comment
Question by:Dominator1025
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39727821
try this


DoCmd.TransferSpreadsheet acImport, 8, "tblContractorAvailableInventory", FileToGet, True, shtName & "!" & MyRange
0
 

Author Comment

by:Dominator1025
ID: 39727861
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39727889
try

changing 8 to 9


DoCmd.TransferSpreadsheet acImport, 9, "tblContractorAvailableInventory", FileToGet, True, shtName & "!" & MyRange
0
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!

 

Author Comment

by:Dominator1025
ID: 39727894
Same error.  When I hover over acImport, I see it = 0.

What does this mean?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39728037
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39728341
<<
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question