I am using MS Excel 2007 on Windows XP Professional Version 2002 SP3.
I am using VBA to create a temporary workbook, TempWorkBK, in which I copy over several sheets. I am using TempWorkBk and saving the book using the .SaveAs method. The TempWorkBk is emailed to various users then it is deleted. The first time through the code it works great. However, if I execute the code the second time, it creates the TempWorkBk fine, but when the .SaveAs is executed, I get an error 1004: 'Microsoft Office Excel cannot access the file C:\Program Files\Microsoft Office\Office12\63E04100'. There are several possible reasons: blah, blah, blah.'
First of all, I don't understand why on the second SaveAs it is giving this weird file number because each time the error occurs it is a different number. Second, I am not trying to access this file directly in the code. I have searched through various 1004 error responses on numerous forums but none have indicated this strange numbered file.
The code is below, but please be kind because I don't have error trapping yet, and it needs some more cleanup work. I am debugging within VBA by stepping through the code. Checking the flow of line 4,5 and 6, DIR(TempWorkbk.FullName) <> "", it does not find this file (because I delete it each time after it is emailed.)
LINE 7 IS THE CULPRIT. FIRST TIME THROUGH IS GOOD. SECOND TIME THROUGH NOT SO GOOD! What am I doing wrong?
1. Set TempWorkBk = Workbooks.Add(1)
2. TempName = "MOM - End of Day - " & Format(Now(), "yyyy-mmm-dd") & ".xlsx"
3. Application.DisplayAlerts = False
4. If Dir(TempWorkBk.FullName) <> "" Then
5. MsgBox "The file " & TempWorkBk.FullName & " already exists.", vbOKOnly
6. End If
7. TempWorkBk.SaveAs TempName, xlOpenXMLWorkbook, False, , , , xlNoChange, xlLocalSessionChanges
8. Workbooks(WorkingBook).Sheets("TRUCK").Copy Before:=Workbooks(TempName).Sheets("Sheet1")
9. Workbooks(WorkingBook).Sheets("USPS").Copy Before:=Workbooks(TempName).Sheets("TRUCK")
10. Workbooks(WorkingBook).Sheets("FEDEX").Copy Before:=Workbooks(TempName).Sheets("USPS")
11. Workbooks(WorkingBook).Sheets("UPS - DOCK").Copy Before:=Workbooks(TempName).Sheets("FEDEX")
12. Workbooks(WorkingBook).Sheets("UPS GROUND").Copy Before:=Workbooks(TempName).Sheets("UPS - DOCK")
13. Workbooks(WorkingBook).Sheets("UPS AIR").Copy Before:=Workbooks(TempName).Sheets("UPS GROUND")
14. TempWorkBk.SendMail Recipients:="john.X.collier.-ND@disney.com", Subject:="End of Day Shipping Records " & Format(Date, "mmm/dd/yyyy")
15. TempName = Workbooks(TempName).FullName
16. TempWorkBk.Close
17. Kill TempName
18. Application.DisplayAlerts = True