Format CSV
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,2 0060317 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,2 0060317,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,, 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. |
Format CSV
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 "Martin" wrote in message ... 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,2 0060317 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,2 0060317,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,, 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. |
Format CSV
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,2 0060317 3,testbankcode1,,nyc,testbankaccount1,usd,50001.00 ,20060317 99,13,100001.00,,,,, Thanks for your help! "EvolBob" wrote in message ... 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 "Martin" wrote in message ... 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,2 0060317 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,2 0060317,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,, 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. |
All times are GMT +1. The time now is 04:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com