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

 

[Darren Bartrup]

Hi Airfix9,

All I can think of is to move this line to the Declarations section of the module (i.e. move it to the very top of the module, above any procedures):

Const cPath As String = "G:\CPG\Business Planning\CAV Biz Planning\Independents\Key Reports\Report Databases\Data Files\FY06\Daily Files\"

or to change the variable names 'FOLDER' and 'FILE' to something that won't confuse Excel (I'm not sure, but those variable names might already be in use by Excel) - try changing it to strFolder and strFile.

Not sure if that's the reason, but it's worth a try.

 

[Airfix9]

Hi Delmar, thanks for your reply.

Alas, didn't work! Still crashing. The annoying thing is that I am sure that this worked fine the other day - but couldn't guarantee it. It is so rare that we have to over-write this file, but the functionality should be there.

Incidentally, I have over-writes in other macros that are unaffected!

VERY strange...

 

[shippey121]

maybe its the two \'s your cpath ends with a \ then you add another \

so path would be

G:\CPG\Business Planning\CAV Biz Planning\Independents\Key Reports\Report Databases\Data Files\FY06\Daily Files\\ folder file

try removing one of them

HTH

 

[shippey121]

sorry, re read and the \ is after the folder,

you could try using code to check if the file exists, if yes kill it first

 

[Airfix9]

Hi shippey121, thanks for your answer.

The cPath ends in \ but the next \ is between FOLDER and FILE. The file saves perfectly the first time, it's just when I attempt to over-write that the problem arises.

 

[jdavis9]

if you just did a save as, you now have the file open, and need to close it. It can not overwrite it if its open.

 

[shippey121]

assuming your file is a master file,

use

on error resume next
kill cpath & "\" & folder & file

this will delete the file is it exists, if not carry on as normal

HTH

 

[Airfix9]

Hi shippey121, I can do that (and will have to), I just don't understand why Excel crashes on this macro.

Hi jdavis9, thanks for your reply. The file that is open is another name (it's called "Download" actually). This instruction asks it to save itself as the filename named on the "Data" sheet. Or am I missing something?

 

[Airfix9]

shippey121 - you da man (or maybe da woman). The kill instruction works perfectly, thanks.