[SOLVED] Run time error 462 The remote server machine does not exist - runs ok first time, errors on second code run

Run time error 462 The remote server machine does not exist - runs ok first time, errors on second code run - MS Project and Excel

Am running some VBA from within MS Project, exporting an Excel sheet based on a saved map and then opening it to tidy it up and format. Works fine on the first pass, second try and I get error 462 when it gets to 'With XLWorksheet > Range ("C:C")....'.

The error was previously 1004, but I added a routine to terminate all Excel processes at the end.

If I stop the code when it errors and manually close the workbook and then Excel, it will run again without error.

Reading some previous posts am thinking I have made some unqualified references which have created implicit references that cannot be killed or don't exist but am a complete novice so am struggling to see if that's the case.

Am using the desktop to store the file just because I am not confident to use in Sharepoint yet.

Code below

Dim XLApp As Excel.Application
Dim XLwbook As Excel.Workbook
Dim XLWorksheet As Excel.Worksheet
Dim strInputFileName As String

    strInputFileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Costs Export " & Format(Date, "ddmmyy") & "_" & Format(Time, "hhnnss") & ".xlsx"
    FileSaveAs Name:="" & strInputFileName & "", FormatID:="MSProject.ACE", Map:="Costs by date"

    Set XLApp = CreateObject("Excel.application")
    Set XLwbook = XLApp.Workbooks.Open(strInputFileName)
    Set XLWorksheet = XLwbook.Worksheets(1)
    
    XLApp.Visible = True

    With XLWorksheet
        'Convert and format export data
        Range("C:C").TextToColumns **'< ERRORS HERE ON SECOND RUN**
        Range("D:D").TextToColumns
        Range("E:E").TextToColumns
        Range("F:F").TextToColumns
        Range("G:G").TextToColumns
        Range("C:C").NumberFormat = "dd/mm/yy"
        'Sort
        Range("A1", Range("G1").End(xlDown)).Sort key1:=Range("C1"), order1:=xlAscending, Header:=xlYes
        
        'Add Dev and Tool columns
        Range("D1").EntireColumn.Insert
        Range("D:D").NumberFormat = "#,###"
        Range("D1").Value = "Dev Cost"
        
        'Extract budget or actual figures
        Range("D2", Range("D2").End(xlDown)).Formula = "=IF(H2=999, 0,IF(H2>0, H2, F2))"

        'Insert row and add totals
        Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete
        Range("A1").EntireRow.Insert
        Range("D1").Value = WorksheetFunction.Sum(.Range("D:D"))

        Columns("A:I").AutoFit
    End With

    XLwbook.Close (True)
    Set XLWorksheet = Nothing
    Set XLwbook = Nothing
    
    XLApp.Quit
    Set XLApp = Nothing

'Kill all Excel processes
FindAndTerminate

End Sub

Solution

Put "." at the beginning part of all the lines inside of With. Like below. :)

 With XLWorksheet
    'Convert and format export data
    .Range("C:C").TextToColumns **'< ERRORS HERE ON SECOND RUN**
    .Range("D:D").TextToColumns
    .Range("E:E").TextToColumns
    ....