Excel VBA Problem saving file
|
Original Message
|
Name: dustybob
Date: September 19, 2006 at 13:38:13 Pacific
Subject: Excel VBA Problem saving file OS: Windows XPCPU/Ram: intel p4 2.66GHz 512mb ra |
Comment: I'm trying to save an excel spreadsheet with the filename of K&N.csv but when I try and do this from within my macro, excel crashes when the workbook is closed. I can save the file and close the workbook manually with the name K&N.csv and excel doesn't crash. If I remove the ampere sign from the file name I can save the file as KN.csv from within the macro without any crash. Does anyone know how to save a file with excel vba and give it a filename that contains an ampere sign? Thanks any help is appreciated. Below is a copy of the code I used to save the file. Private Sub cmdUpdateWeb_Click() On Error Resume Next Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\kn\K&N.csv", _ FileFormat:=xlCSV, CreateBackup:=False Windows("K&N.CSV").Activate ActiveWorkbook.Close False Unload Me End Sub
Report Offensive Message For Removal
|
|
Response Number 1
|
Name: StuartS
Date: September 19, 2006 at 19:26:17 Pacific
|
Reply: (edit)I am afraid you wont be able to without some juggling. The ampersand has a special meaning in VBA and it doesn't like it anywhere it doesn't belong. It used a a type declaration character for a long integer. You might try saving the file with a double ampersand and see if that works. It will appear as a single ampersand and you will have to retrieve it with a single ampersand. Stuart
Report Offensive Follow Up For Removal
|
|
Response Number 2
|
Name: dustybob
Date: September 20, 2006 at 15:31:26 Pacific
|
Reply: (edit)Ok I tried changing to a double ampersand and excel didn't crash after closing the file but the file that that was saved has the double ampersand in the filename and I need it to only have one ampersand in the name. Below is a copy of what I tried Private Sub cmdUpdateWeb_Click() On Error Resume Next Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\kn\K&&N.csv", _ FileFormat:=xlCSV, CreateBackup:=False Windows("K&N.CSV").Activate ActiveWorkbook.Close False Unload Me End Sub
Report Offensive Follow Up For Removal
|
|
Response Number 3
|
Name: StuartS
Date: September 20, 2006 at 17:21:19 Pacific
|
Reply: (edit)There is one otherthing you can try. Try saving the file as "K" + Chr(38) + "N.csv" 38 is the ASCII code for an & and it might fool VB to ingnoring it. I have had problems with ampersands in variable names and database fields. VB just does not like it! Stuart
Report Offensive Follow Up For Removal
|
Use following form to reply to current message: