Programmatic export to CSV

[lucidr]

Hi All

I am trying to write some VBA that will export one sheet from m
workbook as a CVF file. I can do this using:

ActiveWorkbook.SaveAs myFileName, xlCSV

...but this leaves me with the open document pointed at the new CS
file on disk. In an attempt to work around this, I tried storing th
original path first and saving back to that location as a standar
Excel workbook. This works, except the sheet I exported to CSV is no
renamed (great 'feature'), thus breaking my macro!

Does anyone know of a cleaner way to do this (short of writing my ow
CSV generator, which seems a little excessive)? The existence of th
'Excel 2000 Programmatic Text Export Update
(http://office.microsoft.com/Downloads/9798/xl8p8pkg.aspx) on the M
Office site would seem to suggest that there might be a sensibl
programmatic interface to the export filters somewhere, but I can'
find it..

 

[MattShoreson]

what about 'savecopyas'.

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

set wks = worksheets("whateveroneyouwanthere")

wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks
Application.DisplayAlerts = False
.Parent.SaveAs Filename:="C:\TEMP\" & .Name & ".txt", _
FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With

End Sub

 

[lucidr]

Great; thank you

Just have to figure out how to deal with Excel's randomly appende
trailing commas in the CSV now..

 

[Dave Peterson]

Not so random...

http://support.microsoft.com/default.aspx?scid=77295
Column Delimiters Missing in Spreadsheet Saved as Text

(It actually describes missing delimiter, but if some are "missing", maybe the
ones appearing are "extra".)

But a lot of programs (excel included) don't care about those extra columns.
Maybe you don't have to care, either???

Maybe you could write your own exporting program that would behave exactly the
way you want:

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(or maybe you could build your own formula and copy|paste that single column
into Notepad--and save from there.)

 

[lucidr]

Not so random...

http://support.microsoft.com/default.aspx?scid=77295
Column Delimiters Missing in Spreadsheet Saved as Text

(It actually describes missing delimiter, but if some are "missing",
maybe the
ones appearing are "extra".)

Ahh ...I was (slowly) coming to a 'similar conclusion'
(http://www.excelforum.com/showthread.php?t=564635). The length of the
header determines the extra columns in the first few rows.

Maybe you could write your own exporting program that would behave
exactly the
way you want

Cheers ...this is the Babylon 5 ('last, best hope') option ;-)

(or maybe you could build your own formula and copy|paste that single
column
into Notepad--and save from there.)

This is my favourite quick and easy method for personal use.

 

[Dave Peterson]

And if you save the formula somewhere, you won't have to rebuild it each time!