open an access report from excel? 1

[ck1999]

Is there a way to open an access report from excel using vba?

Chris

 

[lameid]

You'll have to use automation to open Access from within Excel but try...

sub OpenAccessReport (strDBpath as string, strReport as string)
Dim App As Application
Set App = CreateObject("Access.application")
App.Visible = True

App.OpenCurrentDatabase strDBpath, False
App.DoCmd.Openreport strFrmName, acNormal
Set App = Nothing
End Sub

 

[ck1999]

Thank you lameid. it worked i changed the dim statement to object and it worked great. Now I need to have the macro send text to a popup menu from the report.

When the report is opened it ask for a id. I want to know if i can send the id throught the macro. Sendkeys did not work because the macro pauses at the openreprt line and is waiting for the response from the popup menu before moving on. I hope I explained it ok.

Chris

 

[lameid]

So you have a paramter query or report in Access and you want to have it pop up in Excel instead?

Hopefully it is a criteria paramter...

You could remove it from the query, use an inputbox in Excel to get the value and change the line that opens the report to take criteria (the Where option in help for docmd.openreport). This variable is equivalent to the where part of an SQL statement without the where. Hopefully that's enough for you. From some of your posts I think it is. If you need specifics, please tell me the data type of the field in question and post the SQL from the query.

If it is a value on the report, try creating a form with a control on it and reference it on the report instead of the parameter. Then use automation to open the form and populate the value before opening the report.

Let me know if you need more help.

 

[ck1999]

it is similar to a parameter query however it is just asking for an id. It is not sorting or querying the data based off of it. I am sending one parameter in the where conidition to do a query and only show the report for the certain data. and this works fine. The popup box is for and id that goes into a textbox that is not related to any data in the table or on the form

Chris

 

[lameid]

So the problem is you don't know what is causing the prompt?

Does it come up when you run the query? If it does post the SQL and I'll show you what I mean.

If not, check all the contolls on your report and all the Sorting and Grouping on the report for the ID. I find that checking controls is sometimes easiest by deleting several controls until the problem goes away and then reopening the report without checking to inspect the control source.

 

[ck1999]

When I open the report from access I want the user to enter an ID number into the popup box (similar to a parameter query) to put onto the report. This does not query any data just fills in a text box.

Now I am trying to open the report from excel and want to send the ID number from a cell in excel to the report in access.

When I open the access report from excel using the above code the popup box opens. And if I typed in the info the report opens and everything is fine.

However, I do not want to make the user type in the ID but pull it from a cell in an excel spreadsheet.

Chris

 

[lameid]

GAK! "When I open the report from access I want the user to enter an ID number into the popup box (similar to a parameter query) to put onto the report."

SO WHAT CAUSES THE PROMT? A REPORT CONTROL SOURCE WHAT IS IT?

 

[ck1999]

the text box is unbound with the control source = ID Number

When the report opens a popup screen appears asking for the ID number

Chris

 

[lameid]

So have them enter it on a form instead, have automation code open the form, enter the value on the form and then run the report. All the Access objects and collections should be exposed by the application object... I.e. App.Forms; App.Reports etc.

 

[ck1999]

thanks for the help

chris