first of all,
record a macro in which you open one of the files in the folder
the way you want to open it (space delimited, skip columns).
Next, check out the documentation on the "Files Collection",
the example looks like:
Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 in fc
s = s & f1.name
s = s & vbCrLf
Next
MsgBox s
End Sub
You can modify this to be:
Sub ImportData()
Dim folderspec, UserFilename
Dim fs, f, f1, fc, s
folderspec = "C:\Documents and Settings\All Users\officemenu
\Desktop\NewProto\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 in fc
UserFilename = f1.name
Workbooks.OpenText Filename:= folderspec & UserFilename, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 9),
Array _
(3, 9), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
Next
End Sub
or:
Workbooks.OpenText Filename:= f1.path, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 9),
Array _
(3, 9), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
hope this helps,
Paul
________________________________
From: Tony <tblatn...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Friday, July 24, 2009 11:20:23 AM
Subject: $$Excel-Macros$$ Help importing multiple .txt filew with delimters
Hi everyone...
First off, i am not a VBA guru, so most of the stuff I have found is
from the net...
I have a folder with 10 .txt files (maybe more eventually) that need
to be imported into a single excel document. I have the following code
i found, to import one, but it is not space delimiting, or removing
the first 3 columns...
Option Explicit
Const FILEPATH = "C:\Documents and Settings\All Users\officemenu
\Desktop\NewProto"
Sub ImportData()
Dim UserFilename As String
Dim NewImport As Variant
'ChDir FILEPATH
ChDir "C:\Documents and Settings\All Users\officemenu\Desktop
\NewProto"
UserFilename = Application.GetOpenFilename '(filefilter:="Text
Files(*.txt),*.txt", Title:="Text Files to Import")
NewImport = "Text;" & UserFilename
Sheets("Sheet1").Select
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:=NewImport, _
Destination:=Range("A1"))
.Name = "TabDelimitedFile"
.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
.TextFileTabDelimiter = True
' This is for importing four columns
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
' it would read ...=Array(1, 1, 1, 1, 1, 1, 1, etc.....) if
you imported 7 or more columns
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
I have tried to add in a .TextFileSpace = True , but it bugs on that
line. I have also tried to eliminate colums using
.TextFileColumnDataTypes = Array(Array(1,9), (2,9), (3,9), (4,1)) ,
but it says that type is not supported.
Also once I run this is only works for one file, i have tried to make
a FNum loop for the number of files in the folder using
'Fill the array(myFiles)with the list of Excel files in the folder
FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop
With a If FNum > 0, and increment, but it does not appear to work.
Any help would be GREATLY appreciated!!!!!
Thanks!
tony
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,200 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
