Sheets Name

Hi,
I use this code to load and parses a test file it can have any name
Workbooks.OpenText FileName:= _
ThisWorkbook.Path & "\Input\" & FileName, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 4), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 4))

The worksheet name is need for the next step. So I have two questions
1. can I get the name from the workbook I have just opened
2. are there a simple set of rules to get from the file name to sheet name

i.e.

Text File Name Worksheet Name
GLOBALM_MDFF_19981108.I080 GLOBALM_MDFF_19981108
INTEGM_WPWF_PRUD_20060121.I474229 INTEGM_WPWF_PRUD_20060121.I4742
AextrasandabitGLOBALM_MDFF_19990116.I056

AextrasandabitGLOBALM_MDFF_1999

Thanks MarkS

There is n o simple rule because a workbook can have many sheets.

Activesheet.Name

will get the name of the activesheet in the newly opened workbook, until
another sheet is selected.

Worksheets(1).Name

gets the name of the first worksheet in the book.

As you seem to be talking about a text file, activesheet should be fine.

Activeworkbook.Name

gets the name of the newly opened workbook.

Another way is to set a variable

sFilename = ThisWorkbook.Path & "\Input\" & Filename

Workbooks.OpenText Filename:=sFilename, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 4),
_
Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1),
Array(11, 4))

Set oWB = ActiveWorkbook


and then use that

oWB.ACtivesheet

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MarkS" <MarkS@discussions.microsoft.com> wrote in message
news:08409B78-03B1-4FF0-90F5-DB50301F2191@microsoft.com...
> Hi,
> I use this code to load and parses a test file it can have any name
> Workbooks.OpenText FileName:= _
> ThisWorkbook.Path & "\Input\" & FileName, Origin:= _
> xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
> Semicolon:=False, _
> Comma:=True, Space:=False, Other:=False,
> FieldInfo:=Array(Array(1, 1), _
> Array(2, 4), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1),
> Array(7, 1), Array(8, 1), _
> Array(9, 1), Array(10, 1), Array(11, 4))
>
> The worksheet name is need for the next step. So I have two questions
> 1. can I get the name from the workbook I have just opened
> 2. are there a simple set of rules to get from the file name to sheet name
>
> i.e.
>
> Text File Name Worksheet Name
> GLOBALM_MDFF_19981108.I080 GLOBALM_MDFF_19981108
> INTEGM_WPWF_PRUD_20060121.I474229 INTEGM_WPWF_PRUD_20060121.I4742
> AextrasandabitGLOBALM_MDFF_19990116.I056
>
> AextrasandabitGLOBALM_MDFF_1999
>
> Thanks MarkS
>
>