Consulting

Results 1 to 5 of 5

Thread: MS Access 2016 VBA Error '462' a second time a code is executed

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location

    MS Access 2016 VBA Error '462' a second time a code is executed

    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

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    The only thing that I can see that may be a problem is that you set xl here

    Set xl = CreateObject("excel.application")

    but do not set xl = nothing when you end the sub.

    ps I never use on error resume next, I always a proper error trap, otherwise you can get in to problems without notification.

  3. #3
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    I really appreciate your reply.
    At the end:
    I Set xl = nothing

    When I run the code the second time VBA displays:
    Run Time error '462': the remote server machine does not exist or is unavailable.

    Can you tell me the meaning of "proper error trap"

    Thanks

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    A proper error trap identifies the error and then exits the Sub or module.
    So you would have after dimensioning the variables

    on error goto errortrap

    your code and then directly before the End Sub

    Exit Sub
    errortrap:
    Msg Box Err.Description


    See if that stops the code at some other point.

    I am not sure that the prior "With" and "Select" should be combined, but it is a long time since I worked remotely with Excel.

  5. #5
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    I apologize for the delay in replying.
    Your lines of code work fine.
    Thank you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •