CreateObject("Excel.Application") - Suppressing Excel Error Messages

Question

Hi,

I am trying to open a password protected Excel 2010 file from within Access 2010. The file contains a list of passwords which I do not want the user to see in Access - so instead Access should open the file behind the scenes and extract the relevant password data, without the user seeing what's happening in the background.

I tried using ADODB.Connection to do this but realised that it will not work with a password protected file. I also tried importing the Excel sheet into a table using VBA but yet again the password creates a problem.

Instead, I'm now using CreateObject("Excel.Application") which works but with 1 problem: Excel loads but comes up with a message saying it cannot find an XLA file which it is looking for (unsure why - seems to be related to the ribbon). The problem is I don't want the user to see or interact with Excel so any errors should just be bypassed by Excel and not displayed on the screen. I saw the idea of using Application.Visible = False and Application.DisplayAlerts = False, but these can only be set after the object is created so they do not help.

Is there any way of supressing these error messages at the point when the Excel.Application object is created? Alternatively if anyone can suggest a better way of accessing a password protected Excel file, feel free!

Another alternative I tried was to use CreateObject("Excel.Sheet") instead. This got rid of the XLA error as it loads all add-ins. However, all the addin splash screens appear and while all the add-ins are loading, I get error "Automation error. The message filter indicated that the application is busy". I can't seem to win!

Many thanks for your help!

Answer 1

Hi UpsideDown55,

try in this way

Dim objExcel As Excel.Application
Dim xlWkBook As Excel.Workbook
Dim xlWkSheet As Excel.Worksheet

                Set objExcel = New Excel.Application
                objExcel.Visible = False
                Set xlWKBook = objExcel.Workbooks.Open(name of your excel file)
                Set xlWkSheet = xlWB.Worksheets(name of your sheet)

so the XL object doesn't shows up

HTH Paolo

Answer 2

Hi Paolo_S,

Thanks for your suggestion. This was in fact very similar to my code (I have now pasted this at the bottom of this msg). Unfortunately the following line still causes the same behaviour:

Set objExcel = New Excel.Application

It seems that instantiating a new Excel.Application causes Excel to be loaded and I can't see any way to send any parameters to tell VBA to suppress any on load error messages. So by the time it gets to objExcel.Visible = False it's too late already.

Below is my current code:

Private Sub importPWOLD()
Dim oExcel As Object
Dim owB As Object



    Set oExcel = CreateObject("Excel.Application")
    oExcel.Application.Visible = False
    oExcel.Application.DisplayAlerts = False

    Set owB = oExcel.Application.Workbooks.Open(FileName:=FPATH & "Storage.xlsx", Password:="test1")

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TEMP1", FPATH & "Storage.xlsx", True, "Sheet2!C:E"
    owB.Close SaveChanges:=False

    oExcel.Application.Quit
    Set oExcel = Nothing
    
End Sub

Answer 3

Hi again,

to avoid interaction by the user you can try also this (air code, untested)

 Set objExcel = New Excel.Application
 objExcel.Visible = False

 objExcel.interactive=False

 objExcel.displayalerts=false

give it a try.

And be sure to include in the references Microsoft Excel xx.x object library

Cheers Paolo

Answer 4

Hi UpsideDown,

Welcome to the MSDN forum!

Would you please provide the detail information about the error message? You may upload a screen shot of the error message.

Also, the following code works fine on my computer:

Sub OpenExcel()
Dim objExcel As Excel.Application
Dim xlWkBook As Excel.Workbook
Dim xlWkSheet As Excel.Worksheet

Set objExcel = New Excel.Application
objExcel.Visible = True
Set xlWkBook = objExcel.Workbooks.Open("D:\00-Excel\TestPassword.xlsx", Password:="123")
Set xlWkSheet = xlWkBook.Worksheets(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbNames", "D:\00-Excel\TestPassword.xlsx", True, "Sheet1!"
End Sub

Thanks.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 5

Hi Paolo_S,

Thanks, but it's the same issue - when VBA hits the first line of code Set objExcel = New Excel.Application, it loads up Excel and displays the error message I want to hide. All this happens before VBA even moves to the next line of code  objExcel.Visible = False

Thanks

Answer 6

Hi Yoyo,

Thanks for your welcome.

I have attached the error message. The reason I didn't include it originally is because it relates to a specific add-in in Excel, which is probably not widely used. This is why you would not have the same error on your PC.

Brief background:

Our Excel loads a "Hyperion" add-in and also adds this to the Add-Ins bar in Excel 2010. When VBA creates a new Excel.Application object, Excel is opened with add-ins disabled. However it seems Excel is still looking for the add-in - perhaps for the ribbon.

The error message is a bit of a mystery to me as the add-in is not stored in the folder which Excel is looking in, but it always loads fine when add-ins are enabled!

Thanks

Answer 7

Sorry, but there seems to be something freaky going on with inserting images into posts at the moment, so it's only displaying the top of the image!

The text of the message is:

'\Documents\hstbar.xla' cannot be found. Check your spelling or try a different path.

Answer 8

Hi UpsideDown,

Thanks for your response.

Does "Hyperion" add-in add button or other control onto the ribbon?

"The error message is a bit of a mystery to me as the add-in is not stored in the folder which Excel is looking in, but it always loads fine when add-ins are enabled!"

Do you mean that you can manually open the excel with all the add-ins enabled?

Does the error message display when you run the open to open the excel with the add-ins disabled?

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 9

Hi Yoyo,

I'm not sure what is looking for this addin.

I have had so many problems accessing Excel data using this option that I have just decided to put the data into Access and password protect the database. It's a shame that  after 12 years+ of modern VBA, it's a nightmare to access password protected Excel files and pull data out of them.

Thanks for your help anyway!

Answer 10

How about if you use Shell to start Excel in safe mode (there's a command-line switch for that), and then use GetObject to get a reference to that Excel instance for further automation?  If you start it in safe mode, it won't try to load any add-ins, will it?

I think the code would be something like this:

    Dim objXL As Excel.Application
    
    Shell "excel.exe /safe"
    
    Set objXL = GetObject(, "Excel.Application")

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 11

Hello UpsideDown55,

I tried code similar to the code that Yoyo used and the code ran successfully for me and kept Excel hidden through the loading process. 

Dim oExcel As Object
Dim owB As Object

    Set oExcel = CreateObject("Excel.Application")
    oExcel.Application.Visible = False
    oExcel.Application.DisplayAlerts = False

    Set owB = oExcel.Application.Workbooks.Open("c:\testtemp\book1.xlsx", Password:="password")

    owB.Close SaveChanges:=False

    oExcel.Application.Quit
    Set oExcel = Nothing

As Dirk mentioned, you could try the Shell command to see if you receive any different behavior with this.  If not, what happens if you remove the add-in or add-ins from the machine?  Does the code run successfully then?

Best Regards,
Nathan O.
Microsoft Online Community Support

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.