none
MS Access VBA - Copy Excel Template to another folder location - Rename worksheets as desired RRS feed

  • Question

  • Hi community,

    Background:

    1. There is an Excel template file;

    2. Lots of Excel reports need to use the Excel template file as format;

    3. Multiple types of Excel reports. Each type of report is an Excel file. In each Excel file, there are multiple worksheets for different regions of that report. Let's say, Excel file (Personal Computer) has 4 regions of sales: North America, Latin America, Europe and Asia Pacific; Another Excel file (Tablet) also has 4 regions of sales: North America, Latin America, Europe and Asia Pacific; etc...

    Desired result:

    I need to write Access VBA code to copy an Excel template file (pre-defined format). Then paste the template file in another location. Then insert reports in the correct report type (such as Personal Computer and Tablet) and in the correct region (North America, Latin America, Europe, and Asia Pacific)?

    We will see 2 excel files here: 1. Personal Computer; and 2. Tablet;

    Under each Excel file there are: 1. Personal Computer (North America, Latin America, Europe and Asia Pacific) and 2. Tablet (North America, Latin America, Europe, and Asia Pacific);

    Basic logic:

    1. Create the Excel template file (all reports use the same Excel template);

    2. Write VBA code in Access to copy the Excel template and assign correct file names and worksheet names;

    3. Insert records in the correct file and worksheet;

    Please help, thanks.




    • Edited by MelZZ Monday, September 14, 2015 5:00 AM more details;
    Monday, September 14, 2015 4:53 AM

Answers

  • Cause this is how Excel templates work? You tell Excel to use a template to create a new file. E.g.

    Dim ea As Object 'Excel.Application
    Dim ew As Object 'Excel.Workbook
    Dim fd As DAO.Field
    Dim rs As DAO.Recordset
      
    Dim Count As Long
    
    Set rs = CurrentDbC.OpenRecordset("SELECT * FROM WhatEver;", dbOpenSnapshot) 
    Set ea = CreateObject("Excel.Application")
    Set ew = ea.Workbooks.Add(ATemplateFileName) 
    ea.DisplayAlerts = False  
    Count = 0
    For Each fd In rs.Fields
      ew.Sheets(1).Range("A1").Offset(0, Count) = fd.Name
      Count = Count + 1
    Next fd
    
    ew.Sheets(1).Range("A2").CopyFromRecordset rs     
    Set ew = Nothing    
    ea.DisplayAlerts = True
    ea.Visible = True
    Set ea = Nothing

    It does not work.

    Run-time error '424';

    Object required

    Stefan's corrected code:

    Function TestExcel()
        
        Dim objApp As Object        'Excel.Application
        Dim objWkBk As Object       'Excel.Workbook
        Dim fld1 As DAO.Field
        Dim rst1 As DAO.Recordset
        Dim dbs1 As Database
        Dim Count As Long
        Dim strTable As String      'The data source table
        Dim strTemplate As String   'The Excel template to use
        
        strTable = "Your Table Name"
        strTemplate = "Your Template Name"
        
        Set dbs1 = CurrentDb
        Set rst1 = dbs1.OpenRecordset("SELECT * FROM " & strTable & ";", dbOpenSnapshot)
        Set objApp = CreateObject("Excel.Application")
        Set objWkBk = objApp.Workbooks.Add(strTemplate)
        
        objApp.DisplayAlerts = False
        
        Count = 0
        For Each fld1 In rst1.Fields
          objWkBk.Sheets(1).Range("A1").Offset(0, Count) = fld1.Name
          Count = Count + 1
        Next fld1
        
        objWkBk.Sheets(1).Range("A2").CopyFromRecordset rst1
        
        objApp.DisplayAlerts = True
        objApp.Visible = True
        
        Set dbs1 = Nothing
        Set rst1 = Nothing
        Set objApp = Nothing
        Set objWkBk = Nothing
        
    End Function
    

    Monday, September 14, 2015 5:37 PM
  • No, it was not.

    CurrentDb is very cost intensive call. CurrentDbC is the common proxy solution. Place it in a standard module:

    Option Compare Database
    Option Explicit
    
    Private m_CurrentDb As DAO.Database
    
    Public Property Get CurrentDbC() As DAO.Database
    
      If m_CurrentDb Is Nothing Then
        Set m_CurrentDb = CurrentDb
      End If
      
      Set CurrentDbC = m_CurrentDb
    
    End Property

    Tuesday, September 15, 2015 1:55 PM

All replies

  • When it is an Excel Template, then you don't need to copy it. This is done automatically by Excel.  But I guess it would be not bad, when you can give some more information about your process here..

    Monday, September 14, 2015 12:55 PM
  • When it is an Excel Template, then you don't need to copy it. This is done automatically by Excel.  But I guess it would be not bad, when you can give some more information about your process here..

    I don't understand your post. All the detailed information is provided in my question section. I need VBA code to do that.

    I have only common shared Excel template for, let's say 40 reports. I don't need to copy it? Then how come this one template appear in those 40 excel reports?

    Monday, September 14, 2015 1:20 PM
  • Cause this is how Excel templates work? You tell Excel to use a template to create a new file. E.g.

    Dim ea As Object 'Excel.Application
    Dim ew As Object 'Excel.Workbook
    Dim fd As DAO.Field
    Dim rs As DAO.Recordset
      
    Dim Count As Long
    
    Set rs = CurrentDbC.OpenRecordset("SELECT * FROM WhatEver;", dbOpenSnapshot) 
    Set ea = CreateObject("Excel.Application")
    Set ew = ea.Workbooks.Add(ATemplateFileName) 
    ea.DisplayAlerts = False  
    Count = 0
    For Each fd In rs.Fields
      ew.Sheets(1).Range("A1").Offset(0, Count) = fd.Name
      Count = Count + 1
    Next fd
    
    ew.Sheets(1).Range("A2").CopyFromRecordset rs     
    Set ew = Nothing    
    ea.DisplayAlerts = True
    ea.Visible = True
    Set ea = Nothing

    Monday, September 14, 2015 3:41 PM
  • Cause this is how Excel templates work? You tell Excel to use a template to create a new file. E.g.

    Dim ea As Object 'Excel.Application
    Dim ew As Object 'Excel.Workbook
    Dim fd As DAO.Field
    Dim rs As DAO.Recordset
      
    Dim Count As Long
    
    Set rs = CurrentDbC.OpenRecordset("SELECT * FROM WhatEver;", dbOpenSnapshot) 
    Set ea = CreateObject("Excel.Application")
    Set ew = ea.Workbooks.Add(ATemplateFileName) 
    ea.DisplayAlerts = False  
    Count = 0
    For Each fd In rs.Fields
      ew.Sheets(1).Range("A1").Offset(0, Count) = fd.Name
      Count = Count + 1
    Next fd
    
    ew.Sheets(1).Range("A2").CopyFromRecordset rs     
    Set ew = Nothing    
    ea.DisplayAlerts = True
    ea.Visible = True
    Set ea = Nothing

    It does not work.

    Run-time error '424';

    Object required

    Monday, September 14, 2015 4:38 PM
  • Cause this is how Excel templates work? You tell Excel to use a template to create a new file. E.g.

    Dim ea As Object 'Excel.Application
    Dim ew As Object 'Excel.Workbook
    Dim fd As DAO.Field
    Dim rs As DAO.Recordset
      
    Dim Count As Long
    
    Set rs = CurrentDbC.OpenRecordset("SELECT * FROM WhatEver;", dbOpenSnapshot) 
    Set ea = CreateObject("Excel.Application")
    Set ew = ea.Workbooks.Add(ATemplateFileName) 
    ea.DisplayAlerts = False  
    Count = 0
    For Each fd In rs.Fields
      ew.Sheets(1).Range("A1").Offset(0, Count) = fd.Name
      Count = Count + 1
    Next fd
    
    ew.Sheets(1).Range("A2").CopyFromRecordset rs     
    Set ew = Nothing    
    ea.DisplayAlerts = True
    ea.Visible = True
    Set ea = Nothing

    It does not work.

    Run-time error '424';

    Object required

    Stefan's corrected code:

    Function TestExcel()
        
        Dim objApp As Object        'Excel.Application
        Dim objWkBk As Object       'Excel.Workbook
        Dim fld1 As DAO.Field
        Dim rst1 As DAO.Recordset
        Dim dbs1 As Database
        Dim Count As Long
        Dim strTable As String      'The data source table
        Dim strTemplate As String   'The Excel template to use
        
        strTable = "Your Table Name"
        strTemplate = "Your Template Name"
        
        Set dbs1 = CurrentDb
        Set rst1 = dbs1.OpenRecordset("SELECT * FROM " & strTable & ";", dbOpenSnapshot)
        Set objApp = CreateObject("Excel.Application")
        Set objWkBk = objApp.Workbooks.Add(strTemplate)
        
        objApp.DisplayAlerts = False
        
        Count = 0
        For Each fld1 In rst1.Fields
          objWkBk.Sheets(1).Range("A1").Offset(0, Count) = fld1.Name
          Count = Count + 1
        Next fld1
        
        objWkBk.Sheets(1).Range("A2").CopyFromRecordset rst1
        
        objApp.DisplayAlerts = True
        objApp.Visible = True
        
        Set dbs1 = Nothing
        Set rst1 = Nothing
        Set objApp = Nothing
        Set objWkBk = Nothing
        
    End Function
    

    Monday, September 14, 2015 5:37 PM
  • Hi Running,

    Your post is working. 

    Do you know if there is a way in Access to reference/call an Excel file as a template. Using the Excel file as the only template and copy the Access table data into multiple templates?


    • Edited by MelZZ Tuesday, September 15, 2015 2:19 PM Running
    Tuesday, September 15, 2015 3:45 AM
  • Hi community,

    Here I have another question regarding to Access VBA to copy Excel files.

    Let's say, I have an Excel file C:\Users\ABC\Documents\Template.xlsx. The datasheet name in this Excel file is "TemplateReport".

    I have an Access report name list table: tblReportName.

    

    Another table in Access is: tblReportTabName

    How can I write a VBA/query to export 4 reports referencing the structure in "C:\Users\ABC\Documents\Template.xlsx"? (Sheet = "TemplateReport")

    Reports should be copy and paste in the following addresses based on the template Excel file:

    "C:\Users\ABC\Documents\Report\Report_One.xlsx"
    "C:\Users\ABC\Documents\Report\Report_Two.xlsx"
    "C:\Users\ABC\Documents\Report\Report_Three.xlsx"
    "C:\Users\ABC\Documents\Report\Report_Four.xlsx"

    In addition:

    In Report_One.xlsx, there are 2 datasheets. The first datasheet is "AAA" and the second datasheet is "BBB".
    In Report_Two.xlsx, there are 1 datasheet. The first datasheet is "CCC".
    In Report_Three.xlsx, there are 2 datasheets. The first datasheet is "DDD" and the second datasheet is "EEE".
    In Report_Four.xlsx, there are 3 datasheets. The first datasheet is "FFF", second is "GGG", and the third is "HHH".

    Does anyone know how to do that? Please let me know. Thanks.

    Sincerely,

    M.

    Tuesday, September 15, 2015 4:18 AM
  • Just run the code again.
    Tuesday, September 15, 2015 7:36 AM
  • Just run the code again.

    No, it does not work.

    The code stopped in here: Set rs = CurrentDbC.OpenRecordset("SELECT * FROM tblNameXXX;", dbOpenSnapshot)

    Error code:

    Tuesday, September 15, 2015 1:29 PM
  • The code stopped in here: Set rs = CurrentDbC.OpenRecordset("SELECT * FROM tblNameXXX;", dbOpenSnapshot)

    Hi Mel,

    Probably there was a typo. You could try:

        Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblNameXXX;", dbOpenSnapshot)

    Imb.

    Tuesday, September 15, 2015 1:40 PM
  • No, it was not.

    CurrentDb is very cost intensive call. CurrentDbC is the common proxy solution. Place it in a standard module:

    Option Compare Database
    Option Explicit
    
    Private m_CurrentDb As DAO.Database
    
    Public Property Get CurrentDbC() As DAO.Database
    
      If m_CurrentDb Is Nothing Then
        Set m_CurrentDb = CurrentDb
      End If
      
      Set CurrentDbC = m_CurrentDb
    
    End Property

    Tuesday, September 15, 2015 1:55 PM