Hello.
The code below is working fine the first time I run it, but when I need to run it a second time an error occurred:
Run Time error '462': the remote server machine does not exist or is unavailable.
Therefore, I have to open Task Manager to end all Excel Applications Task's and delete the excel file exported via CurrentProjectPath
This is my code:
Private Sub cmbexpqry_01_Click()
Dim wb, ws, xl, ch, qry_01 As Object
Dim a, b, r As Range
Dim sExcelWB As String
Set xl = CreateObject("excel.application")
On Error Resume Next
Kill TrailingSlash(CurrentProject.Path) & Form_frm_creattbarstacked.cbxcreattbarstacked.Value & "qry_01.xlsx"
Err.Clear
On Error GoTo 0
sExcelWB = TrailingSlash(CurrentProject.Path) & "qry_01.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_01", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_01")
Set ch = ws.Shapes.AddChart(58).chart
ws.Columns.AutoFit
ws.Columns("B:C").HorizontalAlignment = xlCenter
With ws.Range("D2", Range("D2").End(xlDown)).Select
Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With
wb.Save
xl.Visible = True
xl.UserControl = True
Set ws = Nothing
Set wb = Nothing
End Sub
When the code is executed a second-time error occurs on this line:
With ws.Range("D2", Range("D2").End(xlDown)).Select
I really appreciate your help to tell me what I am doing wrong an how to fix this error.
Cheers