Format CSV

[Martin]

I am exporting a worksheet into CSV and need some help formatting the
results. Here is an example of what my CSV looks like in text format. The
second record needs 39 "empty" comma separated placeholders and the first
and third "do not" need the extra placeholders.

This is what I get when I run my macro.

CI,20060320203300,testuser,001,,,,
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317
99,1,50000.00,,,,,

This is what I need the file to look like.

CI,20060316140834,testuser,001
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
99,1,50000.00

Code I am using.

Set rngSave = Selection
Set wkbNew = Workbooks.Add
Set wkSheet = wkbNew.ActiveSheet
wkSheet.Cells(1, 1).Value = "CI"
wkSheet.Cells(1, 2).NumberFormat = "@"
wkSheet.Cells(1, 2).Value = Format(Now,
"yyyymmddhhmmss")
wkSheet.Cells(1, 3).Value = "testuser"
wkSheet.Cells(1, 4).Value = "00000001"
i = 1
rngSave.Copy wkSheet.Range("A2")
For i = i + 1 To rwCount + 1
wkSheet.Cells(i, 9).Value = ""
wkSheet.Cells(i, 10).Value = ""
wkSheet.Cells(i, 11).Value = ""
wkSheet.Cells(i, 12).Value = ""
wkSheet.Cells(i, 13).Value = ""
wkSheet.Cells(i, 14).Value = ""
wkSheet.Cells(i, 15).Value = ""
wkSheet.Cells(i, 16).Value = ""
wkSheet.Cells(i, 17).Value = ""
wkSheet.Cells(i, 18).Value = ""
wkSheet.Cells(i, 19).Value = ""
wkSheet.Cells(i, 20).Value = ""
wkSheet.Cells(i, 21).Value = ""
wkSheet.Cells(i, 22).Value = ""
wkSheet.Cells(i, 23).Value = ""
wkSheet.Cells(i, 24).Value = ""
wkSheet.Cells(i, 25).Value = ""
wkSheet.Cells(i, 26).Value = ""
wkSheet.Cells(i, 27).Value = ""
wkSheet.Cells(i, 28).Value = ""
wkSheet.Cells(i, 29).Value = ""
wkSheet.Cells(i, 30).Value = ""
wkSheet.Cells(i, 31).Value = ""
wkSheet.Cells(i, 32).Value = ""
wkSheet.Cells(i, 33).Value = ""
wkSheet.Cells(i, 34).Value = ""
wkSheet.Cells(i, 35).Value = ""
wkSheet.Cells(i, 36).Value = ""
wkSheet.Cells(i, 37).Value = ""
wkSheet.Cells(i, 38).Value = ""
wkSheet.Cells(i, 39).Value = ""
wkSheet.Cells(i, 40).Value = ""
wkSheet.Cells(i, 41).Value = ""
wkSheet.Cells(i, 42).Value = ""
wkSheet.Cells(i, 43).Value = ""
wkSheet.Cells(i, 44).Value = ""
wkSheet.Cells(i, 45).Value = ""
wkSheet.Cells(i, 46).Value = ""
wkSheet.Cells(i, 47).Value = ""
Next i
wkSheet.Cells(r, 1).Value = "99"
wkSheet.Cells(r, 2).Value = rwCount
wkSheet.Cells(r, 3).Formula = "=sum(" & rng.Address(1,
1, xlA1, True) & ")"
Application.DisplayAlerts = False
wkbNew.SaveAs Filename:="c:\" & wkbName & ".csv",
FileFormat:=xlCSV
wkbNew.Close

Any help would be greatly appreciated.

 

[EvolBob]

Hi Martin.
I'm not about to test your code as a number of your variables are not initiated or set, ...anyway I think I can shorten the middle bit a tad. You didn't say what r, rng and RWcount are for.

with wksheet.Range("A1")
.resize(1,4).value = Array("C1", [Text(Now(),"yyyymmddhhmmss")],"testuser","00000001")
rngSave.Copy .Offset(1,0)
.offset(1,8).Resize(RWcount,39) = ""
.offset(r,1).resize(1,3).formula = Array("99",RWcount,"=sum(" & rng.Address(1,1, xlA1, True) & ")"
end with


Regards
Robert McCurdy

I am exporting a worksheet into CSV and need some help formatting the
results. Here is an example of what my CSV looks like in text format. The
second record needs 39 "empty" comma separated placeholders and the first
and third "do not" need the extra placeholders.

This is what I get when I run my macro.

CI,20060320203300,testuser,001,,,,
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317
99,1,50000.00,,,,,

This is what I need the file to look like.

CI,20060316140834,testuser,001
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
99,1,50000.00

Code I am using.

Set rngSave = Selection
Set wkbNew = Workbooks.Add
Set wkSheet = wkbNew.ActiveSheet
wkSheet.Cells(1, 1).Value = "CI"
wkSheet.Cells(1, 2).NumberFormat = "@"
wkSheet.Cells(1, 2).Value = Format(Now,
"yyyymmddhhmmss")
wkSheet.Cells(1, 3).Value = "testuser"
wkSheet.Cells(1, 4).Value = "00000001"
i = 1
rngSave.Copy wkSheet.Range("A2")
For i = i + 1 To rwCount + 1
wkSheet.Cells(i, 9).Value = ""
wkSheet.Cells(i, 10).Value = ""
wkSheet.Cells(i, 11).Value = ""
wkSheet.Cells(i, 12).Value = ""
wkSheet.Cells(i, 13).Value = ""
wkSheet.Cells(i, 14).Value = ""
wkSheet.Cells(i, 15).Value = ""
wkSheet.Cells(i, 16).Value = ""
wkSheet.Cells(i, 17).Value = ""
wkSheet.Cells(i, 18).Value = ""
wkSheet.Cells(i, 19).Value = ""
wkSheet.Cells(i, 20).Value = ""
wkSheet.Cells(i, 21).Value = ""
wkSheet.Cells(i, 22).Value = ""
wkSheet.Cells(i, 23).Value = ""
wkSheet.Cells(i, 24).Value = ""
wkSheet.Cells(i, 25).Value = ""
wkSheet.Cells(i, 26).Value = ""
wkSheet.Cells(i, 27).Value = ""
wkSheet.Cells(i, 28).Value = ""
wkSheet.Cells(i, 29).Value = ""
wkSheet.Cells(i, 30).Value = ""
wkSheet.Cells(i, 31).Value = ""
wkSheet.Cells(i, 32).Value = ""
wkSheet.Cells(i, 33).Value = ""
wkSheet.Cells(i, 34).Value = ""
wkSheet.Cells(i, 35).Value = ""
wkSheet.Cells(i, 36).Value = ""
wkSheet.Cells(i, 37).Value = ""
wkSheet.Cells(i, 38).Value = ""
wkSheet.Cells(i, 39).Value = ""
wkSheet.Cells(i, 40).Value = ""
wkSheet.Cells(i, 41).Value = ""
wkSheet.Cells(i, 42).Value = ""
wkSheet.Cells(i, 43).Value = ""
wkSheet.Cells(i, 44).Value = ""
wkSheet.Cells(i, 45).Value = ""
wkSheet.Cells(i, 46).Value = ""
wkSheet.Cells(i, 47).Value = ""
Next i
wkSheet.Cells(r, 1).Value = "99"
wkSheet.Cells(r, 2).Value = rwCount
wkSheet.Cells(r, 3).Formula = "=sum(" & rng.Address(1,
1, xlA1, True) & ")"
Application.DisplayAlerts = False
wkbNew.SaveAs Filename:="c:\" & wkbName & ".csv",
FileFormat:=xlCSV
wkbNew.Close

Any help would be greatly appreciated.

 

[Martin]

Robert,

Thanks for shortening my code. I guess I accidentally left some stuff out.
Here is the complete code.

Dim rng As Range
With Worksheets(ActiveSheet.Name)
Set rng = .Range("G2", .Cells(Rows.Count,
"G").End(xlUp))
End With
wkbName = Format(Now, "mmmm_dd_yyyy_hh_mm_ss") & "_" &
ActiveSheet.Name
RWcount = Selection.Rows.Count
r = RWcount + 2
Set rngSave = Selection
Set wkbNew = Workbooks.Add
Set wksheet = wkbNew.ActiveSheet
With wksheet.Range("A1")
.Resize(1, 4).Value = Array("C1",
[Text(Now(),"yyyymmddhhmmss")], "testuser", "00000001")
rngSave.Copy .Offset(1, 0)
.Offset(1, 8).Resize(RWcount, 39) = ""
.Cells(r, 1).Value = "99"
.Cells(r, 2).Value = RWcount
.Cells(r, 3).Formula = "=sum(" & rng.Address(1, 1, xlA1,
True) & ")"
End With
Application.DisplayAlerts = False
wkbNew.SaveAs Filename:="c:\" & wkbName & ".csv",
FileFormat:=xlCSV
wkbNew.Close

Unfortunately I still cant figure out how to remove the extra commas from
the header and trailer records. Also, I need to append about 39 commas to
each range in the selection. When I save the CSV file as .txt I see this.
Should I try manipulating the file via FSO?

CI,20060320203300,testuser,1,,,,
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317
3,testbankcode1,,nyc,testbankaccount1,usd,50001.00,20060317
99,13,100001.00,,,,,

Thanks for your help!

Hi Martin.
I'm not about to test your code as a number of your variables are not
initiated or set, ...anyway I think I can shorten the middle bit a tad. You
didn't say what r, rng and RWcount are for.

with wksheet.Range("A1")
.resize(1,4).value = Array("C1",
[Text(Now(),"yyyymmddhhmmss")],"testuser","00000001")
rngSave.Copy .Offset(1,0)
.offset(1,8).Resize(RWcount,39) = ""
.offset(r,1).resize(1,3).formula = Array("99",RWcount,"=sum(" &
rng.Address(1,1, xlA1, True) & ")"
end with


Regards
Robert McCurdy

I am exporting a worksheet into CSV and need some help formatting the
results. Here is an example of what my CSV looks like in text format. The
second record needs 39 "empty" comma separated placeholders and the first
and third "do not" need the extra placeholders.

This is what I get when I run my macro.

CI,20060320203300,testuser,001,,,,
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317
99,1,50000.00,,,,,

This is what I need the file to look like.

CI,20060316140834,testuser,001
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
99,1,50000.00

Code I am using.

Set rngSave = Selection
Set wkbNew = Workbooks.Add
Set wkSheet = wkbNew.ActiveSheet
wkSheet.Cells(1, 1).Value = "CI"
wkSheet.Cells(1, 2).NumberFormat = "@"
wkSheet.Cells(1, 2).Value = Format(Now,
"yyyymmddhhmmss")
wkSheet.Cells(1, 3).Value = "testuser"
wkSheet.Cells(1, 4).Value = "00000001"
i = 1
rngSave.Copy wkSheet.Range("A2")
For i = i + 1 To rwCount + 1
wkSheet.Cells(i, 9).Value = ""
wkSheet.Cells(i, 10).Value = ""
wkSheet.Cells(i, 11).Value = ""
wkSheet.Cells(i, 12).Value = ""
wkSheet.Cells(i, 13).Value = ""
wkSheet.Cells(i, 14).Value = ""
wkSheet.Cells(i, 15).Value = ""
wkSheet.Cells(i, 16).Value = ""
wkSheet.Cells(i, 17).Value = ""
wkSheet.Cells(i, 18).Value = ""
wkSheet.Cells(i, 19).Value = ""
wkSheet.Cells(i, 20).Value = ""
wkSheet.Cells(i, 21).Value = ""
wkSheet.Cells(i, 22).Value = ""
wkSheet.Cells(i, 23).Value = ""
wkSheet.Cells(i, 24).Value = ""
wkSheet.Cells(i, 25).Value = ""
wkSheet.Cells(i, 26).Value = ""
wkSheet.Cells(i, 27).Value = ""
wkSheet.Cells(i, 28).Value = ""
wkSheet.Cells(i, 29).Value = ""
wkSheet.Cells(i, 30).Value = ""
wkSheet.Cells(i, 31).Value = ""
wkSheet.Cells(i, 32).Value = ""
wkSheet.Cells(i, 33).Value = ""
wkSheet.Cells(i, 34).Value = ""
wkSheet.Cells(i, 35).Value = ""
wkSheet.Cells(i, 36).Value = ""
wkSheet.Cells(i, 37).Value = ""
wkSheet.Cells(i, 38).Value = ""
wkSheet.Cells(i, 39).Value = ""
wkSheet.Cells(i, 40).Value = ""
wkSheet.Cells(i, 41).Value = ""
wkSheet.Cells(i, 42).Value = ""
wkSheet.Cells(i, 43).Value = ""
wkSheet.Cells(i, 44).Value = ""
wkSheet.Cells(i, 45).Value = ""
wkSheet.Cells(i, 46).Value = ""
wkSheet.Cells(i, 47).Value = ""
Next i
wkSheet.Cells(r, 1).Value = "99"
wkSheet.Cells(r, 2).Value = rwCount
wkSheet.Cells(r, 3).Formula = "=sum(" & rng.Address(1,
1, xlA1, True) & ")"
Application.DisplayAlerts = False
wkbNew.SaveAs Filename:="c:\" & wkbName & ".csv",
FileFormat:=xlCSV
wkbNew.Close

Any help would be greatly appreciated.