Had a little error in my MS Access VBA when trying to use the Select method of an Excel Range. It turns out that you have to select the WorkSheet before the Range, otherwise you might get Error 1004

Sub RangeError()

    Dim xls As Excel.Application
    Dim xlsBook As Excel.Workbook
    Dim xlsSheet As Excel.Worksheet
    Dim xlsSheet2 As Excel.Worksheet

    Set xls = New Excel.Application
    Set xlsBook = xls.Workbooks.Open(PathToExcelFile)
    Set xlsSheet = xlsBook.Sheets("Sheet1")
    Set xlsSheet2 = xlsBook.Sheets("Sheet2")

    xlsSheet2.Select  'THIS LINE IS REQUIRED
    xlsSheet2.Range("C3").Select   'THIS IS THE PROBLEM LINE

End Sub

I wrote some code to generate Excel Workbooks and Worksheets. All worked well on my Windows 10 development machine, but a when I ran it on the Server, running Windows 2019 Server, it hung on the Workbook.Close command, until I added the SaveChanges property like shown below.

This hung at the Close line.

Sub TestExcel()

    Dim obj As Object
    Dim wkb As Object

    Set obj = CreateObject("Excel.Application")
    Set wkb = obj.Workbooks.Open("C:\Users\[username]\Desktop\Excel\test.xlsx")

    ' Change True to False if you do not want to save
    wkb.Worksheets("Sheet1").Range("A1") = "Hello!"

    wkb.Close
    Set wkb = Nothing
    Set obj = Nothing

End Sub

This worked fine!

Sub TestExcel()

    Dim obj As Object
    Dim wkb As Object

    Set obj = CreateObject("Excel.Application")
    Set wkb = obj.Workbooks.Open("C:\Users\[username]\Desktop\Excel\test.xlsx")

    ' Change True to False if you do not want to save
    wkb.Worksheets("Sheet1").Range("A1") = "Hello!"

    DoEvents
    wkb.Close SaveChanges:=True
    Set wkb = Nothing
    Set obj = Nothing

End Sub

I retested with the SaveChanges option on Windows 10 and it worked fine. I added the DoEvents too, just to let the code catchup. But it was the SaveChanges that made the difference!

Happy Coding, Coder!

I recently had the need to create workbooks and worksheets from scratch so this is what I did. No error trapping was added in order to keep the example simple!

Public Sub CreateExcelWorkBookAndSheetsTest()

    Dim xlsNew As Excel.Application
    Dim wkbNew As Excel.Workbook
    Dim wksNew As Excel.Worksheet
    Dim strNewWorkBook As String
    Dim strWorkBookName As String
    
    ' Create the New Workbook Name
    strWorkBookName = "TEST EXCEL FROM MSACCESS VBA"
    strNewWorkBook = CurrentProject.Path & "\" & strWorkBookName & ".xlsx"
        
    If Dir(strNewWorkBook) <> "" Then
        Kill strNewWorkBook
    End If
    
    ' Create a New Excel File and add the WorkSheet to it. Then add the WorkSheet.
    Set xlsNew = New Excel.Application
    
    ' New Workbook
    Set wkbNew = xlsNew.Workbooks.Add
    
    ' Create Workbook and name the first Worksheet Index. By default it will be Sheet1
    ' Reference Worksheets(1) because this is the first worksheet that is created with the initial workbook!
    Set wksNew = wkbNew.Worksheets(1)
    wksNew.Name = "Index"
    ' Use SaveAs only once at the beginning!
    wkbNew.SaveAs strNewWorkBook, xlOpenXMLWorkbook
    
    ' Add another Worksheet and name it Main Data 1. Create a new reference to the worksheet using Count!
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    ' Add the worksheet to the workbook "After" the wksNew reference that we created above.
    wkbNew.Sheets.Add After:=wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Main Data 1"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save
    
    ' Add another Worksheet and name it Main Data 2. Same comments as above.
    ' You can use After:= to reference the [After] parameter or you can just used the parameter like below.
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add , wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Main Data 2"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save
    
    ' Add a Final Worksheet call Total
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add , wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Total"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save

    ' Now you can create another worksheet and put it before or after another one!
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add Before:=wksNew
    ' We use the -1 in this case as we want to make sure we Name the sheet before the reference "Before" using wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count - 1).Name = "Final Test"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save

    ' Now we just add 5 new sheets! These are added starting at the beginning of the last Sheet (workbook) we added, Final Test
    wkbNew.Sheets.Add Count:=5

    Set xlsNew = Nothing

    Application.FollowHyperlink strNewWorkBook
End Sub