Solved

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

Posted on 2006-07-06
16
3,976 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
  • 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now