Excel crashes with long file names
 

I am running a macro in Excel 2000 that does a "save as". If the file already
exists and the full filename (including drive and path) is greater than 149
characters, then Excel crashes. Apart from the obvious of shortening the
directory or file names, does anyone know how to stop this happening?

Example code below:

Private Sub CommandButton1_Click()

' This line works (149 characters)
ActiveWorkbook.SaveAs
"C:\abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrst uvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqr stuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl.xls "

' This line works (150 characters) if file does not already exist
ActiveWorkbook.SaveAs
"C:\abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrst uvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqr stuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklm.xl s"

' This line crashes (150 characters) if file already exists
ActiveWorkbook.SaveAs
"C:\abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrst uvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqr stuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklm.xl s"

End Sub

Excel crashes with long file names
 

Hi Confuscious,

Apart from the obvious of shortening the
directory or file names, does anyone know how to stop this happening?

Delete the file you're overwriting first:

On error resume next 'In case file is not there
Kill "c:\YaddaYadda.xls"
Activeworkbook.SaveAs "c:\YaddaYadda.xls"

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Excel crashes with long file names
 

Hi Jan

Thanks for that.

I'd already thought of that one but hadn't included it in the original post.
It will probably be the simplest solution though so I'll probably go with it
anyway.

Confuscious

"Jan Karel Pieterse" wrote:

Hi Confuscious,

Apart from the obvious of shortening the
directory or file names, does anyone know how to stop this happening?

Delete the file you're overwriting first:

On error resume next 'In case file is not there
Kill "c:\YaddaYadda.xls"
Activeworkbook.SaveAs "c:\YaddaYadda.xls"

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Excel crashes with long file names
 

"Confuscious" ...
Hi Jan

Thanks for that.

I'd already thought of that one but hadn't included it in the original
post.
It will probably be the simplest solution though so I'll probably go with
it
anyway.

Confuscious

"Jan Karel Pieterse" wrote:

Hi Confuscious,

Apart from the obvious of shortening the
directory or file names, does anyone know how to stop this happening?

Delete the file you're overwriting first:

On error resume next 'In case file is not there
Kill "c:\YaddaYadda.xls"
Activeworkbook.SaveAs "c:\YaddaYadda.xls"

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com