GetOpenFileName method question

camandab

Board Regular
Joined
Feb 10, 2010
Messages
79
I'm trying to use the GetOpenFileName method to pick up a file without actually opening it. Then I will use data from this file to populate several other worksheets that are opened.

However, I need to be able to reference a specific sheet on the file I select with the GetOpenFileName method.

In other macros I've written, I've used the code below where getFile is a function that contains the Application.GetOpenFileName method.

Code:
Templatename = getFile("Select dashboard file")
Set Templatebook = Workbooks.Open(Templatename)
Set Templatesheet = Templatebook.Sheets("abc")
So essentially, I need to do the same thing but eliminate the middle step of opening the workbook but still be able to define the sheet in the 3rd line. Is this possible? Any assistance will be helpful. Thanks!

I'm using Excel 2002
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
Speaking generally, you'll need to open the file to get information from it - including sheet names as well as the data you intend to copy from it. I'm sure that that's not an absolute, but usually simplest and best is just open the file (it can be opened read-only, and then hidden, to keep it safe and out of the way). Here's a test case to demonstrate the above idea -- to open & hide the source file:
Code:
[COLOR="Navy"]Sub[/COLOR] TestOpenWorkbook()
[COLOR="Navy"]Dim[/COLOR] wbSource [COLOR="Navy"]As[/COLOR] Workbook

    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] WorkbookIsOpen("Book1.xls") [COLOR="Navy"]Then[/COLOR]
        [COLOR="SeaGreen"]'//open a workbook and hide it[/COLOR]
        [COLOR="SeaGreen"]'//warning: if the workbook is slow to open, the user may wonder what's happening ...[/COLOR]
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
        Application.ScreenUpdating = False
        [COLOR="Navy"]Set[/COLOR] wbSource = Workbooks.Open(Filename:="C:\myTemp\Book1.xls", ReadOnly:=True)
        Windows(wbSource.Name).Visible = False
        Application.ScreenUpdating = True
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

    [COLOR="SeaGreen"]'//since the above is encapsulated in an error handler, check that the operation succeeded[/COLOR]
    [COLOR="Navy"]If[/COLOR] WorkbookIsOpen("Book1.xls") [COLOR="Navy"]Then[/COLOR]
        MsgBox "Workbook is open."
    [COLOR="Navy"]Else[/COLOR]
        MsgBox "Excel could not find/open the workbook."
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'------------------------------------------------------------------------[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] WorkbookIsOpen(ByRef sWorkbookName [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    s = Workbooks(sWorkbookName).Name
    [COLOR="Navy"]If[/COLOR] Err [COLOR="Navy"]Then[/COLOR]
        WorkbookIsOpen = False
    [COLOR="Navy"]Else[/COLOR]
        WorkbookIsOpen = True
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,938
Messages
5,508,235
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top