Sorry for the delay. I've never worked with microsofts programming
languages, so I've been experimenting when I have time.
Here is what I've learned from monkeying around with the macro
recorder. The file selected when using the "Data -> input external
data" function has a name property. And I want that name property
inserted into the worksheet's Sheets("Sheet1").Name property
So, learning that helps me eliminate the request on parsing the text
for the name character sequence.
So, back to my original goal, I'm trying to consolidate, or archive,
over a 100 txt files to be assigned a worksheet for each file. And
each file's name is inserted into the tab name. So I'm trying to
figure how to take each .cfg file in a directory insert it into it's
own sheet, and name that worksheet with it's filename.
Sub import2DatanameWorksheet()
'
' import2DatanameWorksheet Macro
' Macro recorded 6/15/2009 by Test Image
'
'
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\work\DNS\bkup\5-11\diablo.cfg", Destination:=Range( _
"A2"))
.Name = "diablo"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Name = "diablo.cfg"
End Sub
How do I loop through the file directory names and use wild cards to
avoid creating a list file?
I realize that seems pretty simple, but I'm new to VBA.
I'd appreciate your help.
Mike
On Jun 2, 5:15 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> Am I missing part of this thread?
> Or is this a new topic?
>
> If it's new... what you're asking is fairly simple.
> But I need more info.
>
> Are the files in a single folder?
> What is the format of the name that you want to parse to get the third "word"?
> like: "file_For_Tommy.xls", "file For Tommy.txt", what delimits a "word"?
>
> Are you familiar with VBA?
> Have you started?
>
> I think I can throw something together quickly (10-15 minutes).
>
> Paul
>
> ________________________________
> From: mmccaws2 <mmcc...@comcast.net>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Monday, June 1, 2009 4:34:57 PM
> Subject: $$Excel-Macros$$ importing multiple data files (csv)
>
> Hi
>
> Maybe I'm not using the right search terms, but I couldn't locate how
> to import over 100 small files into Excel. Each file should get it's
> own worksheet. And each worksheet should be name with the 3rd "word"
> or character string in the file. In case someone is wondering, the
> total number of files is less than 1MB.
>
> Thanks
>
> Mike
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
