Excel crashes - VERY annoying

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
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):
Code:
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. :biggrin:
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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

Well-known Member
Joined
Mar 21, 2006
Messages
1,001

ADVERTISEMENT

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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

Active Member
Joined
Mar 8, 2002
Messages
337

ADVERTISEMENT

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

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

Well-known Member
Joined
Sep 23, 2005
Messages
886
shippey121 - you da man (or maybe da woman). The kill instruction works perfectly, thanks.
 

Forum statistics

Threads
1,137,292
Messages
5,680,648
Members
419,923
Latest member
Kalthus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top