chiguy99
02-16-2012, 09:14 AM
Hi all, I am in need of some help. I have a folder with a bunch of csv files. What I need is a macro that will open each csv look though a list of column names that I provide and if the column is not there insert the name of the missing one and insert "0" all the way to the bottom of the document. It needs to do this for all of the files in the folder.
Attached is an example, so in the file we have some columns labeled: time stamp, a, b, c, d.
And what I needed is columns: time stamp, a, b, c, d, e. The script will see that there is no column E, so it will add the title at the top and fill it all with 0 till end end of the data.
I had some help from someone but the script does not work, What I mean is that it does insert the columns but it will insert them even if the columns are already there. So it will double up on the columns. Do you know what the problem could be? Some of my my column names have spaces you think that could be it?? It also seems to be stuck in the loop it never stops.
example code
Sub InsertMissingColums()
Dim C As Long
Dim ColumnHeaders As Variant
Dim Filename As String
Dim Filepath As String
Dim R As Long
Dim Rng As Range
Dim Wkb As Workbook
ColumnHeaders = Array("time_stamp", "a", "b", "c", "d", "e")
Filepath = "C:\Documents and Settings\Desktop\csvimport\"
Filename = Dir(Filepath & "*.csv")
Do While Filename <> ""
Set Wkb = Workbooks.Open(Filepath & Filename)
Set Rng = ActiveSheet.UsedRange.Rows(1)
R = ActiveSheet.UsedRange.Rows.Count
For C = 0 To UBound(ColumnHeaders)
If Rng.Cells(1, C + 1) <> ColumnHeaders(C) Then
Rng.Cells(1, C + 1).EntireColumn.Insert
Rng.Cells(1, C + 1) = ColumnHeaders(C)
Rng.Cells(1, C + 1).Offset(1, 0).Resize(R - 1, 1).Value = 0
End If
Next C
Wkb.Close SaveChanges:=True
Loop
End Sub
Attached is an example, so in the file we have some columns labeled: time stamp, a, b, c, d.
And what I needed is columns: time stamp, a, b, c, d, e. The script will see that there is no column E, so it will add the title at the top and fill it all with 0 till end end of the data.
I had some help from someone but the script does not work, What I mean is that it does insert the columns but it will insert them even if the columns are already there. So it will double up on the columns. Do you know what the problem could be? Some of my my column names have spaces you think that could be it?? It also seems to be stuck in the loop it never stops.
example code
Sub InsertMissingColums()
Dim C As Long
Dim ColumnHeaders As Variant
Dim Filename As String
Dim Filepath As String
Dim R As Long
Dim Rng As Range
Dim Wkb As Workbook
ColumnHeaders = Array("time_stamp", "a", "b", "c", "d", "e")
Filepath = "C:\Documents and Settings\Desktop\csvimport\"
Filename = Dir(Filepath & "*.csv")
Do While Filename <> ""
Set Wkb = Workbooks.Open(Filepath & Filename)
Set Rng = ActiveSheet.UsedRange.Rows(1)
R = ActiveSheet.UsedRange.Rows.Count
For C = 0 To UBound(ColumnHeaders)
If Rng.Cells(1, C + 1) <> ColumnHeaders(C) Then
Rng.Cells(1, C + 1).EntireColumn.Insert
Rng.Cells(1, C + 1) = ColumnHeaders(C)
Rng.Cells(1, C + 1).Offset(1, 0).Resize(R - 1, 1).Value = 0
End If
Next C
Wkb.Close SaveChanges:=True
Loop
End Sub