Access Report to Excel Template

MrDeeds

New Member
Joined
Nov 30, 2005
Messages
17
I want to take a report from Access and push it to an Excel template. The Excel template has logos etc in rows 1-10. I want the Access report to begin data on Row 11. Then I want the Save as box to pop up to force user to save the file as an xls. So the template is always there to receive the Access data. Any ideas? Thanks.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
What 'macro box' are you referring to?

Is it a security warning? Does it have a message?
 
Upvote 0
Access...I put the code in VBA access and run it from there...macros box pops up...before anything even runs.
Ok...now I put code in Module and it seems to want to run...however, it fails at:
.Open sSQL, objConn, adOpenStatic, adLockReadOnly
Gives me error: "No value given for one or more required parameters"
 
Upvote 0
Does the query you're reffering to need any parameters before you can run it? Like controls on the form?
From where are you running the code now? With the form open where you enter the parameters for your report?
 
Upvote 0
Does the query you're reffering to need any parameters before you can run it? Like controls on the form?
From where are you running the code now? With the form open where you enter the parameters for your report?
The query does need parameters...but they are already chosen in Access. I can run the report in Access. The code is in Module1 in Access. I have a form that I enter 2 parameters....then display report (which is based on query that I can run also). So, not sure why it would be parameter issue they are already chosen on the Access form.
 
Upvote 0
Ok, how exactly did you change this line?

Code:
sSQL = "Select * from [B][COLOR=red]qTheQuery[/COLOR][/B]"

The red text should be the name of your query
 
Upvote 0
Ok, how exactly did you change this line?

Code:
sSQL = "Select * from [B][COLOR=red]qTheQuery[/COLOR][/B]"

The red text should be the name of your query
Query name is in place of red text..correct. Now, I tried to run using a simpler query with no parameters...I get a different error: Compile Error: User-defined type not defined." Stops at: "Dim oExcel As New Excel.Application" Thought I would see if it ran with a simple query.
 
Upvote 0
This is because you didn't set the references needed.
You need to set a reference to the following libraries:

In the VBA editor choose extra, references and then select

Microsoft ActiveX DataObjects 2.x
Microsoft Excel object xx library
 
Upvote 0
We are getting closer...now it runs my simpler query...opens the Excel template...but errors/stops with RunTimeError 13 - Type Mismatch
at:
rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData)
 
Upvote 0
What did you set as the range?
It should be a single cell in this case, like A1.
Code:
 Set rng = .Range("A1")

And did you change the name of the sheet to an existing sheet?


Code:
Set WS = WB.Worksheets("Sheet1")
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top