ActiveSheet not in ActiveWorkbook?
 

c:\SomeTextFile.txt is a text file created in NotePad that simply contains

A B C D E
F G H I J

Go into VBA and run this code:

Public Sub OpenTextFile()
Dim wbActive As Workbook, wsActive As Worksheet
Dim wbActiveSheetParent As Workbook, wsRangeParent As Worksheet
Const cstrFileName As String = "c:\SomeTextFile.txt"
If Len(Dir(cstrFileName)) 0 Then
Workbooks.OpenText Filename:=cstrFileName
Set wbActive = ActiveWorkbook
Set wsActive = ActiveSheet
Set wbActiveSheetParent = wsActive.Parent
Set wsRangeParent = ActiveCell.Parent
MsgBox "wbActive: " + wbActive.Name + vbCrLf + "wsActive: " +
wsActive.Name + vbCrLf + _
"wbActiveSheetParent: " + wbActiveSheetParent.Name + vbCrLf +
"wsRangeParent: " + wsRangeParent.Name
ActiveWorkbook.Close
End If
End Sub


When the message box appears, I see a spreadsheet with the file I just
opened. SomeTextFile.txt is in the title bar, and the Worksheet I see is
named SomeTextFile.

My message box contains:

wbActive: SomeTextFile.txt
wsActive: Sheet1
wbActiveSheetParent: Book1
wsRangeParent: SomeTextFile

How is this possible? Is this a known bug? Has anyone else run across this?

Bob

ActiveSheet not in ActiveWorkbook?
 

When I run your code in Excel 2000, my message box contains:

wbActive: SomeTextFile.txt
wsActive: SomeTextFile
wbActiveSheetParent: SomeTextFile.txt
wsRangeParent: SomeTextFile

Is this what you were expecting?
--
Regards,
Bill Renaud

ActiveSheet not in ActiveWorkbook?
 

Bill,

Yes, that IS what I was expecting .... but what I get is

wbActive: SomeTextFile.txt
wsActive: Sheet1
wbActiveSheetParent: Book1
wsRangeParent: SomeTextFile

I'm running Excel 2003, what version are you running?

Bob


"Bill Renaud" wrote:

When I run your code in Excel 2000, my message box contains:

wbActive: SomeTextFile.txt
wsActive: SomeTextFile
wbActiveSheetParent: SomeTextFile.txt
wsRangeParent: SomeTextFile

Is this what you were expecting?
--
Regards,
Bill Renaud

ActiveSheet not in ActiveWorkbook?
 

<<what version are you running?

Excel 2000, as I mentioned in the first line of my post.
Actually, to be more correct: Excel 2000 SP-3 (Service Pack 3; which I
believe is the last one).

Do you have all of the service releases (or patches) applied?
--
Regards,
Bill Renaud

ActiveSheet not in ActiveWorkbook?
 

Hi Bill,

Yes, I noticed that after I posted my reply. Sorry about that.

I am using Excel 2003, version (11.8146.8132) SP2 on Win2K

Actually, now that I mentioned the OS, I tried the same test with the same
version of Excel on a WinXp machine, and it works as expected. I guess I
should have tried that first.

So, maybe it's OS related.

Bob

"Bill Renaud" wrote:

<<what version are you running?

Excel 2000, as I mentioned in the first line of my post.
Actually, to be more correct: Excel 2000 SP-3 (Service Pack 3; which I
believe is the last one).

Do you have all of the service releases (or patches) applied?
--
Regards,
Bill Renaud