Solved

Export to an Excel template file

Posted on 2014-09-29
2
278 Views
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
   appExcel.Quit
   
ErrorHandlerExit:
   Set appExcel = Nothing
   Exit Sub

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

End Sub

Open in new window

0
Comment
Question by:SteveL13
2 Comments
 
LVL 12

Expert Comment

by:danishani
ID: 40351066
Change the extension to .xltx

Is that what you are after?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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")
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

15 Experts available now in Live!

Get 1:1 Help Now