Macro to load textfiles to excel

[undercrisis01]

Hi everyone,

Im not good at doing macros and i need anyones help with this problem,

i got loads of textfiles to work on and it is much convenient to work with all these files into one excel file, so im thinking of doing a macro with it.

At present im doing this stuff manually by loading the texfiles in excel one by one and applying a text tab delimited with ";" as separator.

my idea is to save all textfiles to a folder say, C:\\Temp\, and telling excel to import all text files in that directory into one spreadsheet.

The problem is, i have the idea but i dont know how to make it to a reality.

any help from experts such as people from this forum would be greatly appreciated

Thanks in advance

 

[syntaxed]

Just paste the code given at the following link into a standard module and replace

Print #1, "Copy " & Chr(34) & foldername & "*.csv" _

with

Print #1, "Copy " & Chr(34) & foldername & "*.txt" _

http://www.rondebruin.nl/csv.htm

Edit:
Just saw that you need it to be delimited by semicolons.

You'll need to replace:

ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _

With:

ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, _

 

[undercrisis01]

by running a record macro i returned to this code. And by the way im using a MS office 2007.
This code is good for specified Textfiles, but will not work if random files must be transferred to the excel file

Sub Macro1()
'
' Macro1 Macro
Cells.Select
Selection.NumberFormat = "@"
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\temp\L0271201AA01-00.txt", Destination:=Range("$A$1"))
.Name = "L0271201AA01-00"
.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 = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=60
Range("A74").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\temp\L0271202AA01-00.txt", Destination:=Range("$A$74"))
.Name = "L0271202AA01-00"
.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 = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=66
Range("A144").Select
End Sub

Any suggestions?

 

[undercrisis01]

Just paste the code given at the following link into a standard module and replace

Print #1, "Copy " & Chr(34) & foldername & "*.csv" _

with

Print #1, "Copy " & Chr(34) & foldername & "*.txt" _

http://www.rondebruin.nl/csv.htm

Oh thanks for this dude..

 

[undercrisis01]

Oh by the way, say i want to format the spreadsheet to text format before the files are imported to it, just to prevent values from getting changed such as with E or with 0000 to stay as is.

where can i insert the format code?


Cells.Select
Selection.NumberFormat = "@"
Range("A1").Select

thanks for the advice

 

[undercrisis01]

Oh by the way, say i want to format the spreadsheet to text format before the files are imported to it, just to prevent values from getting changed such as with E or with 0000 to stay as is.

where can i insert the format code?

Cells.Select
Selection.NumberFormat = "@"
Range("A1").Select

thanks for the advice