SteveL13
asked on
How close an open Excel file with code
An expert helped me figure out how to run a routine yesterday via the following code. But the Excel file opens and displays. But I want to close the Excel file right after it opens programmatically.
What do I have to add to the code to do this?
--Steve
What do I have to add to the code to do this?
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
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
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 = Environ("userprofile") & "\Desktop\IHMG Notes.xlsx"
wkb.SaveAs FileName:=strXLFile
appExcel.Visible = True
ErrorHandlerExit:
Set appExcel = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in FieldsToColumns procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
--Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER