none
DoCmd.TransferSpreadsheet acLink - Makes the Navigation Pane appear RRS feed

  • Question

  • Hi

    I have a line of vba code. It makes the Navigation Pane appear...

    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "TEMPLEVPORTALUNDERLAG", strFolder3 & "NYTTLEVPORTALUNDERLAG" & ".xlsx", True

    How can I stop it?


    Best // Peter Forss Stockholm GMT +1.00

    Sunday, January 14, 2018 3:47 PM

Answers

  • Turn off screen updating, do the link, then hide the nav pane and turn screen updating back on.  For example, if you have a function like this to hide the nav pane:

    Sub HideNavPane()
    
        DoCmd.SelectObject acTable, , True
        RunCommand acCmdWindowHide
    
    End Sub

    Then you can perform your link under control of a function like this:

    Private Sub LinkSpreadsheet()
    
        On Error GoTo Err_Handler
    
        Application.Echo False  ' turn off screen updating
    
        DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "TEMPLEVPORTALUNDERLAG", strFolder3 & "NYTTLEVPORTALUNDERLAG" & ".xlsx", True
    
    Exit_Point:
        HideNavPane
        Application.Echo True  ' turn on screen updating
        Exit Sub
    
    Err_Handler:
        Application.Echo True
        MsgBox Err.description, vbExclamation, "Error " & Err.Number
        Resume Exit_Point
        
    End Sub

    If no error is raised, the navigation pane never appears.  If an error is raised, I find that there's a very brief flash of the nav pane after the user acknowledges the error message, but it's not too bad.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    • Marked as answer by ForssPeterNova Sunday, January 14, 2018 5:55 PM
    • Edited by Dirk Goldgar Sunday, January 14, 2018 6:01 PM typo
    Sunday, January 14, 2018 4:53 PM

All replies

  • Turn off screen updating, do the link, then hide the nav pane and turn screen updating back on.  For example, if you have a function like this to hide the nav pane:

    Sub HideNavPane()
    
        DoCmd.SelectObject acTable, , True
        RunCommand acCmdWindowHide
    
    End Sub

    Then you can perform your link under control of a function like this:

    Private Sub LinkSpreadsheet()
    
        On Error GoTo Err_Handler
    
        Application.Echo False  ' turn off screen updating
    
        DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "TEMPLEVPORTALUNDERLAG", strFolder3 & "NYTTLEVPORTALUNDERLAG" & ".xlsx", True
    
    Exit_Point:
        HideNavPane
        Application.Echo True  ' turn on screen updating
        Exit Sub
    
    Err_Handler:
        Application.Echo True
        MsgBox Err.description, vbExclamation, "Error " & Err.Number
        Resume Exit_Point
        
    End Sub

    If no error is raised, the navigation pane never appears.  If an error is raised, I find that there's a very brief flash of the nav pane after the user acknowledges the error message, but it's not too bad.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    • Marked as answer by ForssPeterNova Sunday, January 14, 2018 5:55 PM
    • Edited by Dirk Goldgar Sunday, January 14, 2018 6:01 PM typo
    Sunday, January 14, 2018 4:53 PM
  • Thanks Dirk

    Much appreciated!


    Best // Peter Forss Stockholm GMT +1.00

    Sunday, January 14, 2018 6:08 PM