TransferSpreadsheet -Run time errors

[weatherman85]

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.

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

 

[xenou]

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]

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]

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:

, 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]

Do they have a backslash on the very end of those entries?
If not, it will be problematic here:

, 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]

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]

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

 

[weatherman85]

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]

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.

.DoCmd.TransferSpreadsheet 1, 10, "qry1", export & "qry1.xlsx", True

 

[Norie]

You appear to be using late-binding in the code so acExport won't be defined.

Add this at the top of the module.

Const acExport = 1