Controlling Excel from Access

[mundo7]

Hi,

I use VBA on a button click to run a macro which uses the TransferSpreadsheet process to export a query to excel (and keeps it open).

What i want is to then format the excel workbook to how i like it using the VBA still.

Is this possible?

I am trying to get my head around how to make access realise that Excel is already open but am having trouble. I'm using code such as:

set xlApp = Excel.Application
set xlBk = xlApp.ActiveWorkbook

but to no avil.

Can someone help please?

 

[Kenln]

You could open (create) your own workbook and let access control that.

    Set xlApp = CreateObject("Excel.Application")

Use this to open a new workbook

    Set xlWorkBook = xlApp.Workbooks.Add

or use this to open an existing one

    Set xlWorkBook = xlApp.Workbooks.Open(strMyPath_and_FileName)

 

[mundo7]

Cheesr for the help,

however the file that i want to control is already open in excel, it hasn't been saved yet.

How do i state that it is this file i want to control?

 

[Kenln]

I'll pay around, I have never done that so I'm not sure. I'm not sure how it would identify which excel you wanted. What if you had more than one open. Even if you don't you have to be ready for the possibility.

 

[mundo7]

that's true, i never thought what would happen if ou had more than one open.

This is the process:

1. user selects from a combo box.
2. user clicks a button
3. clicking button runs some code which runs a macro to export a query to excel based on the combo box result.
4. (this is where i want to control excel).

 

[Kenln]

If I understand you correctly and the spread sheet is already open the process should read:

1. Open Excel
2. Preform whatever user functions are required
3. user selects from a combo box.
4. user clicks a button
5. clicking button runs some code which runs a macro to export a query to excel based on the combo box result.
6. (this is where i want to control excel).

Right???

However if the new (not yet saved) spreadsheet is open soley for the purpose of receiving the exported data then I would suggesting opening it later in the process.

1. user selects from a combo box.
2. user clicks a button
3. Clicking button runs some code which opens Excel
4. having clicked the button it continues and runs some code which runs a macro to export a query to excel based on the combo box result.
5. (this is where i want to control excel). <- Not sure what you want to do here
6. Preform whatever user functions are required

If this is the case I would use the code snippet I posted above. Then instead of using an export to excel open a recordset and copy that directly to the WorkSheet you wish, probably Sheet1 since this is a new book just opened.

    Dim DAODb As DAO.Database
        Set DAODb = CurrentDb
    Dim MyRst As DAO.Recordset
        'Use if you are building a recordset form a sql statement
        Set MyRst = DAODb.OpenRecordset(strMySql)
        'Use if you are building a recordset form an existing query
        'Set MyRst = DAODb.OpenRecordset("Query1")

        ' Set the worksheet you wish to use
        Set xlWorkSheet = xlWorkBook.Worksheets(1)
        xlWorkSheet.Visible = xlSheetVisible
        xlWorkSheet.Select
        xlWorkSheet.Activate

        ' Paste the data form your recordset you defined above.
        ' Note the cells are (Row, Column)
        xlWorkSheet.Range(xlWorkSheet.Cells(1, 1), xlWorkSheet.Cells(1, 1)).CopyFromRecordset MyRst

        MyRst.Close
        Set MyRst = Nothing
        Set DAODb = Nothing

        Set xlApp = Nothing
        Set xlWorkBook = Nothing
        Set xlWorkSheet = Nothing
        Set xlRange = Nothing

Just a thought