RefreshAll Method fails

[Mike Collard]

I want to refresh an Excel spreadsheet and then save it
from an MS Access module. I have tried the following,
based on the MS Help for the GetObject function, which
opens Excel and the appropriate workbook but fails to
refresh the data.

Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final
release.

' Test to see if there is a copy of Microsoft Excel
already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument
returns a
' reference to an instance of the application. If the
application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Set the object variable to reference the file you want
to see.
Set MyXL = GetObject("c:\myfile.XLS")

' Show Microsoft Excel through its Application property.
Then
' show the actual window containing the file using the
Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows("myfile.XLS").Visible = True
MyXL.ActiveWorkbook.RefreshAll
MyXL.ActiveWorkbook.Save
MyXL.ActiveWorkbook.Close

' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit
method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunning = True Then
MyXL.Application.Quit
End If

Set MyXL = Nothing ' Release reference to the
' application and
spreadsheet.
End Sub

Any help appreciated.

Thanks

Mike Collard

 

[TC]

That code doesn't make sense. See comments below.

I want to refresh an Excel spreadsheet and then save it
from an MS Access module. I have tried the following,
based on the MS Help for the GetObject function, which
opens Excel and the appropriate workbook but fails to
refresh the data.

Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final
release.

' Test to see if there is a copy of Microsoft Excel
already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument
returns a
' reference to an instance of the application. If the
application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

Ok. If Excel was running, you now have a reference to it. If it was >not<
running, you should now do a CreateObject() to start it up & get a
reference. You do not do that CreateObject(), so, your code will only work
if Excel is already running.

' Set the object variable to reference the file you want
to see.
Set MyXL = GetObject("c:\myfile.XLS")

You do not use GetObject() to open a worksheet! You use methods of the Excel
object model, which is exposed through (available via) the MyXL object. I
don't have Excel here to check, but it is probably something like this:

dim MyWS as object ' for worksheet.
set MyWS = MyXL.open ("c:\myfile.XLS")

HTH,
TC

 

[Guest]

The GetObject statement does work - the Excel spreadsheet
opens OK but the RefreshAll statement fails.

 

[TC]

Ok. I thought the GetObject() funcion required a progid like
"Excel.Application". Apparently that is not the case.

BUT, this might be the source of the problem. First you do a getobject() to
get a reference to Excel (if it is running). Let's assume that excel is
running, so you get the reference ok. Now, the second getobject opens the
file. But could it start >another< instance of excel, to do that? If so, you
now have a reference to copy #1 of excel, and a reference to a worksheet
which is loaded into copy #2 of excel - to which you do not have a
reference! So, things you do to copy #1 of excel, might not affect anything
loaded in copy #2.

I haven't checked your code in detail, so this is just an educated guess.
But I think that it would be worth following up. In summary, I'm not sure
you can be confident that your exscel reference MyXL, is a reference to the
actual copy of excel in which the worksheet is opened!

HTH,
TC

 

[Guest]

TC

Thanks - I think you could be on the right track here.

Mike

 

[EddieA]

Mike,

Don't know if you've resolved this issue. I've been struggling with it
for two days. First working, then suddenly not. This is what I found.

It seems that you must do a CreateObject for an Excel Application,
Open the workbook and then do a RefreshAll on the work book. See code
below:
Dim xl As Excel.Application, r As Excel.Range, xlWorkBook As
Excel.Workbook

Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open "c:\lloydgroup\optima\MonthliesTemp.xls"
Set xlWorkBook = xl.Workbooks(1)
xlWorkBook.RefreshAll


xlWorkBook.Save
xlWorkBook.Close
Set xl = Nothing

That finally did it.

I think the reason we kept butting our heads up against a wall is
because the GetObject never failed, as the your code suggests.

Let me know if this helps,
EddieA