[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2006-07-06
16
Medium Priority
?
4,265 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

650 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