Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

Open Excel to specific Worksheet…

Function fncOpenSpecificWorksheet(strSheetName As String)
'http://www.access-diva.com/tips.html
'Open Excel to the specified Worksheet
    
        Dim xlApp As Object
        Dim xlWBk As Object
        Dim xlWSh As Object
    
        On Error GoTo Err_Handler
            
            Set xlApp = CreateObject("Excel.Application")
            Set xlWBk = xlApp.Workbooks.Open("C:\... ENTER YOUR PATH HERE")
            
            xlApp.Sheets(strSheetName).Select
            xlApp.Visible = True
            
            Set xlApp = Nothing
            Set xlWBk = Nothing
            Set xlWSh = Nothing
            
            'Only for use with Access 2010 and above will not work with lower versions
            'Brings Excel to the front
            AppActivate "Microsoft Excel", True
            
        Exit Function
Err_Handler:
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        Exit Function

End Function

To use, copy/paste into your modUtilities and then in the Event Procedure of your Command Button

Call fncOpenSpecificWorksheet("YOUR WORKSHEET NAME")

53 total views, 1 views today

Comments are closed.