This is a fairly easy task but I'm struggling to find the right objects to
use.

I have a named range on a spreadsheet or around 20 columns and 10 rows and I
want to write that range out to a CSV file in a specific directory with a
specific name (I want the extension to end in .txt and not .csv).

So I'm thinking that I could loop over each row and inside that loop I would
loop over each column and then write the conents of each cell to the file
and put a "," between each value.

I'm not familiar with Excel VBA syntax so any pointers would be much
appreciated.

Thanks.

Sub WriteCSV()
Dim rng as Range, bk as Workbook
set rng = Workbooks("MyBook.xls") _
.Names("Table").RefersToRange
workbooks.add
set bk = Activeworkbook

rng.copy bk.Worksheets(1).Range("A1")
Application.DisplayAlerts = False
bk.Saveas "C:\Myfolder\Myfile.csv, FileFormat:=xlCSV
Application.DisplayAlerts = True
bk.close SaveChanges:=False
On Error Resume Next
Kill "C:\Myfolder\Myfile.txt"
On Error goto 0
name "C:\Myfolder\Myfile.csv" as "C:\Myfolder\Myfile.txt"
End Sub

--
Regards,
Tom Ogilvy


"guy" wrote in message
...
This is a fairly easy task but I'm struggling to find the right objects to
use.

I have a named range on a spreadsheet or around 20 columns and 10 rows and
I
want to write that range out to a CSV file in a specific directory with a
specific name (I want the extension to end in .txt and not .csv).

So I'm thinking that I could loop over each row and inside that loop I
would
loop over each column and then write the conents of each cell to the file
and put a "," between each value.

I'm not familiar with Excel VBA syntax so any pointers would be much
appreciated.

Thanks.

Many thanks Tom. I was only expecting a couple of pointers and not the
entire sub so your help is VERY much appreciated. I think that there was a
missing " in this line:
bk.Saveas "C:\Myfolder\Myfile.csv, FileFormat:=xlCSV
but otherwise the code ran perfectly.

Thanks again for your help.

"Tom Ogilvy" wrote in message
...
Sub WriteCSV()
Dim rng as Range, bk as Workbook
set rng = Workbooks("MyBook.xls") _
.Names("Table").RefersToRange
workbooks.add
set bk = Activeworkbook

rng.copy bk.Worksheets(1).Range("A1")
Application.DisplayAlerts = False
bk.Saveas "C:\Myfolder\Myfile.csv, FileFormat:=xlCSV
Application.DisplayAlerts = True
bk.close SaveChanges:=False
On Error Resume Next
Kill "C:\Myfolder\Myfile.txt"
On Error goto 0
name "C:\Myfolder\Myfile.csv" as "C:\Myfolder\Myfile.txt"
End Sub

--
Regards,
Tom Ogilvy


"guy" wrote in message
...
This is a fairly easy task but I'm struggling to find the right objects
to
use.

I have a named range on a spreadsheet or around 20 columns and 10 rows
and
I
want to write that range out to a CSV file in a specific directory with a
specific name (I want the extension to end in .txt and not .csv).

So I'm thinking that I could loop over each row and inside that loop I
would
loop over each column and then write the conents of each cell to the file
and put a "," between each value.

I'm not familiar with Excel VBA syntax so any pointers would be much
appreciated.

Thanks.