Access -> Excel Template with macros
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("D
xltPath = GetProjectDirectory() & "MEL\DMELHeader.xltx"
Set xlObj = CreateObject("Excel.Applic
xlObj.Workbooks.Add (xltPath)
With xlObj
.Range("E3") = DLookup("[ProjectName]", "Main-ProjectInformation")
.Range("E4") = DLookup("[ProjectLocation]
.Range("A6").CopyFromRecor
End With
CheckForFile st
xlObj.ActiveWorkbook.SaveA
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.
Dim xlObj As Object, xltPath As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("D
xltPath = GetProjectDirectory() & "MEL\DMELHeader.xltx"
Set xlObj = CreateObject("Excel.Applic
xlObj.Workbooks.Add (xltPath)
With xlObj
.Range("E3") = DLookup("[ProjectName]", "Main-ProjectInformation")
.Range("E4") = DLookup("[ProjectLocation]
.Range("A6").CopyFromRecor
End With
CheckForFile st
xlObj.ActiveWorkbook.SaveA
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.
ASKER
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.
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.
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:\mytempl
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:\mytempl
or perform the saveas at the initial point of opening, so you have your new workbook then, then do the ops you want.
ASKER
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?
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?
ASKER
In order for macros to go along with the template file, do I need to save as a macro enabled workbook?
yes, I take it then you are using office 2007 or 2010
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.
Further, when you save your resulting file, you need to save it as either *.xlsm, or the older *.xls format.
looks like macro enabled templates are suffixed with xltm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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