Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets Name
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
Allocate Files to Sheets and Build a Master Sheet which Summarises All Sheets | Excel Programming |