Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Access Report to Excel Template

This is a discussion on Access Report to Excel Template within the Microsoft Access forums, part of the Question Forums category; I want to take a report from Access and push it to an Excel template. The Excel template has logos ...

  1. #1
    New Member
    Join Date
    Nov 2005
    Posts
    17

    Default Access Report to Excel Template

    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.

  2. #2
    Board Regular Tardis's Avatar
    Join Date
    Dec 2011
    Location
    Campbell River, BC Canada
    Posts
    103

    Default Re: Access Report to Excel Template

    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
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Using Excel 2010 at work Mac Excel 2007 at home
    - If Plan A doesn't work out, "Keep Cool"! There are 25 other letters in the alphabet.

  3. #3
    New Member
    Join Date
    Nov 2005
    Posts
    17

    Default Re: Access Report to Excel Template

    Quote Originally Posted by Tardis View Post
    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.

  4. #4
    Board Regular Tardis's Avatar
    Join Date
    Dec 2011
    Location
    Campbell River, BC Canada
    Posts
    103

    Default Re: Access Report to Excel Template

    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
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Using Excel 2010 at work Mac Excel 2007 at home
    - If Plan A doesn't work out, "Keep Cool"! There are 25 other letters in the alphabet.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,644

    Default Re: Access Report to Excel Template

    Would exporting the raw data to Excel into a specific file/template to a specific range in a specific worksheet work for you?
    If posting code please use code tags.

  6. #6
    New Member
    Join Date
    Nov 2005
    Posts
    17

    Default Re: Access Report to Excel Template

    Quote Originally Posted by Norie View Post
    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.

  7. #7
    New Member
    Join Date
    Nov 2005
    Posts
    17

    Default Re: Access Report to Excel Template

    Quote Originally Posted by Tardis View Post
    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.

  8. #8
    Board Regular
    Join Date
    Mar 2011
    Location
    The Hague, Netherlands
    Posts
    409

    Default Re: Access Report to Excel Template

    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 by Kreszch68; Feb 27th, 2012 at 05:53 AM.
    Problems are solutions in progress

  9. #9
    New Member
    Join Date
    Nov 2005
    Posts
    17

    Default Re: Access Report to Excel Template

    Quote Originally Posted by Kreszch68 View Post
    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.

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,644

    Default Re: Access Report to Excel Template

    The macros pop-up from where? Access or Excel?
    If posting code please use code tags.

Page 1 of 4 123 ... LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com