I am trying to edit an Excel file from Access. If possible, I do not want Excel to be visible during the edit process. Below is the code I wrote but it does not Save the changes and I get the message "ZPP_BOM Extract.XLSX is locked for editing" when I try to look at the Excel file. I am not sure what I am doing wrong.
Sub ChangeExcel4()
Dim MySheetPath As String
Dim XlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
MySheetPath = "C:\Users\Downloads\BOM Extract.xlsx"
Set XlApp = CreateObject("Excel.Application")
Set XlBook = XlApp.Workbooks.Open(MySheetPath, , False)
Set XlSheet = XlBook.Sheets("Sheet1")
XlSheet.Activate
XlSheet.Range("A1") = "Test"
XlBook.Save
XlBook.Close
XlApp.Quit
Set XlApp = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub
Thanks.
Sandy