Excel crashes - VERY annoying

[Airfix9]

Hopefully this will come out!

Essentially, this piece of code looks on the "data" sheet of my file for the folder name and file name to use when saving the file.

This is part of a macro that I wrote to take some data on a daily basis and save a snapshot of it as a file named for the day that it was created.

The macro runs fine the first time but, occasionally, it needs running twice in a day. If I run the macro again, it crashes Excel when it gets to this bit of code. Have tried disabling the "DisplayAlerts" instruction and manually agreeing to overwrite the file - if I say "Yes" or "No" it matters not... Excel still crashes!

Anyone know of a reason for this, please?


<font face=Courier New><SPAN style="color:#007F00">' Saves file in correct folder</SPAN>

    
    Workbooks("06 Master Data Transit.xls").Sheets("Data").Activate
    <SPAN style="color:#00007F">Dim</SPAN> FOLDER <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    FOLDER = Sheets("Data").Range("B9").Value
    <SPAN style="color:#00007F">Dim</SPAN> FILE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    FILE = Sheets("Data").Range("B10").Value
<SPAN style="color:#00007F">Const</SPAN> cPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "G:\CPG\Business Planning\CAV Biz Planning\Independents\Key Reports\Report Databases\Data Files\FY06\Daily Files\"
    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    ActiveWorkbook.SaveAs cPath & FOLDER & "\" & FILE & ".xls"
    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN></FONT>

Am running XL2000 on Win XP

Ta

 

[jdavis9]

When you have an open file and you do a file save as, the open file is now the new file name, and the old file is not open.

name: Test1.xls
save as: Test2.xls

You now only have Test2.xls open not Test1.xls
If you are done with Test2.xls then do:

Windows("test2.xls").Close

Now if you run your code again, you dont have to kill it first, unless you protected it, it will overwite it without prompting if you do application.displayalerts = false before the save as.

 

[Airfix9]

Sorry jdavis9, I know that, but it won't let me do the "Save As" bit. That's where the crash occurs...

The current file is "Download".

FOLDER is defined as "=TEXT(TODAY(),"yy")&"-"&TEXT(TODAY(),"mm")&" "&TEXT(TODAY(),"mmmm")&" "&TEXT(TODAY(),"yyy")"

and FILE as "=TEXT(TODAY(),"dd")&"."&TEXT(TODAY(),"mm")&"."&TEXT(TODAY(),"yy")&" ("&TEXT(TODAY(),"ddd")&")""

For some reason it doesn't like to do the Save As if that folder/file combination already exist...

 

[jdavis9]

I created your same path and the rest.

I am using xp with excel 2003 (11.8033.8028) sp2

This is the same as your file name formula

"=TEXT(NOW(),"DD.mm.yy (ddd)")"

this is the same as your folder formula

"=TEXT(NOW(),"yy-mm mmmm yyy")"

Check if the file is open...

.....

    Application.DisplayAlerts = False    
Dim objexcel As Excel.Application
Dim wb As Workbook
    Set objexcel = GetObject(, "excel.application") '   get opened excel
    For Each wb In objexcel.Workbooks
        If wb.Name = FILE & ".xls" Then
            Application.Workbooks(FILE & ".xls").Close
        Else: End If
    Next
    ActiveWorkbook.SaveAs cPath & FOLDER & "\" & FILE & ".xls"
    Application.DisplayAlerts = True

I assume your master file is open every time you are running the save as code.

Using the same code you used I have no problem with my version saving it providing the file name you are saving to is not open.

Try running the code once.
Close the file. re-open the master.
Check in windows task manager to verify only one instance of excel is running.

Make sure nobody else has your file you will overwrite opened.
Run it a second time.

If it still fails, verify you have "full acces rights" (windows) to the directory you are trying to over-write a file in.

If it still fails...... remove the periods and the brackes from the file name.

"=TEXT(NOW(),"DD_mmm_yy ddd")"

try again.....

Question: Do you create the directory earlier in the same program?

If so make sure you check if the directory does not exsists before trying to create it........

Other than the above........dunno