Controlling Excel from Access

[DeutchBose]

I know there has to be a way to do this:

From a Microsoft Access Module or Macro, I want to go into an excel spreadsheet and refresh a query.

Any ideas on where I would start?

 

[mdusoe]

Something like this, perhaps?

    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.workbooks.Open("filename.xls)
    Set xlSheet = xlWorkbook.Sheets("MySheet")

    xlSheet.QueryTables("myQueryTable").Refresh

The cool thing is, once you have xlApp defined, all of the objects, methods, properties, etc. associated with Excel become available to you.


You can also, in the VBE for Access, go to Tools --- References and check the Microsoft Excel libraries. Then, you don't need the createobject piece. But, if you are using your database amongst various users who have various versions of Excel, you could run into problems (i.e. Excel 9.0 or 10.0 library?) So, I chose to do it this way.

HTH,
Mike.

 

[Norie]

You can also, in the VBE for Access, go to Tools --- References and check the Microsoft Excel libraries. Then, you don't need the createobject piece.

I don't think that is quite right.

The advantage of checking the libraries is that you will have access to the dropdowns that list properties and methods for Excel objects.

 

[mdusoe]

If you have the reference checked, you can create a new instance just by using:

Dim xlApp as New Excel.Application

as in:

Sub test()
    Dim xlApp As New Excel.Application
    xlApp.Visible = True
    xlApp.Workbooks.Add
End Sub

Again, I don't recommend using it for any situation where there is a potential difference in versions, and this is why I didn't provide the details in my earlier post. But it does work, and I have used it in the past for other solutions.

Please, Norie, don't think me rude. I just want to provide thorough information.

Mike.