FAO Ken Snell - RefreshAll

[Snowsride]

Ken

You were good enough to provide the code below to open an Excel workbook:

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
On Error Resume Next
Set xlx = GetObject( , "Excel.Application")
If Err.Number <> 0 Then Set xlx = CreateObject("Excel.Application")
' Comment out the next line if you don't want EXCEL to be "seen" by user
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\Filename.xls"), , False
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
'
' do other things here
'
Set xlc = Nothing
Set xls = Nothing
xlw.Save
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing

Two things...

Access highlights in red (error) the line Set xlw =
xlx.workbooks.Open("C:\Filename.xls"), , False - my solution was to delete
,,False Don't know what the parameters were for anyway!

The workbook has some code that runs on the Workbook_Open event that
includes Workbooks("MyWorkbook.xls").RefreshAll to refresh data links back to
the Access database that runs your code. The workbook opens but the
RefreshAll statement causes an error "ODBC MS Access Driver Login Failed"
'The database has been placed in a state by user 'Admin' on machine xxx that
prevents it from being opened or locked.'

If I click on the OK button I get a dialog box with a Login name field with
admin already entered and a blank field for a password.

If I substitute your code for the following:

Shell "c:\Program Files\Microsoft Office\Office\Excel.exe " _
& "c:\MyWorkbook.xls", vbMaximizedFocus

then it works OK but of course a new instance of Excel is opened each time
and if the user switches between Excel and Access to refresh the tables and
back to Excel multiple instances are created.

Hope you can follow the above and grateful for any help.

Regards

 

[Ken Snell [MVP]]

One must be so careful when copying stuff from old files < grin > .. my
error on the ", , False" stuff. It should be
Set xlw = xlx.workbooks.Open("C:\Filename.xls", , False)

The False argument in the above line is so to open the file in "not read
only" mode. This is the default value for that argument so leaving it out is
ok.

You can turn off the macros in the EXCEL workbook by using the
Excel.Application.EnableEvents property. This allows you to turn off or on
the events in EXCEL.

So we can modify the code that I'd given to you this way:

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
On Error Resume Next
Set xlx = GetObject( , "Excel.Application")
If Err.Number <> 0 Then Set xlx = CreateObject("Excel.Application")
' Comment out the next line if you don't want EXCEL to be "seen" by user
xlx.Visible = True
' turn off EXCEL's events so that event procedures won't run in EXCEL
xlx.EnableEvents = False
Set xlw = xlx.workbooks.Open("C:\Filename.xls", , False )
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
'
' do other things here
'
Set xlc = Nothing
Set xls = Nothing
xlw.Save
xlw.Close False
Set xlw = Nothing
' turn on EXCEL's events
xlx.EnableEvents = True
xlx.Quit
Set xlx = Nothing

 

[Snowsride]

Ken

Haven't had time to check out your revision but I'm not sure I understand
why I would want to stop the Excel macro from running? I refresh the tables
in Access and then open the Excel workbook which must have the data links to
Access refreshed. If set EnableEvents to false then the Excel data won't get
refeshed.

Am I missing something here?

Regards

 

[Ken Snell [MVP]]

I was assuming that you didn't want to refresh while you had your ACCESS
database open. That error that you're getting appears to be caused by the
fact that your EXCEL file is trying to open the ACCESS file that just opened
the EXCEL file -- a circular process. And once you've opened the ACCESS file
and are running code, you can't have EXCEL open it as well in order to
access the data in ACCESS.

You'll need to think about a different approach for how you get data into
EXCEL, or else how you refresh the EXCEL data from ACCESS. I can't offer
other suggestions because I don't know what you're doing in the EXCEL file
specifically.

 

[Snowsride]

Ken

Now understand why you would disable the macros. My final solution was for
Access to disable the macros and open the workbook and then quit Access. The
RefreshAll action is now attached to a button in the workbook. Not as
elegant as I had hoped but it works.

Regards