Export to an Excel template file

Posted on 2014-09-29
Medium Priority
Last Modified: 2014-09-30
I'm using the following code which was provided by an expert.  (thank you very much).  But now I'm wondering if it can be exported to an Excel template.

Public Sub FieldsToColumns()
'Created by Helen Feddema 30-Jul-2014
'Last modified by Helen Feddema 12-Aug-2014

On Error GoTo ErrorHandler

   Dim appExcel As New Excel.Application
   Dim wkb As Excel.Workbook
   Dim rst As DAO.Recordset
   Dim fld As DAO.Field
   Dim intFieldCount As Integer
   Dim intCount As Integer
   Dim strFieldName As String
   Dim varFieldValue As Variant
   Dim sht As Excel.Worksheet
   Dim intRow As Integer
   Dim strRange As String
   Dim rng As Excel.Range
   Dim strXLFile As String
   Dim strDesktopPath As String
   Set rst = CurrentDb.OpenRecordset("LOCALtblTEMPIHMGNotes")
   intFieldCount = rst.Fields.Count
   Debug.Print "No. of Fields: " & intFieldCount
   Set wkb = appExcel.Workbooks.Add
   Set sht = wkb.Sheets(1)
   intRow = 1
   strDesktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
   For intCount = 0 To intFieldCount - 1
      strFieldName = rst.Fields(intCount).Name
      Debug.Print "Field name: " & strFieldName
      strRange = "A" & CStr(intRow)
      Set rng = sht.Range(strRange)
      rng.Value = strFieldName
      varFieldValue = rst.Fields(intCount)
      Debug.Print "Field value: " & varFieldValue
      strRange = "B" & CStr(intRow)
      Set rng = sht.Range(strRange)
      rng.Value = varFieldValue
      intRow = intRow + 1
   Next intCount

   strXLFile = strDesktopPath & "\IHMG Notes - " & Forms!frmIHMGnotes.Text328 & ".xlsx"
   wkb.SaveAs FileName:=strXLFile
   appExcel.Visible = False
   Set appExcel = Nothing
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in FieldsToColumns procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Question by:SteveL13
LVL 12

Expert Comment

ID: 40351066
Change the extension to .xltx

Is that what you are after?
LVL 86

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40351819
Do you mean export to a NEW workbook created from an Excel template? If so, change this line:

Set wkb = appExcel.Workbooks.Add("Path to your template")

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

607 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