Excel Temp files

[godistop1]

Have an excel 2010 file with code behind creating another excel file from options selected. Been uses this file long time and just making copies and making modifications for new month. In process of creating a new monthly file some current sheets are deleted and some other sheets are copied from another excel file and the code behind is executed. All if sudden started getting error message can not find a temp file. I finally got the file the way it should be by deleting a lot of temp files in local\temp directory. Passed the new file to a user for testing, first time processing it worked fine. But on second time got error message File not found: 'c:\Temp\VBEF4E.tmp, click ok the 400 error message. When user close the excel file and opened back up same thing: first account worked, error message on second account.

How to fix this problem? Don't want to go to each users to delete temp files, it might happen again.

Any help would be appreciated

[TMS]

Excel usually manages its own temporary files. The implication is that Excel is not closing them/itself down tidily and they are left behind. Or, maybe, the code is looking in the wrong folder for the temporary file created by your process/code.

Given that you haven't shared the workbook or code, it's difficult to offer anything more specific.

[ccasio04]

Good afternoon,

If I understood well, your code creates a temporary file which is later used by an user, the first time the codes relates to the temp file, it works fine, but the next times it won't.

If so, what I do for those kind of codes is, creating a "Temporary files" folder on the PC or Server, and saving the new excel with the same name on that path. So for example, you would have "Temp_Excel_for_User_1.xls" on "Temporary files folder.
Whenever you need to change that temporary file, you can command it to overwrite if there's an Excel file with the same name on that folder. So you won't be creating tons of temporary files, but a normal excel file that is always overwrited when you need it, and therefore the path and the excel name always stay the same, so users won't need to erase anything.

I hope this can help you out, BR

[godistop1]

After doing more some debugging on the code the error is actually happening of copy sheet command. It appears the code cannot find the specific worksheet to copy. Error happens randomly more testing that is been done.

Please Login or Register  to view this content.

[TMS]

Can you upload a sample workbook with some typical data? If you can describe the process you follow, it may be possible to reproduce the problem.

We, at least I, can't step through the code to check it without the workbook + data + process.

[godistop1]

After spending time, the problem for some unknown reason the command stopped working after years of working. Sheets("Item Master").Copy when try other methods I break macros/formulas. What is the best practice to create a new workbook from current open workbook sheet? The open workbook has 5 sheets, need to use just the 2 sheet to create the workbook. I am using Excel 2010, XLSM format

Thanks for additional help.

[godistop1]

Never did figure out why all of the sudden the Sheet().Copy started erroring out. I changed the method of creating the new file without breaking any macros.
Thanks for the help.