Programmatic export to CSV

[lucidr]

Hi All

I am trying to write some VBA that will export one sheet from my 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 CSV file on disk. In an attempt to work around this, I tried storing the original path first and saving back to that location as a standard Excel workbook. This works, except the sheet I exported to CSV is now renamed (great 'feature'), thus breaking my macro!

Does anyone know of a cleaner way to do this (short of writing my own CSV generator, which seems a little excessive)? The existence of the Excel 2000 Programmatic Text Export Update on the MS Office site would seem to suggest that there might be a sensible programmatic interface to the export filters somewhere, but I can't 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 wrote:
>
> Hi All
>
> I am trying to write some VBA that will export one sheet from my
> 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 CSV
> file on disk. In an attempt to work around this, I tried storing the
> original path first and saving back to that location as a standard
> Excel workbook. This works, except the sheet I exported to CSV is now
> renamed (great 'feature'), thus breaking my macro!
>
> Does anyone know of a cleaner way to do this (short of writing my own
> CSV generator, which seems a little excessive)? The existence of the
> 'Excel 2000 Programmatic Text Export Update'
> (http://office.microsoft.com/Download.../xl8p8pkg.aspx) on the MS
> Office site would seem to suggest that there might be a sensible
> programmatic interface to the export filters somewhere, but I can't
> find it...
>
> --
> lucidr
> ------------------------------------------------------------------------
> lucidr's Profile: http://www.excelforum.com/member.php...o&userid=36699
> View this thread: http://www.excelforum.com/showthread...hreadid=564338

--

Dave Peterson

[lucidr]

Great; thank you.

Just have to figure out how to deal with Excel's randomly appended 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 wrote:
>
> Great; thank you.
>
> Just have to figure out how to deal with Excel's randomly appended
> trailing commas in the CSV now...
>
> --
> lucidr
> ------------------------------------------------------------------------
> lucidr's Profile: http://www.excelforum.com/member.php...o&userid=36699
> View this thread: http://www.excelforum.com/showthread...hreadid=564338

--

Dave Peterson

[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. 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!

lucidr wrote:
>
<<snipped>>
> Dave Peterson Wrote:
> > (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.
>
> --
> lucidr
> ------------------------------------------------------------------------
> lucidr's Profile: http://www.excelforum.com/member.php...o&userid=36699
> View this thread: http://www.excelforum.com/showthread...hreadid=564338

--

Dave Peterson