Column names for dump of text file to Excel
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 |
Column names for dump of text file to Excel
|
All times are GMT +1. The time now is 04:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com