Activate workbook

fddekker

Board Regular
Joined
Jun 30, 2008
Messages
86
Office Version
  1. 365
Platform
  1. Windows
What is the correct syntax to activate an open workbook?

This is the scenario:

I am send a file
I obtain the file name with:

Dim vFileNames As Variant
vFileNames = Application.GetOpenFilename

While this file is open, I open another file (a static file that will always have the same name), copy information from this second file and then paste it into the original file.

How do I reactivate the original file.
(The macro recorder comes up with Windows(XXXX.XLS).activate, which will not work for variants in the file names.

After pasting the information, the static file is closed.

I tried
Workbook.vFilename.Activate
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Workbooks(vFilename).Activate
 
Upvote 0
Still bombs out at Workbooks(vFilename).Activate

Sub AssignNumber()


Dim vFileNames As String
vFileNames = ThisWorkbook.Name

Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Excel test Folder\Counter.xls"
Range("B4").Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
Selection.Copy
Workbooks(vFilename).Activate
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("counter.xls").Activate
ActiveWorkbook.Save
ActiveWindow.Close
Range("C4").Select
End Sub
 
Upvote 0
Try:

Dim vFileNames As Object
Set vFileNames = ActiveWorkbook

'Other code here!

vFileNames.Select
 
Upvote 0
Sub isWBNowOpen()
'Standard module code, like: Module1.
Dim wb&
Dim myWB As Boolean
Dim myWorkbookName$

myWorkbookName = "AAA.xls"
myWB = False

For wb = 1 To Workbooks.Count
If Workbooks(wb).Name = myWorkbookName Then myWB = True
Next wb

If myWB = False Then Workbooks.Open (myWorkbookName)
Windows(myWorkbookName).Activate

'Now what ever Workbook name is in "myWorkbookName" will be active and ready to work with.
End Sub
 
Upvote 0
Solution
Why not create references to the workbooks?

For example:
Code:
Set wbThis = ThisWorkboook ' create reference to workbook the code is in
Code:
Set wbAct  = ActiveWorkbook ' create reference to the active workbook
Code:
Set wbOpen = Workbooks.Open("C:\MyPath\MyBook.xls") ' open workbook and create a reference to it
Code:
Set wbNew = Workbooks.Add ' create new workbook and a reference to it
 
Upvote 0
Sorry, my mistake,

Workbooks(vFileNames).Activate (with the s) ... works perfectly

Thanks for all the suggestions. I will try them as well.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top