Solved

Automation using Excel says, "ERROR 1004: Open method of Workbooks class failed"

Posted on 2006-07-06
16
4,135 Views
Last Modified: 2010-11-27
I have an Access 97 database that runs code overnight via an automated batch process.  I create various XLS files for my clients throughout the office.  While the code within Access always runs on Access 97, some clients have Excel 97 installed while others have Excel 2003.  The following process works just fine with Excel 97, but on a computer with 2003, it errors.  I'm looking for a solution that can work independent of the Excel version a user has installed.

I have a report (again, in Access 97) that is output to an XLS file during this nightly process via the following line of code:
DoCmd.OutputTo acOutputReport, strDocName, acFormatXLS, strTempFileName

I then want to simply open the XLS document, format the cells for my clients, then save the XLS document again.
It currently looks like this:


Public Function XLCellFix(strTempFileName As String)

On Error GoTo Error_Handler
                               
    'Bind so that it will run on any machine
    Dim oXL As Object
                               
    Set oXL = CreateObject("Excel.Application")
    With oXL
        'Open the passed-in XLS document
        .application.Workbooks.Open (strTempFileName)

        'Autofit all rows and columns for easier viewing by customer
        With .ActiveSheet.Cells
            .Select
            .EntireRow.AutoFit
            .EntireColumn.AutoFit
        End With
        .ActiveSheet.Range("A1").Select

        'Close document and automatically save the changes
        oXL.application.Workbooks(1).Save
        .Quit
    End With
    Exit Function

Error_Handler:
         AddToLogFile "ERROR " & Err.number & ": " & Err.Description & " (" & Err.Source & ")", LOG_ERROR
         Resume Next

End Function



On the computers with Excel 97, it correctly opens Sheet1, formats, and closes.
On the computers with Excel 2003, it bombs on this line of code:
      .application.Workbooks.Open (strTempFileName)

and I see the following error in my logfile:
      "ERROR 1004: Open method of Workbooks class failed (Microsoft Office Excel)"

When I open the file manually through Excel 2003, I see the problem - a dialogue box pops up that says:
"Errors were detected in 'filename.xls,' but Microsoft Excel was able to open the file by making the repairs below.
    Renamed invalid sheet name."

The sheet has been renamed to Recovered_Sheet1 in this case.  Saving it manually will fix the problem and let the code run on the XLS file, but that obviously defeats the purpose.  I cannot find a way to automatically close the error message box; Access 97's automation just throws that error and wont open the XLS file.

Any help would be greatly appreciated!!!
0
Comment
Question by:Skkra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17050591
just try

.Workbooks.Open (strTempFileName)

instead
0
 

Author Comment

by:Skkra
ID: 17050652
Tried it... no go.  Same issue.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17050658
ok, hang on, I just read the last part, didnt scroll down enough
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 65

Expert Comment

by:rockiroads
ID: 17050678
Now Im thinking the fact you got a corrupt spreadsheet, should you really be working on it?
Have u looked into why it keeps getting corrupt?
0
 

Author Comment

by:Skkra
ID: 17050814
Its not actually corrupt, really.  The data is all fine no matter how I open it, and there is nothing wrong with opening the spreadsheet in Excel 97.  I just that odd error when using Excel 2003, and even then its only concerning the sheet name for some reason.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17051381
Would specifying Converter help in this case?
Without myself reproducing this, I can only give suggestions


expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad, OpenConflictDocument)


Password  Optional Variant.  A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.

WriteResPassword  Optional Variant.  A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password.

IgnoreReadOnlyRecommended  Optional Variant.  True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).

Origin  Optional Variant.  If the file is a text file, this argument indicates where it originated (so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly). Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.

Delimiter  Optional Variant.  If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use "," for commas, use ";" for semicolons, or use a custom character. Only the first character of the string is used.

Editable  Optional Variant.  If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it’s a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option doesn't apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False.

Notify  Optional Variant.  If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.

Converter  Optional Variant.  The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter doesn’t recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.

AddToMru  Optional Variant.  True to add this workbook to the list of recently used files. The default value is False.

Local  Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

CorruptLoad  Optional Variant. Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The Default behavior if no value is specified is usually normal but may be safe load or data recovery, if Excel has already attempted to open the file. The first attempt is normal. If Excel stops operating while opening the file the second attempt is safe load. If Excel again stops operating the next attempt is data recovery.

OpenConflictDocument  Optional Variant. True to open the local conflict document. Default is False.

0
 

Author Comment

by:Skkra
ID: 17051615
I definitely understand you can only give suggestions without having this yourself.  Sadly, I actually tried specifying converter before with no luck.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17051911
Try this...when creating the object, also specify the sheet

    Set xlWb = xlApp.Workbooks.Open(strFile, True)
    Set xlWs = xlWb.Worksheets(strSheet)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17052069
Have a look at   CorruptLoad  

u see there are thee values that it can be

xlNormalLoad
xlRepairFile
xlExtractData

read the help on that, that may be the one (and not Jet Li)
0
 

Author Comment

by:Skkra
ID: 17052070
Sorry; it still fails.
I think I may just have to code some convoluted method to work around it using C#.NET externally or something...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17052322
Does that excel sheet fail fail generally?
I was wondering if u could upload it in something like http://www.ee-stuff.com (minus your sensitive stuff)
so I can have a look to see if I can reproduce it
0
 

Author Comment

by:Skkra
ID: 17052716
No, it does not fail generally.  Sadly I cant upload a file where you could see it yourself.  The data the report runs from is confidential production data, and if I open the file to edit those values to dummy ones, the error will stop happening.  Sorry.

I ended up having to recode it in the macro of ANOTHER excel sheet, which I the open via Access and run the macro.  Seems to work once excel 2003 is already open with my "valid" XLS file, but sadly nothing mentioned above would seem to do it from Access, which frustrates the heck out of me - my way is such a convoluted workaround...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17053402
Pity, I thought the parameters may help, especially  CorruptLoad  as that sounds like the solution to your issues

When u tried it, did u try all three options (I guess xlNormalLoad is the default)

also after u open the workbook, set visible=true so u can see what is going on

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17053410
Perhaps that may be a workaround? setting visible to True so that the user can see what is going on and act accordingly
0
 

Author Comment

by:Skkra
ID: 17053565
I have actually had Visible set to true this entire time for my debugging purposes, but this is run overnight in a batch process, so no users will be interacting with it.

Yes, I tried all three recovery options.  Sadly none of the CorruptLoad parameters could solve the problem UNTIL I ran them inside of that XLS file I created, in a macro.  However, thank you very much for all of your help, I appreciate it.  I will award you the points for your effort, and since you halfway found the solution; it just couldnt be done inside Access for whatever freak reason!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17053663
Real shame, sorry I couldnt help further
thanks for rewarding me though, appreciated
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question