Export via Macro results in random error 2950

[PhilSmith1964]

I have a Macro I run daily (via task scheduler).  About 1 in 4 times I get error 2950 exporting one query using ImportExport spreadsheet.  The same command exports similar queries to the same location just  fine.  Rerunning the routine manually works fine.  The output file is never opened by anything.  A batch run at the start deletes all *NEW files,  I export to "fileNEW", then copy to "file" when I am done via batch file. 

So I am getting an error saying it  can't find a file that does not exist, so it can't create the file. That makes sense.  The filename is  S:\IT\PowerBIFiles\BrandCategoryPenetrationStep4NEW.xlsx.

I don't know where to start looking.  

Thanx

Phil

[#2]

@PhilSmith1964 

Error 2950 in Microsoft Access typically occurs when there's an issue with executing a macro or VBA code. In your case, it seems to be related to exporting a query using the ImportExport spreadsheet action. Here are some steps you can take to troubleshoot and resolve this issue:

1. Check File Path and Permissions: Ensure that the file path (S:\IT\PowerBIFiles) exists and that the user running the task scheduler has appropriate permissions to access and write to this location. Sometimes, intermittent errors can occur due to network issues or permissions problems.
2. Verify File Existence: Double-check that the file "BrandCategoryPenetrationStep4NEW.xlsx" does not exist before running the export macro. It's possible that the macro is trying to overwrite an existing file, which could cause errors.
3. Error Handling in Macro: Implement error handling in your macro to handle the 2950 error gracefully. You can use VBA's On Error Resume Next statement to ignore errors temporarily and continue execution, or On Error GoTo ErrorHandler to handle errors explicitly.
4. Review Macro Code: Review the VBA code associated with the macro to ensure that there are no issues with the export process. Check for any hardcoded file paths or other potential sources of error.
5. Logging and Debugging: Add logging statements or debug messages to your macro code to track the progress and identify any specific steps or conditions leading to the error. This can help pinpoint the root cause of the issue.
6. Test Environment: Test the macro in different environments or under different conditions to see if the error persists consistently or if it's related to specific circumstances.
7. Update or Repair Office Installation: If the issue persists, consider updating or repairing your Microsoft Office installation. Sometimes, errors can occur due to corrupted files or outdated software components.

By following these steps and investigating the potential causes of the error, you should be able to identify and resolve the issue with exporting the query in your macro. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.