Tom's Guide | Tom's Hardware | Tom's Games
![]() |
![]() |
![]() |
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

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

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

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

![]() |
![]() |
![]() |

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |