Hi,
I got some code from TSTom to export and import ranges from excel to a text file. This has been working great, but now its time to put the text files to use. Im trying to import the ranges from the text files in a directory to a single sheet to do Data analysis on it. I have most of the code, but i have a few problems. Here is the code first:
Sub DataReport()
With Application.FileSearch
.LookIn = LookIn
.SearchSubFolders = True
.Filename = "*" & Criteria & "*.txt"
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
'################################################
'restore from textfile Code
Dim BackUpRange
Dim CurrentBytePosition
Dim FilePathAndName
Dim FileNum
Dim DataSheet As Worksheet
FileNum = FreeFile
CurrentBytePosition = 1
FilePathAndName = .FoundFiles(i)
Open FilePathAndName For Binary As #FileNum
'will restore
Get #1, CurrentBytePosition, BackUpRange
BColCount = AColCount + 4
Sheets("DataReport").Range(Sheets("DataReport").Cells(1, AColCount), Sheets("DataReport").Cells(1000, BColCount)) = BackUpRange
Close FileNum
'################################################
AColCount = BColCount + 1
Next i
End Sub
My first problem: This code works, but it quickly reaches column 256 which is the max in excel. Is there a way to convert the imported range from columns to rows?
Another question is: I export 5 columns of data to the text file. Can i choose which columns i want to import (the 2nd column for example) or do i have to import the WHOLE text file, then do my editing?
I appreciate the help.
Thanks
I got some code from TSTom to export and import ranges from excel to a text file. This has been working great, but now its time to put the text files to use. Im trying to import the ranges from the text files in a directory to a single sheet to do Data analysis on it. I have most of the code, but i have a few problems. Here is the code first:
Sub DataReport()
With Application.FileSearch
.LookIn = LookIn
.SearchSubFolders = True
.Filename = "*" & Criteria & "*.txt"
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
'################################################
'restore from textfile Code
Dim BackUpRange
Dim CurrentBytePosition
Dim FilePathAndName
Dim FileNum
Dim DataSheet As Worksheet
FileNum = FreeFile
CurrentBytePosition = 1
FilePathAndName = .FoundFiles(i)
Open FilePathAndName For Binary As #FileNum
'will restore
Get #1, CurrentBytePosition, BackUpRange
BColCount = AColCount + 4
Sheets("DataReport").Range(Sheets("DataReport").Cells(1, AColCount), Sheets("DataReport").Cells(1000, BColCount)) = BackUpRange
Close FileNum
'################################################
AColCount = BColCount + 1
Next i
End Sub
My first problem: This code works, but it quickly reaches column 256 which is the max in excel. Is there a way to convert the imported range from columns to rows?
Another question is: I export 5 columns of data to the text file. Can i choose which columns i want to import (the 2nd column for example) or do i have to import the WHOLE text file, then do my editing?
I appreciate the help.
Thanks