Solved

Access -> Excel Template with macros

Posted on 2010-09-08
11
643 Views
Last Modified: 2013-11-27
I am using the following code to export from Access to Excel:

Dim xlObj As Object, xltPath As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("DMEL-Excel-Query")
xltPath = GetProjectDirectory() & "MEL\DMELHeader.xltx"
Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add (xltPath)
    With xlObj
        .Range("E3") = DLookup("[ProjectName]", "Main-ProjectInformation")
        .Range("E4") = DLookup("[ProjectLocation]", "Main-ProjectInformation")
        .Range("A6").CopyFromRecordset rs
    End With
    CheckForFile st
    xlObj.ActiveWorkbook.SaveAs st

xlObj.Quit
Set xlObj = Nothing

I am trying to create Excel Macros that do certain things, and I would like to create them in the template file so when I export to Excel I use the template file, copy in the data, and then have access to the macros.

Is this possible? If so do I need to save as something else? or do something different with my template file?

Any advice is greatly appreciated.
0
Comment
Question by:VTKegan
  • 7
  • 3
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33631503
You could try this

open up your excel template, click on tools/macro - > record a macro
do your stuff then stop the macro
now look at the generated code
apply to your excel automation code

simplest way I can think of
0
 
LVL 10

Author Comment

by:VTKegan
ID: 33631661
I already have the macros created.  

The problem is when I export my excel data using the template file and save, the macro does not go along with it.  If I try to use the buttons it just says that the macro cannot be found.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33632038
ok, I understand now. You have a template file with a macro and when you save it as an xls, you want the macro to be saved with it.
My bad as I thought you wanted to create additional vba code.

I would say you should open up your template file Workbooks.open("c:\mytemplate.xls") then use this obect. At the end, instead of performing a save, do a saveas

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33632043
or perform the saveas at the initial point of opening, so you have your new workbook then, then do the ops you want.
0
 
LVL 10

Author Comment

by:VTKegan
ID: 33632093
Excel template files are in the format .xltx

When you open xltx files, the default command isnt to actually open the template file, it opens a xlsx version with the template file in the background.

The point of the save command shouldn't matter.  What I save as could matter but whether I copy then save or save then copy shouldn't affect this.

Should I not use .xltx files and only use .xlsx files?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 10

Author Comment

by:VTKegan
ID: 33632104
In order for macros to go along with the template file, do I need to save as a macro enabled workbook?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33632211
yes, I take it then you are using office 2007 or 2010
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33632225
You need to save your template as either *.xltm or the older *.xlt format.

Further, when you save your resulting file, you need to save it as either *.xlsm, or the older *.xls format.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33632238
looks like macro enabled templates are suffixed with xltm
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33632305
I was having a play and looking at the save options. I opened up a template but for some reason it would not save as a excel macro enabled workbook

eg

    Set objExcel = CreateObject("Excel.Application")
    Set objWB = objExcel.Workbooks.Open("c:\temp\xtest.xltm")
   
    objWB.Worksheets(1).Cells(5, 4) = 22
   
    objExcel.Visible = True

    objWB.SaveAs "Filename:=C:\temp\xtest.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled


Is your original template in excel template format?

Be back in about 15mins, gotta pop out

by the way, value of xlOpenXMLWorkbookMacroEnabled is 52

0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
ID: 33632531
right got it, I was doing the template bit wrong. I forgot that when you use templates you have to add the workbook, not open it up, doh!


    Set objExcel = CreateObject("Excel.Application")
    Set objWB = objExcel.Workbooks.Add("c:\temp\xtest.xltm")
   
    objWB.Worksheets(1).Cells(5, 4) = 22
   
    objExcel.Visible = True

    objWB.SaveAs "c:\temp\xtest.xlsm", xlOpenXMLWorkbookMacroEnabled



I opened up a macro enabled template, changed a value, ensured its visible to the user (for my testing purposes) then saved it as a macro enabled workbook
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
data analyst 3 50
MS Access Tables Linking 6 42
Excel callender with date slider 5 27
NEED TO UPDATE DATA IN EXCEL 18 32
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now