0

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.

Community
  • 1
  • 1
Madosa
  • 15
  • 1
  • 3
  • 6
  • 1
    But why do you want to `.Select` the sheet? In all likelihood, you don't need to. Most operations can be done without using `.Select`. What do you want to do afterwards? – Jean-François Corbett Aug 26 '14 at 08:15

3 Answers3

1

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.

Community
  • 1
  • 1
MP24
  • 3,110
  • 21
  • 23
1

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.

user1644564
  • 385
  • 3
  • 11
0

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

R. Karl
  • 16
  • 3
  • Thanks for the answer. It works ! But its still not the active Workbook.. I want to activate the workbook, do my code and then close it. – Madosa Aug 26 '14 at 07:01
  • IMHO is sheets with no statement before always referencing to the workbook wich contains the code. An alternative way is using [With .. End With](http://msdn.microsoft.com/en-us/library/wc500chb.aspx). Besides it's a bit better for performance. :) – R. Karl Aug 26 '14 at 07:11
  • I had a coffee break, have already edited it .. but to slow ;) – R. Karl Aug 26 '14 at 07:28