First, you use the FileSystemObject to obtain the list of files.
Like:
Sub FileList()
Dim fso, File, Files, FldrName
Dim Fldr, msg
Set fso = CreateObject("Scripting.FileSystemObject")
FldrName = "C:\temp\"
If fso.folderexists(FldrName) Then
msg = FldrName & Chr(13)
Set Fldr = fso.getfolder(FldrName)
For Each File In Fldr.Files
msg = msg & Chr(13) & File.Name
Next File
End If
MsgBox msg
End Sub
Next...
Why are you using the External Data function?
You say you want to "archive over 100 txt files".
I take it that you're wanting to convert the files to Excel
and then remove the files?
(hence the term "archive")
using the External Data function creates a "link" to the original data. this
type of thing is usually used to import data and then MAINTAIN the data so that
when the data is modified, your spreadsheet (workbook) is "automatically"
updated.
You can call up text files in Excel.
In fact, when you open a text file (even with a .cfg extension) it
AUTOMATICALLY names the tab with the file name?
Of course, if you're trying to put all of these into a single workbook, then
you'd have to record the macro in which you move the sheet from the new
workbook to the "Archive" workbook...
How is that different than what you're looking for?
Paul
________________________________
From: mmccaws2 <[email protected]>
To: MS EXCEL AND VBA MACROS <[email protected]>
Sent: Monday, June 15, 2009 3:51:34 PM
Subject: $$Excel-Macros$$ Re: importing multiple data files (csv)
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 <[email protected]> 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 <[email protected]>
> To: MS EXCEL AND VBA MACROS <[email protected]>
> 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 [email protected]
If you find any spam message in the group, please send an email to:
Ayush Jain @ [email protected] or
Ashish Jain @ [email protected]
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
