Macro to Open Workbook then use supplied name elsewhere in code

rickincanada

Board Regular
Joined
Aug 31, 2010
Messages
61
Hello,

I'm looking to create a macro that will prompt the user to select the workbook that their "settings data" is in and then proceed to copy and paste some of the data from that workbook to the new one.

So far I've got this in place:

Sub GetOptions()
Dim vFile As Variant
'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
End If
'Open selected file
Workbooks.Open vFile
End Sub

I'm challenged in that I'm not certain of the location of the file on their computer or if they've possibly renamed it since its creation. Therefore I'm hoping to prompt them to open the file and then once it's open use it's name through the rest of my code.

An example of this working might look like this:

NewWorkbook.xls is open and there is a button called "Get Settings", when the user clicks this button they are prompted for the location and name of "OldWorkbook.xls".

Once they've selected it then I'd like the following to take place:

Windows("OldWorkbook.xlsm").Activate
Sheets("Options").Select
Range("H3:H45").Select
Selection.Copy
Windows("NewWorkbook.xlsm").Activate
Sheets("Options").Select
Range("H3").Select
ActiveSheet.Paste

Thank you for taking a look and I greatly appreciate any help that can be provided.
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Will the WorkSheet you want to copy from and to always be "Options"?
Will the Range to copy always be H3:H45?
Will the Range to Paste to always be H3?
 

rickincanada

Board Regular
Joined
Aug 31, 2010
Messages
61
Yes, yes and yes :)

I will want to expand on that copy paste in the future, however once I've got the idea I'll be good.

Thanks so much!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,816
Office Version
  1. 2010
Platform
  1. Windows
You can use an object variable:
Code:
Sub GetOptions()
    Dim vFile       As Variant
    Dim wkbOld      As Workbook
 
    vFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", _
                                        ButtonText:="Open", _
                                        Title:="Select Excel File", _
                                        MultiSelect:=False)
    If VarType(vFile) = vbBoolean Then Exit Sub
 
    Set wkbOld = Workbooks.Open(vFile)
    wkbOld.Worksheets("Option").Range("H3:H45").Copy _
            Destination:=Workbooks("NewWorkbook.xlsm").Worksheets("Options").Range("H3")
End Sub

You are apparently using a Mac?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This will open your Source workbook, copy the range, then close it:
Code:
Sub GetOptions()
Dim vFile As Variant
Dim SourceWB As String, SourceWS As String
Dim TargetWB As String, TargetWS As String
Dim TargetPath As String
    'Assign Target variables
    TargetWB = ActiveWorkbook.Name
    TargetWS = ActiveSheet.Name
    TargetRng = "H3"

'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
'If Cancel then exit
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    End If
'Open selected file
Workbooks.Open vFile
    'Assign Source variables
    SourceWB = ActiveWorkbook.Name
    SourceWS = "Options"
    SourceRng = "H3:H45"
    'Copy Source data to Target
    With Workbooks(SourceWB) '.Sheets(SourceWS)
        Workbooks(SourceWB).Sheets(SourceWS).Range(SourceRng).Copy Destination:=Workbooks(TargetWB).Sheets(TargetWS).Range(TargetRng)
        .Close
    End With
End Sub
 

rickincanada

Board Regular
Joined
Aug 31, 2010
Messages
61
Thank you gentlemen so much! Datsmart - I appreciate your forward thinking around the closing of the OldWorkbook :)

shg - no Mac here!

Thanks again!
 

Forum statistics

Threads
1,144,568
Messages
5,725,038
Members
422,590
Latest member
Mikeyyy

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