Runtime error 1004 Document Not Saved

[rahulwagh]

Hi,
I have written one macro in excel 2010.
I am trying to save the excel file using below code
Application.Wor kbooks(Dir(repo rtName)).SaveAs
Here report name is the compelte path of the file

But getting runtime error 1004 Document Not Saved.

Please help.

[Killer42]

I think you have the syntax slightly wrong. According to the help, the syntax is:
expression.Save As(FileName,... )

Where expression identifies the workbook you want to save (not the file to save it in). At a guess, that business with Dir() looks as though it probably belongs in the FileName parameter.

[Rabbit]

You are in the wrong forum. Your thread has been moved to the Excel forum.

Ditto on what Killer42 said. But also, you will need to find the correct index for the Workbook. Most likely 0 if that's the only workbook open.

[rahulwagh]

Expression is the object of Excel.
For e.g. Excel.Applicati on
But I am not creating object instead I am trying
Application.Wor kbooks(Dir(repo rtName)).Save


Purpose to use Dir(reportname) is since there are multiple excel files opened so rather than providing entire path just passing the name of the excel using Dir

I am trying to save the excel multiple times.
After 3rd it fails.

[Killer42]

Ah, I see, you were using Dir() to pick up the file name and using that to select the workbook, instead of referring to it by the index number. Interesting. Also seems a bit risky. Is there anything else in the folder?

Also, are you using Save or SaveAs? You've mentioned both.

And what's in ReportName?

[zmbd]

Really making this way too complicated:

rahulwagh:
The function DIR() should NEVER be used in this fashion. It is a low level DOS command intended for use in file IO and manipulations. The fact that you got away with using this in XP points to the fact that the compiler was, and remains, a buggy and broken product.

I need you to clarify what is that you are trying to do:
Are you simply trying to save the changes to the workbook in the current directory?
Or
Are you trying to save the changes to the current workbook in a different directory?

[rahulwagh]

Hi zmbd,
I am trying to save the changed into current workbook into same directory.

When I replied to Killer42 I attached macro as well but don't know why it is not showing now.

[zmbd]

thisworkbook.save

This will save the changes to the calling workbook as though the user pressed <ctrl><s>.
Short and simple.

You did make an additional post; however, within it you started asking more questions and discussing other errors. You should have received a system message that it was removed for being off topic and containing multiple questions. We ask that each thread contain only one question. It makes it easier for the experts to keep track of the problem and for anyone searching for help on a simular question to find the help they need :)

[rahulwagh]

Hi zmbd,
Thanks for your reply.
"thisworkbo ok" refers to the Macro but I want to save the file which macro is processing.
I have asked question under -

http://bytes.com/topic/excel/answers/950712-excel-2010-macro-not-working-windows-7-but-same-works-perfectly-xp

Also have attached macro.
If possible could you please check it.

[zmbd]

Hi zmbd,
Thanks for your reply.
"thisworkbo ok" refers to the Macro but I want to save the file which macro is processing.
I have asked question under -

Well, you didn't explain that the Code was working on a different workbook than it is running in. That is vital information and not somehting that I should have to read thru dozens of lines of code to assertain.


Then you need

activeworkbook.save

It's not that hard.

[rahulwagh]

zmbd,

I got the solution of all the issues. And now my macro is working fine on Windows 7 as well.

There is some problem with the VBE7 Dll i.e. Visual Basic For Application in Windows 7. I replaced this DLL with XP VBE7 DLL and it is working without any issue.

[Killer42]

I'd call that a short-term workaround, rather than a solution. But it's good to hear that you got it working.

[rahulwagh]

Could you please tell me why it is short term workaround?

[zmbd]

You should NEVER use an OLD version of a library with new versions of the software.

1) The next person that has to maintain your program will not think kindly of you!

2) Replacing the new library with the old version may/will break the new software in ways that you can not anticipate.

3) Anyone using your "applicatio n" on a PROPERLY installed version of the software will run into the very same issue that you have done an end-run around and they will "thank you" many times over

4) When your company "upgrades" to the next version of the software, your installation will break and more than likely cause all sorts of headache for your IT support team - whom will "Bless" you all the way to a hot pool of lava

5) It is lazy programing. If you do this for a living, please do not expect your employer to be very happy with you once it is learned that you did an end-run around the sofware (see #3 above, when your supervisor runs the application!)

6) Quite frankly, I gave you the PROPER solution to the issue at hand - once you provided the correct information.

[rahulwagh]

The issue was with the VBE7 DLL. As I mentioned in my previous to replace new DLL with old one. I cancelled this solution.

The issue was with VBE7 DLL. The DLL was not properly registered. I unregister and re-register the DLL and that resolved the issue.