CODEWITHSUNDEEP
excelvba
frank_quint
frank_quint

Reputation: 3

How to Activate a Workbook using a variable workbook name?

I'm new to VBA and need some help with my code. I am using Excel and Application.GetOpenFilename to prompt users to select a file to open. As I copy data from one file to another, I am trying to Activate the workbook what was opened by the GetOpenFilename application.

I am running VBA code from my Primary Workbook and here's what my code looks like.

Open import file

strGetFilename = Application.GetOpenFilename(, , "Open Import Workbook")
Workbooks.Open strGetFilename

For this example, let's assume that the file selected to open is Workbook2, I then select data from a table in Workbook2 and copy it.

ActiveSheet.ListObjects("table1").Range.Select
   Selection.Copy

I now paste the data into my primary Workbook

Windows("PrimaryWorkbook.xlsm").Activate
Sheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
              :=False, Transpose:=False

Here is where I am getting tripped up. I want to go back to Workbook2 in order to copy other tables/sheets or to be able to perform other data edits and I don't want the file name to be hardcoded.

Any suggestions?

Upvotes: 0

Views: 3029

Answers (2)

Hunter
Hunter

Reputation: 13

You need to use 'Set'

Set fileVariable = Workbooks.Open(path & "\" & fileName)

You can then refer to the variable with fileVariable.Range("A1").select.

It is generally better to avoid selecting and activating. You can directly code

.range("A1").value2 = .range("B1").value2

to copy and paste.

Upvotes: 1

SJR
SJR

Reputation: 23081

Assign it to a variable

Sub x()

Dim wb As Workbook, strGetFilename As String
strGetFilename = Application.GetOpenFilename(, , "Open Import Workbook")

Set wb = Workbooks.Open(strGetFilename)

MsgBox wb.Name

End Sub

And read this on how to avoid using Select/Activate.

You can then use 

wb.Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Upvotes: 0

Related Questions