View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
INTP56 INTP56 is offline
external usenet poster
 
Posts: 66
Default 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