TransferSpreadsheet -Run time errors

weatherman85

New Member
Joined
Feb 26, 2016
Messages
5
I've tried looking around to an answer to this but I haven't been able to find anything that actually resolves it for me.

I am trying to use Excel to run TransferSpreadsheet for a series of queries that are determined by a range in Excel. When I put the code in Access, I have no problems running it. When I try to translate it back to Excel I get two different run time errors.

The first happens when the file isn't there: Err 3011. If I create a blank file with the name of the export I then get Err 3073.

Seeing as how it works from Access, I really can't see why it wouldn't work from Excel. Then again, if I could see why I'd know the answer....

Any help would be appreciated.

Code:
Dim objAccess As Object, dbase, export As Range
Dim wkb As Workbook, wks As Worksheet
Set objAccess = CreateObject("Access.Application")

Set wkb = ActiveWorkbook
Set wks = wkb.Worksheets("Sheet1")

Set dbase = wks.Range("D8")
Set export = wks.Range("D10")

With objAccess
.Visible = False
.OpenCurrentDatabase (dbase & "test.accdb")
.Visible = False
.DoCmd.TransferSpreadsheet acExport, 10, "qry1", export & "qry1.xlsx", True
End With
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
What's in cell D8, what's in cell D10? Are you trying to export to the same workbook you are trying to run this from?
 
Upvote 0
What's in cell D8, what's in cell D10? Are you trying to export to the same workbook you are trying to run this from?

D8 contains the Directory of the database, D10 the directory I want the query to export to.

Exporting to a different file than the one I am running it from.

Thanks.
 
Upvote 0
D8 contains the Directory of the database, D10 the directory I want the query to export to.
Do they have a backslash on the very end of those entries?
If not, it will be problematic here:
Code:
, export & "qry1.xlsx",
as you need a backslash before the file name "qry1.xlsx", or else that will become part of your file path!
 
Upvote 0
Do they have a backslash on the very end of those entries?
If not, it will be problematic here:
Code:
, export & "qry1.xlsx",
as you need a backslash before the file name "qry1.xlsx", or else that will become part of your file path!

Yup, I added the \ to the directory path. I can move it to the file name if that would help?
 
Upvote 0
Yup, I added the \ to the directory path. I can move it to the file name if that would help?
It won't make a difference as long as it is in one of the places so that your file name is created correctly.
 
Upvote 0
Problems also result I think if the file already exists. You might need to delete the file if it already exists.
 
Upvote 0
Problems also result I think if the file already exists. You might need to delete the file if it already exists.

If the file doesn't exist I get a "File can't be found" error.
If the file exists, I get a "Query must be updateable" error.

If I write the code in Access it works fine though with both scenarios.
 
Upvote 0
Turns out that it wasn't picking up the acExport and was attempting to import to the query instead. I switched the code to the below and it solved it.
Code:
.DoCmd.TransferSpreadsheet 1, 10, "qry1", export & "qry1.xlsx", True
 
Upvote 0
You appear to be using late-binding in the code so acExport won't be defined.

Add this at the top of the module.
Code:
Const acExport = 1
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top