VBA unable to save Excel file sometimes

[automaker]

I have an issue with a VBA program I wrote (that is the first problem) in Excel. The program is saved in its own workbook (call it progbook). The program opens two other Excel files. One file contains data (call it databook) and the other file contains summary tables and charts (call it chartbook). The program reads data from databook and summarizes the data in chartbook. When I close databook, I don't want to save any changes that might have been made so I use

workbooks(databook).close false

When I close chartbook I do want to save changes so I use

workbooks(chartbook).close true

The program seems to close databook fine but I get an error trying to save chartbook. The error I get is a 1004 error with a description of "document not saved". Using the debug option to go to the editor, the close method line is highlighted. Any attempt to change anything in the code results in an error of something like "can't load dll" and the changes are ignored. I have to manually close the workbook chartbook and then I can make changes in the code.

The code is lengthy because there is a lot of data that is processed, tables made, and charts updated. The code is also a mess because management's vision of the final report continues to evolve. My question at this point is what can cause Excel not to save a workbook? I have tried
workbooks(chartbook).save
I have tried
workbooks(chartbook).saveas [complete path]
No luck with any of these. I seem to have tilted the jupiter rings out of alignment and I don't know how I did it.

Thanks for any help.

Mark

 

[PHV]

What about this ?
Workbooks(chartbook).Save
Workbooks(chartbook).Close

Furthermore you may try to close chartbook before databook

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[automaker]

PHV,
I did try the first suggestion. Same problem. It won't execute the save line. I have not tried the second suggestion. I will do that and get back to you.

By the way, when I tried

workbooks(chartbook).saveas [complete path]

I get the message that the file already exists and do I want to replace it. When I click "yes" then I get the error document not saved.

Thanks,
Mark

 

[SkipVought]

Is your FOLDER full?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[automaker]

Skip,
Folder is not full. I am saving to my hard drive right now as I am developing the program.

Thanks,
Mark

 

[automaker]

Ok. I tried saving the chartbook before closing databook. Same "document not saved" error on the save line of code. Also, the wording of the dll error is "Error loading dll". I received that error when I followed the debug button to the editor and tried to make code changes.

Mark

 

[PHV]

You may try to repair your office install.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[automaker]

I did the google thing and went to the MSDN site. Here is what is out there.

Visual Basic Reference: Error Messages
Error in loading DLL (Visual Basic)

A dynamic-link library (DLL) is a library specified in the Lib clause of a Declare statement. Possible causes for this error include:

The file is not DLL executable.

The file is not a Microsoft Windows DLL.

The DLL references another DLL that is not present.

The DLL or referenced DLL is not in a directory specified in the path.

To correct this error
If the file is a source-text file and therefore not DLL executable, it must be compiled and linked to a DLL-executable form.

If the file is not a Microsoft Windows DLL, obtain the Microsoft Windows equivalent.

If the DLL references another DLL that is not present, obtain the referenced DLL and make it available.

If the DLL or referenced DLL is not in a directory specified by the path, move the DLL to a referenced directory.

Off hand, none of this seems to apply to me. I admit I am mostly ignorant of DLLs but I am not specifying anything that I can see would affect DLLs.

Could I be missing a reference of some type?

Mark

 

[automaker]

If I go the route of repairing the office install, is there something I can run from my computer (such as checkdisk or system file checker or something like that) or are you suggesting I contact our IT folks and get Office reloaded?

Mark

 

[automaker]

I am at work now using my work computer. I will take the files home and see if I have the same issues. If not, then maybe a fresh install is the answer. I will keep you posted.

Mark

 

[Gavona]

All this is via code. Can you save the workbook manually? Sometimes the error message is easier to follow and you eliminate disk full and network errors etc as the cause.

Gavin

 

[automaker]

Gavin,
Yes, I can save the file manually. Good suggestion.

Because it seems everything is fine with the other files that I open, I decided to sort of get rid of the chart file. I manually opened the chart file and for each tab in the workbook I copied it to a new workbook. Then I had the program use the new workbook. I made a couple of runs and it appears to be working.

Thanks to all for the help.

Mark