CODEWITHSUNDEEP
excelvba
Madosa
Madosa

Reputation: 15

Open a Workbook that is in the background

I'm trying to open a workbook that is in the background. 

Dim app As New Excel.Application
app.Visible = False
Dim book As Excel.Workbook
Set book = app.Workbooks.Add(UserForm1.TextBox1.Text)

I get the filename out of a textbox.

I get a crash at:

Sheets("Start").select

I guess the workbook in the background is not the active one. How can I activate it?

My idea was: 

Workbooks(UserForm1.TextBox1.Text).Activate

But I only have the filename, not the whole path and filename.

Upvotes: 0

Views: 10667

Answers (3)

user1644564
user1644564

Reputation: 385

Always avoid selecting or activating anything whether it's a cell, a range, a sheet or a workbook.

When you write your code refer to

Workbooks("freds wrk").Worksheets("Start").Cells(1, 1)
Workbooks("freds wrk").Worksheets("Start").Range("A1")

You can also use the With command to make it shorter.

With Workbooks("freds wrk").Worksheets("Start")
    .Cells(1, 1)
    .Range("A1")
end with

When your code is running a end user will just start clicking buttons and then everything will go wrong because what you think is active is no long active. And what you think is selected isn't any more.

The problems get worse when you start calling sub's in the middle of your code. How can you know what sheet is now going to be active and what is now selected by the sub you just called.

And a with statement will make your code run faster. The Excel Objects Workbook, Worksheet, Cell and Range are amazingly slow for the computer to handle.

Upvotes: 1

MP24
MP24

Reputation: 3200

You could just use book.Activate followed by any code you want to perform on the new workbook. And please note that most actions are possible without using .Select. See How to avoid using Select in Excel VBA macros for more information.

Upvotes: 1

R. Karl
R. Karl

Reputation: 16

You could try book.Sheets("Start").select

I think the better way is using the with statement

With book.Sheets("Start") .cells("A1").Select End With

Upvotes: 0

Related Questions