Excel VBA Error 1004 - with weird file name trying to be accessed

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

July 9th, 2012 6:39pm

Hi,

As this error message, I think in the code, each time you save the file using the name as MOM - End of Day - date.  And if you run this code the second time in one day, the date will not change, then the file name will be the same. So it will not save the file successful.

I think this is the point of this issue. Try to change the file name using date and time to check the issue.

HTH!

Free Windows Admin Tool Kit Click here and download it now
July 10th, 2012 7:18am

Try add to TempName string some fix path.

Without path your file its saved in "My documents" by default, but you can have a different configuration and access is denied.

p.s.

If Dir(TempWorkBk.FullName) <> "" Then

Doesn't work  - because before save your workbook is in temporary directory and .Fullname has no path.

July 10th, 2012 12:38pm

Thanks Jaynet for the input.

However, the last part of the code is deleting the file that was just saved.  So when I go to "My documents" the file that was just emailed is not longer there.  So it shouldn't be finding the same file name.  Also, why doesn't the error say that "MOM - End of day - Date" should be in the error not some strange number from the Office12 directory.

Free Windows Admin Tool Kit Click here and download it now
July 10th, 2012 2:51pm

Thanks VBATools.  However, this code was just put there to check and see if the DIR command finds the file or not.  On both the first and the 2nd execution, the DIR comes back empty (="")  so the statement inside the THEN clause does not ever execute.  This happens because the file is deleted. 
July 10th, 2012 2:53pm

You're wrong. Dir every time give you empty, because you create new workbook. 

New one has not path in .fullname, so Dir commend never works.

I hope you'll check it in immediate window [Ctrl+G]

Set TempWorkBk = Workbooks.Add(1)
debug.print TempWorkBk.FullName

Free Windows Admin Tool Kit Click here and download it now
July 10th, 2012 9:40pm

Thanks VBATools. You are correct.  I just through this code in for debugging purposes anyway.  I'll remove it for now.

So can you answer me this.  The first time through the code it works perfectly.  The file is created and saved to "My Documents", it gets emailed correctly.  However, if you the click the "End of Day" button again without exiting Excel completely, the second time through I get the error 1004 with the strange file name that says it is located in the Office12 directory.  The file name is always a number.  Even thought the file that I want to save has the same name as in the first pass, why do I get the error if I have deleted the previous file after it has been emailed?

This is what I don't understand.  There has to be a way to run this code where you email the file and you can run the code a second time and not get the error.  Can you tell me how this could be done?

July 11th, 2012 4:48pm

Oh.  One thing I forgot to mention.  I changed the filename to include the time after the date.  The filename before saving it is created by this piece of code.

TempName = "MOM - End of Day - " & Format(Date, "yyyy-mmm-dd") & "_" & Format(Time, "hh_mm_ss") & ".xlsx"

It still gives me the 1004 error the second time through even though the file name is now uniquely created.  I get error 1004 which states:

Microsoft Office Excel cannot access the file 'C:\Program Files\Microsoft Office\Office12\64171100'.  There are several possible reasons:

* The file name or path does not exist

* The file is being used by another program

* The workbook you are trying to save has the same name as a (then I can't see the rest of the message)

Any clues?

Free Windows Admin Tool Kit Click here and download it now
July 11th, 2012 5:07pm

Any resolution to this I have same problem?????
June 9th, 2015 1:45pm

I'm getting this same error too and it will work every once and awhile with the same code.

Any ideas?

Free Windows Admin Tool Kit Click here and download it now
June 17th, 2015 2:37pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics