SteveL13
asked on
Export to an Excel template file
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is that what you are after?