overwrite a csv file using vba

Question

There are a number of similar posts but nothing that does exactly what I want as simply as it needs to be for me to understand

I want to use Access 2007 VBA to open a csv file and replace the column headings row ie:

OldColumn1,OldColumn2

1,2

with

NewColumn1,NewColumn2

1,2

ie without disturbing the rump of data.

Then save and close.

I have tried this code, but it deletes my data:

Sub WriteFile()


  Dim OutputFileNum As Integer
  Dim PathName As String

  PathName = Application.ActiveWorkbook.Path
  OutputFileNum = FreeFile

  Open PathName & "\Test.csv" For Output Lock Write As #OutputFileNum

  Print #OutputFileNum, "NewCol1" & "," & "NewCol2"


  Close OutputFileNum

End Sub

Automate the routine with the Excel VBA reference instead, this will allow you to select and amend individual cells, e.g OldColumn1 may be in cell A1, this would be easier to work with. — Matt Donnan
– Matt Donnan, Commented Dec 10, 2012 at 16:11
Sorry Matt - could you elaborate? Presumably this would change the: "Open PathName & "\Test.csv" For Output Lock Write As #OutputFileNum" line. What to? — GERALD DUNN
– GERALD DUNN, Commented Dec 10, 2012 at 16:29
@GERALDDUNN Please see my answer, you would no longer need to use the file handles. — Matt Donnan
– Matt Donnan, Commented Dec 11, 2012 at 9:49

coge.soft · Accepted Answer · 2012-12-10 18:15:10Z

1

Import or link to the .csv so that you have the recordset in your Access 2007 databases.
Write a query with NewColumn[x] as an alias for OldColumn[x].
Write vba code to use TransferText functionality or make a macro to do the same to export your query as a .csv file (overwriting the original csv if you want/need).

Obviously, there are plenty of bonus things you could do to automate and reproduce this concept for any number or types of files. But the above solution should work in an all MS Access environment.

Let me know if you would like details on any of these steps.

answered Dec 10, 2012 at 18:15

coge.soft

1,67417 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Donnan · Accepted Answer · 2012-12-11 09:48:55Z

0

Further to my earlier comment, please see the method which uses the Excel reference:

Public Sub EditCsv()

Dim xlApp As Object
dim xlWbk As Object
Dim xlWst As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open ".../Test.csv" 'Amend this to your needs
    Set xlWst = xlWbk.Sheets(1)

    'This assumes the columns are at the beginning of the file
    xlWst.Range("A1") = "My New Column Name"
    xlWst.Range("B1") = "My New Second Column Name"

    xlWbk.Close -1 'Close and save the file here
    xlApp.Quit

    Set xlApp = Nothing
    Set xlWbk = Nothing
    Set xlWst = Nothing

End Sub

answered Dec 11, 2012 at 9:48

Matt Donnan

5,0233 gold badges22 silver badges32 bronze badges

Collectives™ on Stack Overflow

overwrite a csv file using vba

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related