Cannot open Excel wbook from VB6

[vladk]

I run this code:

Dim objWbk As Workbook
Dim objExcel As New Excel.Application

Set objWbk = objExcel.Workbooks.Open("C:\Projects\2008 RateBase\PageMaker2008\RataBaseTemplates\AL\176_AL_PREM_LIMIT_FCTR.xls")

The workbook exists, I hardcoded it. The location exists also, it's on my personal drive.

Instead of opening this workbook, code opens 176_AL_PREM_LIMIT_FCTR1. This file does not exist at all. I save this ghost workbook:

objWbk.Save
objWbk.Close

Then I find 176_AL_PREM_LIMIT_FCTR1.xls in My Documents directory.... It happens with all workbooks (there about 200 in the folder, but one.... And it happens with all 50 folders, all of them have similar workbooks. They are not hidden, not read only, folders are not locked. Have no idea what is going on...

 

[Andrzejek]

When you run a Macro in Excel, it shows:

Workbooks.Open [blue]Filename:=[/blue]"C:\SomeFolder\SomeFile.xls"

You may try that.

Have fun.

---- Andy

 

[vladk]

Does not work

 

[TysonLPrice]

Seems starng. Check the task manager and see if you have multiple versions of Excel.exe running.

 

[vladk]

I check it all the time

 

[tsuji]

Should check if there's anything in workbook_open handler.

 

[vladk]

You mean, Workbook_Open event? These books have no code at all.

 

[vladk]

I found a way around: use SaveAs instead of Save. However, I don't know why Save works just for one book...

 

[tsuji]

One starts with talking about open() and ends up talking about save(). That's magical cycle of nature one would wonder.

 

[vladk]

I started talking about both open() and save().

The code opens not a very file, but rather it's clone with an extra digit at the end of the original name. As such, Save() saves not the original file but it's clone with the altered name, and it saves it in MyDocuments instead of the initial directory. SaveAs() seems corrects the problem since I type the entire path in the code.

However, the problem of not opening the original file with open() and not saving it in the same location with save() remains unresolved.

 

[strongm]

What error message do you get when you try to open the 'missing' files?

 

[vladk]

Hi strongm,

I don't have any error messages...

When I type Debug.print objWbk.Path after open() and before save(), it shows nothing, blank line.

When I type objWbk.Name after open() and before save(), it shows the name of the workbook, but without ".xls" and with an extra digit at the end of the name.

It ends up saving the book with all things I wanted, BUT with this extra digit, with ".xls" and in MyDocuments instead of the original directory.

For example, if the book was C:\Project\MyBook.xls, then it will become C:\Documents and Settings\MyName\My Documents\MyBook1.xls.

The last digit increments each time I run the code.

The initial book remains intact. I see this for all 50 directories, each has about 200 books.

Just one book in all directories behaves in the expected way, others - not.

Thank you for the response!

vladk

 

[RoyVidar]

Office Automation is sometimes a bit more quirky than other automation. My guess, and let me emphasize guess is that the implicit instantiation of the initial Excel object, has left one or more instances of Excel in memory (check it out with Task Manager), where at least one of them keeps the original workbook open, thus preventing later (implicit) instantiations to be able to open it.

Try with something along the lines of the following, and see if that makes any difference - explicit instantiation, and using createobject, which maps closer to the creation process used by most Visual C++ clients, and allows for possible changes in the server's CLSID between versions.

[tt] Dim objWbk As Excel.Workbook
Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")
Set objWbk = objExcel.Workbooks.Open("C:\Projects\2008 RateBase\PageMaker2008\RataBaseTemplates\AL\176_AL_PREM_LIMIT_FCTR.xls")[/tt]

If I'm not mistaken, I think the following

"When I type Debug.print objWbk.Path after open() and before save(), it shows nothing, blank line.

When I type objWbk.Name after open() and before save(), it shows the name of the workbook, but without ".xls" and with an extra digit at the end of the name."

means the workbook isn't saved. Only after it is saved, it gets a path and extension.

Roy-Vidar

 

[vladk]

Roy-Vidar

Of course, it isn't saved. But I don't want to see it neither saved or not. Because it is not the book I want to work with!

:0)

I will test your advice tomorrow!

Thank you!

Vlad

 

[vladk]

RoyVidar,

I tried your code, unfortunately, it did not solve the problem. I am puzzled, why one book works with save() and open(), while the others do not work...