Auto_Open macro doesn't work when opening Excel from Access VBA

[coxyg]

Hi,

I have a simple access database that copies the query results to clipboard, access then opens up an Excel file and should run and auto_open macro. The problem is the auto_open macro doesn't work when access opens the excel file. But opening the excel file through windows explorer it works fine.

My Access VB is:

Private Sub Command4_Click()

'Copies to Clipboard Query Result'
DoCmd.OpenQuery "Project Programme", acViewNormal, acEdit
DoCmd.SelectObject acQuery, "Project Programme"
DoCmd.RunCommand acCmdSelectAllRecords
RunCommand acCmdCopy
DoCmd.Minimize
DoCmd.SetWarnings False
DoCmd.Close acQuery, "Project Programme", acSaveYes

'Open Up Excel File'

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "G:\Contracts database 2009\DG Project Programme\Project Programme.xlsm", True, False
Set xlApp = Nothing

'Closes Database'
Application.Quit

End Sub


My Excel Autpen is:

Sub Autpen()
'
' Autpen Macro
'

'
Range("A14").Select
ActiveSheet.Paste
End Sub



Any thoughts?
Regards
Coxyg

 

[CharlesChuckieCharles]

<CODE>I know if you run excel as a command line you also state the file to open and then something (used to be) "/M macroname" so something like shell "C:\office\excel.exe C:\mysheets\thiswk.xlsm /m Autoopen" BUT in your case is easier to do xlApp.RunAutoMacros xlAutoOpen</CODE> </PRE>

<TBODY>
</TBODY>

 

[Norie]

Why are you copying the query to the clipboard?

You could easily write code that would open Excel and copy the results of the query to the specified range.

For example.

Private Sub Command4_Click()
Dim rst As DAO.Recordset
Dim xlApp As Object
Dim xlWB As Object

    Set rst = CurrentDb.OpenRecordset("Project Programme")

    Set xlApp = CreateObject("Excel.Application")
    
    xlApp.Visible = True
    
    Set xlWB = xlApp.Workbooks.Open("G:\Contracts database 2009\DG Project Programme\Project Programme.xlsm", True, False)
    
    xlWB.ActiveSheet.Range("A14").CopyFromRecordset rst
    
    Set xlApp = Nothing

    rst.Close
    
    Set rst = Nothing
    
    'Closes Database'
    Application.Quit

End Sub

 

[coxyg]

Norie,

Thanks for your help.

Unfortunately I am getting the following error:

Run-time error '3061'. Too few parameters. Expected 1.

When I debug the highlighted line is:
Set rst = CurrentDb.OpenRecordset("Project Programme")

Any help would be much appreciated.
Regards
Coxyg

 

[Norie]

Does the query 'Project Programme' take any parameters?

 

[coxyg]

Here's my query

 

[coxyg]

Try again

 

[Norie]

As far as I can see the query takes a parameter from the form 'Main' for the criteria of field 'Project Number'.

When you tried the code I posted was that form open?

 

[coxyg]

Correct. But I need the form open for my combo box selection and to press the button. (Sorry my access skills are quite poor)

 

[Norie]

If the form was open then the code I posted should work, if the form is closed both the code you originally posted and the code I posted would cause the parameter error.