vba code to replace first row in CSV file

[JP]

I receive CSV files that I need to open and replace the first row of data
with different column headings and close and save the file.

When I do this in Excel and save it, it truncates data below the first row
that begins with 0. For example, 000300 is saved as 300.

Is there a way to do this with the Open and Print statements in VBA?

 

[GS]

If you are putting the data on a spreadsheet before making your revisions
you will get exactly the result you state here. It would be better to read
the file into a string variable and replace the first line with the data you
want to insert, then write the file back.

This will require parsing the first line in a For Each... Next loop using
the Split() function and specifying vbCrLf as the delimiter. Once you have
the first line loaded into a variable you could use the Replace() function to
swap it with your new first line.

HTH
Kind regards, Garry

 

[GS]

Here's some reuseable functions to read/write text files, and a sub that does
what you want.

Sub ReplaceText_CsvHeadings()
Dim vSz As Variant, vFilename As Variant
Dim sTextNew As String, sTextOld As String, sFileText As String

'Get the file contents
vFilename = Application.GetOpenFilename
If vFilename = False Then Exit Sub '//user cancels
sFileText = ReadTextFileContents(CStr(vFilename))

'Parse the first line from the file
For Each vSz In Split(sFileText, vbCrLf)
If Not vSz = Empty Then sTextOld = vSz: Exit For
Next vSz

'Replace the first line with new headings
sTextNew = "000100,000200,000300,000400,000500" '**replace with your data
sFileText = Replace(sFileText, sTextOld, sTextNew)
WriteTextFileContents sFileText, vFilename, False
End Sub

Function ReadTextFileContents(Filename As String) As String
'A reuseable procedure to read large amounts of data from a text file

Dim iNum As Integer
Dim bIsOpen As Boolean

On Error GoTo ErrHandler
iNum = FreeFile()
Open Filename For Input As #iNum
'If we got here the file has opened successfully
bIsOpen = True

'Read the entire contents in one single step
ReadTextFileContents = Input(LOF(iNum), iNum)

ErrHandler:
'Close the file
If bIsOpen Then Close #iNum
If Err Then Err.Raise Err.Number, , Err.Description
End Function 'ReadTextFileContents()

Sub WriteTextFileContents(Text As String, Filename As String, Optional
AppendMode As Boolean)
' A reuseable procedure to write or append large amounts of data to a text
file

Dim iNum As Integer
Dim bIsOpen As Boolean

On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then Open Filename For Append As #iNum Else Open Filename
For Output As #iNum
'If we got here the file has opened successfully
bIsOpen = True

'Print to the file in one single step
Print #iNum, Text


ErrHandler:
'Close the file
If bIsOpen Then Close #iNum
If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFileContents()

HTH
Garry

 

[GS]

oops!
The last line should read:

WriteTextFileContents sFileText, CStr(vFilename), False

Sorry about that!
GS