VBA to open workbook to specific tab

Posted on 2014-10-23
Last Modified: 2014-10-24
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
        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

Question by:jmac001
LVL 15

Expert Comment

by:David L. Hansen
ID: 40400550
Handling the Open event (see the dropdown boxes at the top of the VBA environment) you can just do this...

Private Sub Workbook_Open() 
End Sub

Open in new window

LVL 15

Accepted Solution

Haris Djulic earned 400 total points
ID: 40400552

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


Author Comment

ID: 40400564
Hi samo4fun,

That worked.  Any thoughts on the  Application.AskToUpdateLinks = False placement?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 15

Expert Comment

by:Haris Djulic
ID: 40400573
I changed the following line to disable the links update :

Application.Workbooks.Open newestFile.Path , FALSE  ---added param FALSE to disable link updates
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 100 total points
ID: 40402960
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.

Author Closing Comment

ID: 40403018
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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question