Runtime Error:Microsoft office excel cannot access the file

[peace77]

Hello Friends,

I get the following error when my macro is trying to save the file in Excel 2003. It does not always give this error- at times it runs fine and at times it does not…And if the error comes and I close the excel application entirely and reopen it than the the macro runs without giving any error and nicely saving the file..

Run Tim Error:
Microsoft office excel cannot access the file ‘c:\Program Files\CommonFiles\SYSTEM\msapi\1003\

• The file name or path does not exist.
• The file that you are trying to open is being used by another program. Close the document in the other program, and try again.
• The name of the workbook that you are trying to save is the same as the name of another document that is read-only. Try saving the workbook with a different name.

None of the above options is causing this error. Also this file is accessed from this location:
C:\Documents and Settings\pn\My Documents\projects\Capital (not ‘c:\Program Files\CommonFiles\SYSTEM\msapi\1003\)

This is the part of the code where the error occurs ( ActiveWorkbook.SaveAs FileName:=)

     ChDrive "K:\"
     ChDir "K:\Caps" 
     Application.DisplayAlerts = False
     ActiveWorkbook.SaveAs FileName:= _
        "USCapstock" & Format(Now(), "yyyy-mm-dd") & ".xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="",  ReadOnlyRecommended:= _
    False, CreateBackup:=False
    Application.DisplayAlerts = True

I have no clue why this error comes invariably. Any thoughts are highly appreciated.

Thanks a ton

Peace

 

[ck1999]

Try removing the
Application.DisplayAlerts = False

and see what kind of msgboxes you get.


ck1999

 

[combo]

What is the current folder before saving? You can add full path in FileName.

combo

 

[peace77]

the reason I have Application.DisplayAlerts = False is to allow overwriting on the file with the same name. If I remove that it will display a dialog box asking users permission to overwrite the file.

I cant specify the path name of current folder before saving as it will vary on users to users.

 

[combo]

1) DisplayAlerts = False addumes default action, in this case it is 'no overwrite'
2) If any rule exists in folder name you can apply it. You can ask user to point a folder too. Next you can add two strings to create full name.

combo

 

[ck1999]

I cant specify the path name of current folder before saving as it will vary on users to users.

does not the chdir change the default to k:\caps?

ck1999

 

[peace77]

I added full path name under FileName and it resolved the problem. Thanks combo for the suggestion.

Thanks,

Peace