VBA Excel question

[Michael]

Hi everyone,

Say I have this VBA piece of code:

filename = Range("A1")
On Error Resume Next
Set WBook = Workbooks(filename)
On Error GoTo 0
If WBook Is Nothing Then
Workbooks.Open filename:=filename
End If

Here, I am tell VBA to open a filename specified in cell A1.

Now, say that filename/workbook has several wroksheets and one of them
is called "ABC". I want VBA to open that workbook at that worksheet
(i.e. ABC). What else should I add to the above code?

Thanks,
Mike

 

[Gord Dibben]

Michael

Dim WBook as Workbook
filename = Range("A1")
On Error Resume Next
Set WBook = Workbooks(filename)
On Error GoTo 0
If WBook Is Nothing Then
Workbooks.Open filename:=filename
End If
WorkSheets("ABC").Select

Gord Dibben Excel MVP

 

[JMay]

Does
filename = Range("A1") << line 2 -- assume Sheet(1) in the Workbook?
Thanks,

 

[Frank Kabel]

Hi
it assumes the active sheet. You may add a worksheet qualifier in front
of it. e.g.
filename = worksheets("Sheet1").Range("A1").value

--
Regards
Frank Kabel
Frankfurt, Germany

Does
filename = Range("A1") << line 2 -- assume Sheet(1) in the
Workbook? Thanks,

Michael

Dim WBook as Workbook
filename = Range("A1")
On Error Resume Next
Set WBook = Workbooks(filename)
On Error GoTo 0
If WBook Is Nothing Then
Workbooks.Open filename:=filename
End If
WorkSheets("ABC").Select

Gord Dibben Excel MVP

 

[JMay]

Thanks Frank,
So if someone last saved the Workbook referred to by Wbook
with Sheet2 Active at the time of saving <<where Sheet2 Cell A1 was Blank>>
then without adding the worksheet qualifier (as you suggest) things could
"go-wrong"..
right?
Thanks for your valued input!
(I'm gonna get this stuff yet!!)
JMay

Hi
it assumes the active sheet. You may add a worksheet qualifier in front
of it. e.g.
filename = worksheets("Sheet1").Range("A1").value

--
Regards
Frank Kabel
Frankfurt, Germany

Does
filename = Range("A1") << line 2 -- assume Sheet(1) in the
Workbook? Thanks,

 

[Frank Kabel]

Thanks Frank,
So if someone last saved the Workbook referred to by Wbook
with Sheet2 Active at the time of saving <<where Sheet2 Cell A1 was
Blank>> then without adding the worksheet qualifier (as you suggest)
things could "go-wrong"..
right?

yes