Opening a spreadsheet without warnings

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is one I can't find any help on. I was using early binding to open a
workbook, but because of different versions of Access and Excel we have, I
have to change it to late binding. Using early binding, the following would
allow me to open the workbook without getting a warning message about
updating external data sources:
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0)

Since I now have to use the GetObject:
Set xlApp = GetObject(varGetFileName)

I can't find a way to supress the message.
Any assistance will be appreciated.
 
Try using this code for late binding:

Dim xlApp As Object, xlBook As Object
Set xlApp = GetObject(, "Excel.Application")
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0)
 
Thanks, Ken. It worked. I was trying that approach, but I did not do it
quite right. I appreciate the help.
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Klatuu said:
Thanks, Ken. It worked. I was trying that approach, but I did not do it
quite right. I appreciate the help.
Click to expand...
 
By the way. I did have to make one change. If there is no instance of Excel
running, an error occurs (expected) and no object is created, so I have to
test to see if the GetObject returns an error. If it does, then I have to
use a CreateObject to establish an instance of Excel so the open will work.

Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
End If
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0)
 
Probably safer if you check

If Err.Number = 429 Then

just in case there's some other error that you need to handle.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Error handling is for weinies :)

Thanks, Doug. I actually have already changed the code to that.
 
Back
Top