Solved

Export to an Excel template file

Posted on 2014-09-29
2
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

735 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