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
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