Invoking Excel from Access VBA code

john john mackey

I am having real difficulty in opening a specific .XLS file from within Access. I want to call my Excel file from Access to work with charts (templates) and then tweek the values of the charts based upon data from Access tables. I used the Wizard to create the general subroutine and then tried to tailor it to open up my specific file - what I have below has SERIOUS problems when switch between worksheets in the .XLS file and the cursor opens as Hour-Glass until moved out of grid/cell area. Any help on how to do this, where to look? :confused: Thanks, Johnny Here is my code from Access VBA code:

Private Sub cmdRunExcel_Click()
On Error GoTo Err_cmdRunExcel_Click
    Dim oApp As Object
    Dim xlApp As Object
 
    Set oApp = CreateObject("Excel.Application")
    Set xlApp = GetObject("C:\work\myDB_Charts.xls")
    oApp.Visible = True
    xlApp.Application.Visible = True
    xlApp.Parent.Windows(1).Visible = True
 
    'Only XL 97 supports UserControl Property
    On Error Resume Next
    oApp.UserControl = True
 
Exit_cmdRunExcel_Click:
    Exit Sub
 
Err_cmdRunExcel_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunExcel_Click    
 
End Sub

Garner T

I have been having trouble with Excel myself so I don't know if I can be of any help to you. My suggestion would be to try recording a macro of what you want to do. Copy and paste the code it generates into your subs and then tweak it to suit your needs. That got my program "close" to working but finding answers for Excel seems to be very difficult. Hope this helps, Garner

john john mackey

Thanks for the suggestion - I didn't think about recording a macro. I was able to get a working solution that is adequate for my needs. I basically followed the example that I found in the Object Browser's help for "GetObject" (in VBA library) It seems cumbersome to have to use the 2 API functions (to find a window handle to Excel and then "post" it to the Object table via a windows message), but in the end it works. I will now need to control operation in Excel (worksheet) from within Access and VBA. Ultimately, I will use a chart in my Excel file as a template, changing the values according to latest data in Access. Best regards, Johnny

Garner T

Johnny, Since you seem to know how to use VB & Excel together, is there any chance that you would have time to look at my Jan. 6th Post, "VB/Excel interaction"? I have tried everything that I can think of and I still can't make it work. Nobody else has answered. Thanks in advance, Garner