Error 2522

[Ace2014]

Hi Everyone,

I have a table in Access where my users upload the information they'd like to connect to relational data in a knowledge database in order to reduce the scope of their search to what matches the ids they're looking for.

I have them click a button and I'd like them to find and append their excel spreadsheet template data to a table in access
I then want to requery the query the table is attached to
I then want to export the query results to excel
Finally to clear the contents of the table in access

The only problem is my acexport transferspread isn't working.
I've used this model for many other similar exports already and I think it's working fine, at least they're not getting error messages.

Here's the code if anyone has any recommendations:

Private Sub CommandCumulativeBench_Click()
Call Upload_PPNBench
DoCmd.Requery (qry3a_Select_PPNBench)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry3a_Select_PPNBench", strExcelFile, True
DoCmd.Requery (qry3b_Select_PPNBench_dlt)
End Sub

Thanks in advance

[ItsMe]

I would comment out this line
DoCmd.Requery (qry3a_Select_PPNBench)

and this line
DoCmd.Requery (qry3b_Select_PPNBench_dlt)


What exactly is not working? How do you know it is not working?

[Ace2014]

Ok, I commented out the lines and got the same 2522 error.

It has to do with the transferspreadsheet line
I know because when I press debug, that's the line that gets highlighted and after looking up 2522 it says it's for transferspreadsheet commands and that the File Name is not specified.
But it is specified.
So I'm not quite sure why I'm getting error 2552.

[amrut]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry3a_Select_PPNBench", strExcelFile, True

Have you declare/assigned a value to strExcelFile?

[Ace2014]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry3a_Select_PPNBench", strExcelFile, True

Have you declare/assigned a value to strExcelFile?

No, I haven't. I didn't think of that.
How do I get it to just put the information in a new excel spreadsheet?

[ItsMe]

You have identified the problem now initialize your variable or hard code the string directly in the parameter.

Hard code example:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry3a_Select_PPNBench", "\\ServerName\FolderName\FileName.Extension", True

[amrut]

Try this -

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry3a_Select_PPNBench", CurrentProject.Path & "\xyz.xls", True

Above should work. May be other experts on the forums have more ideas.

[Ace2014]

Great, thank you.

Is there a way to allow for the user to choose where they'd like to export the excel spreadsheet to?
The database will be on a server and the users will need to export the information directly to their computers, even if it's just the desktop, that's fine. They can resave it after how every they like after

[Ace2014]

Try this -

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry3a_Select_PPNBench", CurrentProject.Path & "\xyz.xls", True

Above should work. May be other experts on the forums have more ideas.

That's pretty cool. Thanks.
This time I got a error 3027: Cannot update. Database or object is read-only.

I'm not quite sure what that means.

[Ace2014]

Not quite sure how I messed up what I had, but here's what I had and here's what I changed it to, I think it works now

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry3a_Select_PPNBench", CurrentProject.Path & "\xyz.xls, True"
replace with "xyz.xlsx", True
Thanks again for all your help