Column names for dump of text file to Excel

[jakethedog317]

I have a macro that calls the following VB script within Excel to load
a txt file that is ";" delimited to a spreadsheet within Excel


Private Sub LoadXLS()

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Dim FName As String
Dim Sep As String

FName = "c:\dumpfile.txt"
Sep = ";"
Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

c:\dumpfile.txt contents

Complete by;Workflow Description;Task;Workflow Procedure;Job ID;WF id
<----first line

2006 09081115010;Smith and Jones Inc.: 999,990.00 due by
10/15/2006;Review/Approve;AP Processing;0000000006;0000000042 <
---second line

This file has two lines the first line is the title for each column the
second line is the data for each column for one row in the spreadsheet.

Currently my code will put the right data into the spreadsheet for each
column but what I really want is the first row to be in the column
title location of the spreadsheet and not the first row of the
spreadsheet. I know that the wizard has a checkbox to allow you to set
the first row of a text file to be the column titles but is there a
call that I can do that will enable me to do this within my module?
Examples would be helpful

Thanks
Jake

 

[Jake]

I have a macro that calls the following VB script within Excel to load
a txt file that is ";" delimited to a spreadsheet within Excel


Private Sub LoadXLS()

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Dim FName As String
Dim Sep As String

FName = "c:\dumpfile.txt"
Sep = ";"
Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

c:\dumpfile.txt contents

Complete by;Workflow Description;Task;Workflow Procedure;Job ID;WF id
<----first line

2006 09081115010;Smith and Jones Inc.: 999,990.00 due by
10/15/2006;Review/Approve;AP Processing;0000000006;0000000042 <
---second line

This file has two lines the first line is the title for each column the
second line is the data for each column for one row in the spreadsheet.

Currently my code will put the right data into the spreadsheet for each
column but what I really want is the first row to be in the column
title location of the spreadsheet and not the first row of the
spreadsheet. I know that the wizard has a checkbox to allow you to set
the first row of a text file to be the column titles but is there a
call that I can do that will enable me to do this within my module?
Examples would be helpful

Thanks
Jake

Never mind I got off on the wrong track - i realize now that its not
the column that is named with the first row of the txt file in excel
but rather the table column in Access when you import the xls file