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,000 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
-~----------~----~----~----~------~----~------~--~---
