I run my VBA macro in a excel file which shown the message "Error 1004: SaveAs Failed". Then I have found that the possible reason(s) for occuring this error:
1. The command "Application.DisplayAlerts = False" failed to run
2. The command "ActiveWorkbook.SaveAs Filename:=sOFnp, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False" can run but it's still show the error message (when sOFnp is the completed path)
I need uregnt help to debug, please reply if everybody knows. I'm waiting yours, Thank you very much.
Office Version: MS Office 2000
File Path: Y:\MY_DOC\ED\YICK0002-20060613-AC20060331-CSV+PT-V1-KE\1+4.xls (Y: is a mapped drive)
Error Background:
The Script is run after the previous scripts of saving Excel file as CSV format. Then I need to resave as Excel file format again
Script needed to debug:
Sub xlSave()
' xlSave Macro
Application.DisplayAlerts = False 'Disable Alert Message
Sheets("S1").Select
Range("A1").Select
ActiveWorkbook.SaveAs Filename:=sOFnp, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
msgCvtFinished = MsgBox("Job Done。", , "Message")
msgClrRequest = MsgBox("Need Clear Data ", vbYesNo + vbDefaultButton2 + vbQuestion, "")
If msgClrRequest = vbYes Then
Sheets("S1").Select
vCntd = vCnta + 3
vCntd = CStr(vCntd)
vCnte = "A4:U" + vCntd
Range(vCnte).Select
Selection.ClearContents 'Clear Contents in Worksheet "S1"
Range("A4").Select
Application.DisplayAlerts = False 'Disable Alert Message
ActiveWorkbook.SaveAs Filename:=sOFnp, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
msgClrFinished = MsgBox("Cleared。", , "Meesage")
End
Else
End
End If
End Sub
[URGENT] Need Help for "Error 1004: SaveAs Failed"
theFaze
Hi,
Couple of things...
Check that sOFnp has a value
Your calling the save as function twice with sOFnp but you are not changing it's value. SaveAs keeps the file open so if your not changing sOFnp's value your saving to a file thats open and is read only, this would give you the error. You might need to use SaveCopy.
Also check you have write access to the folder your saving to.
mcrfanatic
Halo, Derek...
Thanks for your appropriate response. I'm sorry for my late expression of acknowledgement to you.
However, I'm afraid that it's not easy to solve, since:
1. In fact, I'm a technical support of a company in Hong Kong. This VBA program procedure of converting Excel Data into CSV format is designed by me. It's tested, debuged and operated for a year half;
2. The script of "ActiveWorkbook.SaveAs Filename:=sOFnp, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False" is no problem to run when the excel file is still opening in my colleagues' workstation and mine (using Windows 98 SE) both before the time of the problem occured;
3. I've checked the value of sOFnp = "Y:\MY_DOC\ED\YICK0002-20060613-AC20060331-CSV+PT-V1-KE\1+4.xls", which is same as the original file path in my first post, is no need to change because of the function in receovering the Excel file from CSV format to XLS format;
4. In addition, the file is stored in the mapped drive which is in server (Windows 2003 Server Std) actually. The priviledge of my user account is "completed control" in the server, so that I am sure I have the priviledge of write access;
5. By the way, my workstation has been changed a lot, since my present is still a temporary one, which I found that my NIC seems getting some problem of missing packets, but it's not easy to check and finally I can't.
Maybe can I get your email for sending code to you for check If OK, I try my best to settle this problem, thanks for your kind attention a lot and waiting your response.
azouz
Hey Ronin,
Chinese might be a problem. Do you use chinese characters in your code If you use Chinese variable names written in English characters then it will be ok. Send me it anyway yeah and I'll have a look on Friday when I get some time. Might be I cannot do anything but won't know that till I see it.
cj74
Hi Ronin,
Your right your code works fine with the save as. Not sure why your having to resave the spreadsheet once you export the data to csv but you know how the system works.
Have a look here and see if that helps, try changing xlNormal to 1...
http://support.microsoft.com/default.aspx scid=kb%3Ben-us%3B292482
Its very difficult for me to debug this error because your code is working fine here and I don't have the same network configuration you have. It's very difficult.
You could still send me the code if you like. I won't have much time to look over it and it would have to be the weekend. I'll give it a shot though. Sometimes a fresh pair of eyes can help.
Chris Nahr
Thanks for your help, Derek.
Though I think I got the solution to debug, I still hope you can check all the script with excel file. This is a program to convert accounting data from Excel file to generate many CSV format files for implementing them into Peachtree Accounting. Thus, would you accept my request of checking my program script and offer your opinion to me for improve also, my program is traditional chinese mainly to operate, will it be the obstacle to you for check and run program
I'm willing to wait your response for further matters. Thanks for your kind attention.