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/Download.../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..

--
lucid
-----------------------------------------------------------------------
lucidr's Profile: http://www.excelforum.com/member.php...fo&userid=3669
View this thread: http://www.excelforum.com/showthread.php?threadid=56433

what about 'savecopyas'.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=564338

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

Great; thank you

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

--
lucid
-----------------------------------------------------------------------
lucidr's Profile: http://www.excelforum.com/member.php...fo&userid=3669
View this thread: http://www.excelforum.com/showthread.php?threadid=56433

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

Dave Peterson Wrote:
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.

Dave Peterson Wrote:
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 ;-)

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

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