VBA Excel question

  • Thread starter Thread starter Michael
  • Start date Start date
M

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
 
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
 
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,

Gord Dibben said:
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


Click to expand...
Click to expand...
 
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

Frank Kabel said:
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,
Click to expand...
Click to expand...
 
JMay said:
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?
Click to expand...


yes :-)
 
Back
Top