Refreshing an open excel file 1

[LonnieJohnson]

I know that I have been down this path before. How do you keep from opening multiple excel files? I have code that exports a query to an excel file and then opens it. Well the user will go back to Access and do more processing and possible hit the button to export more data to the excel file. Now how do I revisit the opened excel file with fresh data?

I'm using the shell function to open the file after I export the data

Shell ("C:\Program Files\Microsoft Office\Office\Excel.EXE c:\MyExcelFile.xls&quot, vbMaximizedFocus
prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[petersdaniel]

Which version of Access are you using. If you are able to use Access 200 VBA automation, you can skip the shell execution and create instances of Excel objects which can be controlled from the context of your application.

Example:
To use a pre-existing instance of Microsoft Excel with Automation, use the GetObject function and specify "Excel.Application" as the Class type. If an instance of Microsoft Excel already exists, the GetObject function will return a reference to the instance. If an instance of Microsoft Excel does not already exist, your code will cause a trappable run-time error, and you can use the CreateObject function to create one.

You can use the following function to determine if an instance of Microsoft Excel is running.

Function IsExcelRunning() As Boolean
Dim xlApp As Excel.Application
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application&quot
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear
End Function

And then, your code can determine whether it needs to create a new instance or not…

Sub ExcelInstance()
Dim xlApp As Excel.Application

Dim ExcelRunning As Boolean

ExcelRunning = IsExcelRunning()
If ExcelRunning Then
Set xlApp = GetObject(, "Excel.Application&quot
Else
Set xlApp = CreateObject("Excel.Application&quot
End If

'Other automation code here...

If Not ExcelRunning Then xlApp.Quit
Set xlApp = Nothing
End Sub

 

[LonnieJohnson]

Just what the doctor ordered! You da man. I was actually using A97 for this project, but the concept can still apply.

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.