Single Step Macro Runtime Error 2001 when Transferring Query Data to Excel File (1 Viewer)

[Robecca]

At work, I have a MS Access database I "inherited" managing and updating from someone who only used macros (database built in 2014). We have several users all with their own FE applications. All applications & Excel programs have trusted locations set and macro settings are enabled. We all work remote and connect to a remote desktop server (RDS). All of us have our application stored in our own folder within the users folder. Today, one user started to get a Single Step Macro Runtime Error 2001 when any report he tried to run got to the run code in the macro where it was to transfer the data to an Excel file. I can log onto the RDS, open up his folder in file explorer, then open the application he opens and run the reports with no issues. I have googled but I can't find anything close to this for finding a solution. I read about registry issues but our IT assures me that we'd all have that problem on a RDS. I've read that MS Office is corrupt and needs to be repaired but again IT assures me that would mean more of us would have an issue since we're sharing the application with separate user licenses. Just hoping someone on here might know something more about this runtime error for where it's happening. Thank you!

 

[Ranman256]

Never run single step macro.
just run the macro:

in the macro:
ImportExportSpreadsheet,
excel workbook
queryName
filename
yes

or in VB code:

vFile = "C:\mypath\files\ExportFile.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qsMyQuery", vFile, True

 

[Robecca]

Hi, the macro isn't ran in the single step macro mode. That's just the title for the error box that came up. The macro does have a run code command line that initiates the DoCmd.TransferSpreadsheet VBA code. This is where the error box started popping up for this user. The previous person had ImportExportSpreadsheet in all the report macros and I made the change years ago to use the run code command utilizing VBA and the queries he'd already built as append queries. I did this to reduce the file size of the FE application with all the tables he had to just export data to spreadsheets. Our leadership likes Excel reports. I appreciate your time in responding.