Macro to Open Workbook then use supplied name elsewhere in code

[rickincanada]

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.

 

[Datsmart]

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]

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]

You can use an object variable:

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]

This will open your Source workbook, copy the range, then close it:

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]

Thank you gentlemen so much! Datsmart - I appreciate your forward thinking around the closing of the OldWorkbook

shg - no Mac here!

Thanks again!