Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to replace first row in CSV file
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to replace first row in CSV file
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 "JP" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to replace first row in CSV file
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to replace first row in CSV file
oops!
The last line should read: WriteTextFileContents sFileText, CStr(vFilename), False Sorry about that! GS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using code to replace code modules | Excel Programming | |||
Replace text with variable using VBA replace code? | Excel Programming | |||
If I accidently replace a saved file can I retrieve the old file? | Excel Discussion (Misc queries) | |||
Import VBA Code in Excel-File ? (Export VBA Code to file) | Excel Programming | |||
the file already exists - do you want to replace the existing file? | Excel Programming |