Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Format CSV
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default 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.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default 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.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default 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.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lock Cell Format - Allow copy and paste of data without format change Chris12InKC Excel Worksheet Functions 2 May 9th 23 07:42 PM
Need help with converting CUSTOM format/TEXT format to DATE format Deo Cleto Excel Worksheet Functions 6 June 2nd 09 08:14 PM
Replace million-billion number format to lakhs-crores format Sumit Excel Discussion (Misc queries) 1 December 9th 05 04:58 PM
how to format excel format to text format with separator "|" in s. azlan New Users to Excel 1 January 31st 05 12:57 PM
Keep format after paste from other worksheets - conditional format or EnableControl solution doesn't work No Name Excel Programming 0 May 3rd 04 12:22 PM


All times are GMT +1. The time now is 08:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement