I have a system that creates a delimited text file that I can import into
Excel using the "get external data from text file" option and ... after
answering all the question, imports just fine.

The problem is that this text file contains 160 different sections that have
to be imported into Excel into 160 different locations, so the job becomes
ponderous.

Since I have some control over the input text (as long as it stays text) I
could add any kind of "section_1_starts_here" and/or "section_1_ends_here"
markers in the text.

My questions are 1) Can a macro be created to import this data and 2) how
complex/intelligent would that Macro have to be?

Here is the code I given many people. It is written to open more than one
file.


Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0



Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"

Newfolder = Application.GetOpenFilename("CSV (*.csv),*.csv")
If Not Newfolder = False Then
Folder = ""
Do While InStr(Newfolder, "\") 0
Folder = Folder & Left(Newfolder, InStr(Newfolder, "\"))
Newfolder = Mid(Newfolder, InStr(Newfolder, "\") + 1)
Loop
'remove last character which is a \
Folder = Left(Folder, Len(Folder) - 1)
End If

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = LastRow + 1
First = True
Do
If First = True Then
filename = Dir(Folder & "\*.csv")
First = False
Else
filename = Dir()
End If
If filename < "" Then
'open files
Set fread = fsread.GetFile(Folder & "\" & filename)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine < ""
CommaPosition = InStr(InputLine, ",")
If CommaPosition 0 Then
Data = Trim(Left(InputLine, CommaPosition - 1))
InputLine = Mid(InputLine, CommaPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
Loop
End Sub



"DBme" wrote:

I have a system that creates a delimited text file that I can import into
Excel using the "get external data from text file" option and ... after
answering all the question, imports just fine.

The problem is that this text file contains 160 different sections that have
to be imported into Excel into 160 different locations, so the job becomes
ponderous.

Since I have some control over the input text (as long as it stays text) I
could add any kind of "section_1_starts_here" and/or "section_1_ends_here"
markers in the text.

My questions are 1) Can a macro be created to import this data and 2) how
complex/intelligent would that Macro have to be?

Thanks -- that's clearly over my head, but I guess I can learn ... more
important it tells me that it can be done. Thanks!

"Joel" wrote:

Here is the code I given many people. It is written to open more than one
file.


Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0



Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"

Newfolder = Application.GetOpenFilename("CSV (*.csv),*.csv")
If Not Newfolder = False Then
Folder = ""
Do While InStr(Newfolder, "\") 0
Folder = Folder & Left(Newfolder, InStr(Newfolder, "\"))
Newfolder = Mid(Newfolder, InStr(Newfolder, "\") + 1)
Loop
'remove last character which is a \
Folder = Left(Folder, Len(Folder) - 1)
End If

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = LastRow + 1
First = True
Do
If First = True Then
filename = Dir(Folder & "\*.csv")
First = False
Else
filename = Dir()
End If
If filename < "" Then
'open files
Set fread = fsread.GetFile(Folder & "\" & filename)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine < ""
CommaPosition = InStr(InputLine, ",")
If CommaPosition 0 Then
Data = Trim(Left(InputLine, CommaPosition - 1))
InputLine = Mid(InputLine, CommaPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
Loop
End Sub



"DBme" wrote:

I have a system that creates a delimited text file that I can import into
Excel using the "get external data from text file" option and ... after
answering all the question, imports just fine.

The problem is that this text file contains 160 different sections that have
to be imported into Excel into 160 different locations, so the job becomes
ponderous.

Since I have some control over the input text (as long as it stays text) I
could add any kind of "section_1_starts_here" and/or "section_1_ends_here"
markers in the text.

My questions are 1) Can a macro be created to import this data and 2) how
complex/intelligent would that Macro have to be?

I would use only Start and end. Have the data include where it is suppose to
go. Here is some code that may help.

start,Sheet2,A5
your data


end


Const Start_State = 1
Const Data_State = 2

State = Start_State
Do While tsread.atendofstream = False

InputLine = tsread.ReadLine
select case State

Case Start_State
if Left(InputLine,5) = "Start" then
'your code here to get worksheet name and start range
State = Data_State
end if
Case Data_State
if Left(InputLine,3) = "End" then
Data_Start = Start_State
else
'your code here to extract data
end select


"DBme" wrote:

I have a system that creates a delimited text file that I can import into
Excel using the "get external data from text file" option and ... after
answering all the question, imports just fine.

The problem is that this text file contains 160 different sections that have
to be imported into Excel into 160 different locations, so the job becomes
ponderous.

Since I have some control over the input text (as long as it stays text) I
could add any kind of "section_1_starts_here" and/or "section_1_ends_here"
markers in the text.

My questions are 1) Can a macro be created to import this data and 2) how
complex/intelligent would that Macro have to be?