About Us

Jake[_13_]

wrote:
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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Format column to link file names in that column	desiaashik	Excel Discussion (Misc queries)	2	March 29th 09 05:11 PM
Seeing Text File Names when Saving Excel Files	jkiser	New Users to Excel	4	January 1st 09 01:57 PM
Want to dump text written on page 1 to page 2 in excel	Moni	Excel Worksheet Functions	3	November 2nd 07 07:01 PM
dump file search results into excel	Nadia	Excel Discussion (Misc queries)	2	July 29th 05 02:43 AM
Change names of files in a folder to match names in Excel Column	saybut	Excel Programming	4	February 9th 04 06:26 PM

Menu

About Us