VBA to open workbook to specific tab

Hi Experts,

Hoping that you can help with two issues that I am having with the vba below.   First issues is that I can't get the Application.AskToUpdateLinks to work,  I've place this on different lines and the alert still comes up.   The second is that once the file is open I would like it to open and display the Store Summary tab.  Currently it is opening to the last saved tab.

Sub ActualOpen()


    Dim wb As Workbook
    Dim fso As FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim newestFile As File
    Dim ws As Worksheet
    
    Set fso = New FileSystemObject
    
    '--------------------------------------------------------------------------------
    'For test purposes, I am using the "My Documents" folder... this needs to change
    ' To use whatever folder you need
    '--------------------------------------------------------------------------------
    
    Set myFolder = fso.GetFolder("\\SSFilePrint\GROUPSHARE\Store Planning\LSP Shared\International\Finance\VSBA & ROW")
    '\\SSFilePrint\GROUPSHARE\Store Planning\Projects\International\Store Schedules\JDE Store report
    

    
    Application.ScreenUpdating = False
    Application.AskToUpdateLinks = False
    Application.Calculation = xlCalculationManual
    
    For Each myFile In myFolder.Files
        Select Case UCase(fso.GetExtensionName(myFile.Path))
            Case "XLS", "XLSM", "XLSB", "XLSX":
        
                If newestFile Is Nothing Then
                    Set newestFile = myFile
                ElseIf myFile.DateLastModified > newestFile.DateLastModified Then
                    Set newestFile = myFile
                End If
        End Select
    Next
        
                
        If Not newestFile Is Nothing Then
            
            Application.Workbooks.Open newestFile.Path
            Set wb = Application.Workbooks(newestFile.Name)
            Set ws = wb.Sheets("Store Summary ")
        End If


End Sub

Open in new window

jmac001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David L. HansenProgrammer AnalystCommented:
Handling the Open event (see the dropdown boxes at the top of the VBA environment) you can just do this...

Private Sub Workbook_Open() 
     Worksheets("theWorkSheetName").Activate
End Sub

Open in new window

0
Haris DulicCommented:
Hi,

i made certain changes to the last part of your code:

        If Not newestFile Is Nothing Then
            
            Application.Workbooks.Open newestFile.Path , false 'added param FALSE to disable link updates
            Set wb = Application.Workbooks(newestFile.Name)
            Set ws = wb.Sheets("Store Summary")
			ws.activate ' you should activates selected sheet
        End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmac001Author Commented:
Hi samo4fun,

That worked.  Any thoughts on the  Application.AskToUpdateLinks = False placement?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Haris DulicCommented:
I changed the following line to disable the links update :

Application.Workbooks.Open newestFile.Path , FALSE  ---added param FALSE to disable link updates
0
Ejgil HedegaardCommented:
It does not matter where Application.AskToUpdateLinks = False is placed in the code.
It is not workbook specific, but change the Excel setting for opening all workbooks.

You probably have links to not available files, and that is another issue.
Excel ask if you want to Continue (= open the file without updating the links) or Edit the links.

Specify not to update links in the Open statement ignores all links, and that is probably not what you want.

To automatically update links, the solution Application.AskToUpdateLinks = False, with a "normal" Open statement, is the correct one to use, but you have to edit the links to work.
0
jmac001Author Commented:
Thanks samo4fun and Ejgil.  samo4fun I awarded you the majority of the points as both solutions you gave me did work. But I realized after trying the solution for the alerts that I still had issues which is why I awarded Ejgil 100 points for their explanation on why I would still be see the popup windows.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.