VBA- Activating workbook variable

A

Alpha1980

Board Regular
Joined
Feb 28, 2008
Messages
125
If I want to activate a workbook, I would do this:

Code: 
Windows("File.xls").Activate

But what if the File.xls was a variable named File?

I have tried this:

Code: 
Windows(File).Activate

Without success. Please advise.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
BrianB said:
Code: 
Dim MyFile As String
MyFile = "Book1.xls"
Workbooks(MyFile).Activate
Click to expand...
Thanks Brian. Should this still work if I use Application.GetOpenFilename to select the file?
Code: 
Dim MyFile As String

    MyFile = Application.GetOpenFilename _
      (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
      MultiSelect:=False, Title:="File to Open")
    
    Workbooks.Open Filename:=MyFile, Local:=True

    Workbooks(MyFile).Activate
 
Upvote 0
Alpha1980 said:
Thanks Brian. Should this still work if I use Application.GetOpenFilename to select the file?
Code: 
Dim MyFile As String

    MyFile = Application.GetOpenFilename _
      (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
      MultiSelect:=False, Title:="File to Open")
    
    Workbooks.Open Filename:=MyFile, Local:=True

    Workbooks(MyFile).Activate
Click to expand...




Hi Guys,

I am using the below code as like the same above

Dim MyDir As String
Dim Filename As String
MyDir = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
Workbooks.Add
Filename = Application.InputBox("Enter new report name", "Starting a new report", Type:=2)
ActiveWorkbook.SaveAs Filename:=MyDir & Filename

Windows(Filename).Activate ' I am getting an error here, My system it's working fine but not with other'

Looking for help
Thanks
Anto
 
Upvote 0
Hi
In theory we should not need the .Activate line because Excel puts the latest workbook on top.
Your code works Ok. However I did add the line
Code: 
Filename = Filename & ".xls"
Strangely, in passing, I found GetOpenFileName worked even though I am using Excel 2010. I was sure that this was discontinued and I have had to replace this in the recent past.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Create Shortcut to Excel & an Excel file using VBA
Replies
4
Views
217
RoryA
RoryA
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
156
shknbk2
S
T
  • Question
UserForm Close Event Hangs
Replies
0
Views
134
trough
T
A
  • Question
VBA to unlock worksheet when clicking on "refresh all" ribbon button so it can refresh a power query source
Replies
1
Views
365
PeteWright
PeteWright
C
  • Solved
VBA to open file, clear formats and copy paste
Replies
9
Views
245
Alex Blakenburg
A

Forum statistics

Threads
1,214,559
Messages
6,120,194
Members
448,951
Latest member
jennlynn

Share this page

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