Opening Documents with VB

[Guest]

I have a macro that I am running to open and filter a query. I need to send this query to excel after it has been filtered, which I have successfully done. I wanted to send it to a workbook with some automatic macros loaded, but the problem is that when I output the query to excel, it copies over the old file and makes a new one. My solution to this was to have the query output to excel, and then have the workbook with the loading macros open, and have the data that was outputted to the original excel sheet be transfered to this one.

The problem is that I don't know how to open an excel file from an access macro. I did a RunCode action, but am having problems getting the code right, any input on how to do this in VB would be greatly appreciated.

Brian

 

[Ken Snell]

You would use the RunCode action in a macro to run a public function that
contains code based on the following generic code (this code writes the
value of a variable to a specific cell on a specific worksheet in a specfic
workbook):


Dim xlsApp As Object, xlsWB As Object, xlsWS As Object, xlsRng As Object
Set xlsApp = CreateObject("Excel.Application")
Set xlsWB = xlsApp.Workbooks.Open("C:\FolderName\FileName.xls", , True)
Set xlsWS = xlsWB.Worksheets("WorkSheetName")
Set xlsRng = xlsWS.Range("A1")
xlsRng.Value = VariableName
Set xlsRng = Nothing
Set xlsWS = Nothing
xlsWB.Close True
Set xlsWB = Nothing
xlsApp.Quit
Set xlsApp = Nothing


--
Ken Snell
<MS ACCESS MVP>

I have a macro that I am running to open and filter a query. I need to

send this query to excel after it has been filtered, which I have
successfully done. I wanted to send it to a workbook with some automatic
macros loaded, but the problem is that when I output the query to excel, it
copies over the old file and makes a new one. My solution to this was to
have the query output to excel, and then have the workbook with the loading
macros open, and have the data that was outputted to the original excel
sheet be transfered to this one.

The problem is that I don't know how to open an excel file from an access

macro. I did a RunCode action, but am having problems getting the code
right, any input on how to do this in VB would be greatly appreciated.