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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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]
 

Watch MrExcel Video

Forum statistics

Threads
1,133,322
Messages
5,658,143
Members
418,429
Latest member
Regila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top