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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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?
 

weatherman85

New Member
Joined
Feb 26, 2016
Messages
5
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,303
Office Version
  1. 365
Platform
  1. Windows
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!
 

weatherman85

New Member
Joined
Feb 26, 2016
Messages
5

ADVERTISEMENT

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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,303
Office Version
  1. 365
Platform
  1. Windows
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.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Problems also result I think if the file already exists. You might need to delete the file if it already exists.
 

weatherman85

New Member
Joined
Feb 26, 2016
Messages
5
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.
 

weatherman85

New Member
Joined
Feb 26, 2016
Messages
5
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,044
Messages
5,835,103
Members
430,342
Latest member
sdelan

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
Top