GetOpenFileName method question

[camandab]

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.

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

 

[camandab]

Anyone have any thoughts on this?

 

[xenou]

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:

[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]