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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi Mr Deeds,

You can create a link between Access and Excel to get the data. When I've set this up in the past, I've setup a Query in Access that gets the data I want and then link to it in Excel.

If that "raw" link of data from the Access Query meets your needs, you should be good to go. However, if you need to manipulate the data or format it more, you can do that in Excel (either manually or with VBA).

Hope this helps.

Cheers,
Byron
 
Upvote 0
Hi Mr Deeds,

You can create a link between Access and Excel to get the data. When I've set this up in the past, I've setup a Query in Access that gets the data I want and then link to it in Excel.

If that "raw" link of data from the Access Query meets your needs, you should be good to go. However, if you need to manipulate the data or format it more, you can do that in Excel (either manually or with VBA).

Hope this helps.

Cheers,
Byron
Thanks...however the query in access has some combo box criteria...the query then does not show up as linkable in Excel. All works great in Access I can preview report, publish to PDF, export to Excel...the trouble comes in when Exporting to Excel...first off it creates a new file each time, secondly all the formatting is gone..just raw data. So, my only solution was to create some kind of Excel template with our logos, etc and just tell Access where I want the data i.e. put data in this template, in these cells, etc. Hope that helps...I am at a loss on how to get it to a set template each time we want to export to Excel.
 
Upvote 0
Thanks for the additional info.

It sounds like you are either using a Access Form Tool, or something else with Combo Boxes to setup the query for your nicely formated "Access Report".

Assuming that there is a good reason that the Access Report can't be reformated with the logos and other information that you need, you need to get the data from this report into Excel and into a format/template that meets your companies needs.

When I've been faced with unformated data that I need to format and fit into a template, I have usually relied on writing some Excel VBA code to do this.

Typically, my strategy has been:

1) Start the VBA macro in Excel and clean up the template to get rid of any previous data and set it back to "empty" with all the core logos, etc...

2) Open the the source Excel file with the raw data. This can be hard coded into the VBA or you can use the Application.Dialogs(xlDialogOpen).Show function to get the basic Microsoft Open window to allow the user to select the file and open it.

3) Have Excel VBA loop to go through the opened raw data file and copy and paste it into the template in whatever way is needed.

4) Close the source file, and the template with the updated data with is good to go.

Would this work for you? I guess it is not clear to me if you need to "push" the data into the template, or "pull" the data from Access into Excel.

What I'm describing is a "pull". If you need to "push", you could write the code in Access that "pushes" (saves) the raw data to the the Excel File then opens the Excel template and triggers the VBA macro that I described above to "pull" the data in and save it and then close Excel. Then the updated Excel Report is ready to go.

Hope this makes sense and helps.

Cheers,
Byron
 
Upvote 0
Would exporting the raw data to Excel into a specific file/template to a specific range in a specific worksheet work for you?
 
Upvote 0
Would exporting the raw data to Excel into a specific file/template to a specific range in a specific worksheet work for you?
Yes...that would work...just not sure on how to export from Access to a specific Excel template each time...the user would then save as the template to their own Excel file. Then the template would be clear ready to be filled again. Thanks.
 
Upvote 0
Thanks for the additional info.

It sounds like you are either using a Access Form Tool, or something else with Combo Boxes to setup the query for your nicely formated "Access Report".

Assuming that there is a good reason that the Access Report can't be reformated with the logos and other information that you need, you need to get the data from this report into Excel and into a format/template that meets your companies needs.

When I've been faced with unformated data that I need to format and fit into a template, I have usually relied on writing some Excel VBA code to do this.

Typically, my strategy has been:

1) Start the VBA macro in Excel and clean up the template to get rid of any previous data and set it back to "empty" with all the core logos, etc...

2) Open the the source Excel file with the raw data. This can be hard coded into the VBA or you can use the Application.Dialogs(xlDialogOpen).Show function to get the basic Microsoft Open window to allow the user to select the file and open it.

3) Have Excel VBA loop to go through the opened raw data file and copy and paste it into the template in whatever way is needed.

4) Close the source file, and the template with the updated data with is good to go.

Would this work for you? I guess it is not clear to me if you need to "push" the data into the template, or "pull" the data from Access into Excel.

What I'm describing is a "pull". If you need to "push", you could write the code in Access that "pushes" (saves) the raw data to the the Excel File then opens the Excel template and triggers the VBA macro that I described above to "pull" the data in and save it and then close Excel. Then the updated Excel Report is ready to go.

Hope this makes sense and helps.

Cheers,
Byron
Thanks...that would work too. Just seems like a lot of steps...would think there has to be a way to just export to a specific template each time. Perfect world: User views report in Access, presses "export" button, data is exported and fills in Excel template, Excel opens to the filled in template, user prompted immediately with "save as" to save as they wish, then template is back sitting empty ready to be filled in again. Thanks for the solutions...I will kick ideas around....I appreciate it.
 
Upvote 0
Here is some code you can use.
Replace the template name and query name in the code and set the starting range (now A1).
This will create a new workbook based on your template (which actually doesn't need to be a real template, you can use any excel file) and then prompts the user to save the file after pasting the data in the disered range.

Code:
Public Sub CreateExcelInfo()
'Set reference to Microsoft Excel Object library
'Set reference to Microsoft ActiveX DataObject 2.x
Const sFileNameTemplate As String = "PathAndNameOfYourTemplateXLSX"
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim vData As Variant
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from qTheQuery" 'This has to be the name of the query your report is using to display data
With objRs
    .Open sSQL, objConn, adOpenStatic, adLockReadOnly
    vData = .GetRows()
    .Close
End With
With oExcel
    .Visible = True
               'Create new workbook from the template file
                Set WB = .Workbooks.Add(sFileNameTemplate)
                With WB
                     Set WS = WB.Worksheets("Sheet1") 'Replace with the name of actual sheet
                     With WS
                              Set rng = .Range("A1") 'Starting point of the data range
                              rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData)
                     End With
 
                End With
 
    .Quit
End With
 
'clean up
Set oExcel = Nothing
Set objRs = Nothing
Set objConn = Nothing
Set vData = Nothing
End Sub

You can create a button on the report to trigger this code.
 
Last edited:
Upvote 0
Here is some code you can use.
Replace the template name and query name in the code and set the starting range (now A1).
This will create a new workbook based on your template (which actually doesn't need to be a real template, you can use any excel file) and then prompts the user to save the file after pasting the data in the disered range.

Code:
Public Sub CreateExcelInfo()
'Set reference to Microsoft Excel Object library
'Set reference to Microsoft ActiveX DataObject 2.x
Const sFileNameTemplate As String = "PathAndNameOfYourTemplateXLSX"
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim vData As Variant
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from qTheQuery" 'This has to be the name of the query your report is using to display data
With objRs
    .Open sSQL, objConn, adOpenStatic, adLockReadOnly
    vData = .GetRows()
    .Close
End With
With oExcel
    .Visible = True
               'Create new workbook from the template file
                Set WB = .Workbooks.Add(sFileNameTemplate)
                With WB
                     Set WS = WB.Worksheets("Sheet1") 'Replace with the name of actual sheet
                     With WS
                              Set rng = .Range("A1") 'Starting point of the data range
                              rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData)
                     End With
 
                End With
 
    .Quit
End With
 
'clean up
Set oExcel = Nothing
Set objRs = Nothing
Set objConn = Nothing
Set vData = Nothing
End Sub

You can create a button on the report to trigger this code.
Thanks!...I put in the code...made the necessary changes and when I run it I get the Macros box pop up. Any ideas? Thanks again.
 
Upvote 0
The macros pop-up from where? Access or Excel?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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