Macro to save contents of a named range to a file
 

In Microsoft Excel 2007, is it possible to create a macro that saves
the contents of a named range to a tab delimited text file ?

For example, the DATA name refers to A1:C2 and contains the following:

A B C
1 Item Qty Cost
2 Apple 1 1

The text file DATA.TXT would contain:

Item Qty Cost
Apple 1 1


Any ideas ?



- Ronald K.

Macro to save contents of a named range to a file
 

Yes, it is possible.

Turn on the macro recorder and copy the range(by name) to a new workbook then
save as tab delimited *.txt file.


Gord Dibben MS Excel MVP

On Sat, 30 Apr 2011 23:15:58 -0700 (PDT), kittronald
wrote:

In Microsoft Excel 2007, is it possible to create a macro that saves
the contents of a named range to a tab delimited text file ?

For example, the DATA name refers to A1:C2 and contains the following:

A B C
1 Item Qty Cost
2 Apple 1 1

The text file DATA.TXT would contain:

Item Qty Cost
Apple 1 1


Any ideas ?



- Ronald K.

Macro to save contents of a named range to a file
 

Gord,

Thanks for the quick reply.

How do you copy a name ?

When I copy a named range, the macro records the cell addresses and
not the name.



- Ronald K.

Macro to save contents of a named range to a file
 

Gord,

Figured it out - partly.

Had to use the Goto command to specify the name.

However, when the filename to save to already exists, how do you
specify "Yes" to the overwrite prompt in the macro ?



- Ronald K.

Macro to save contents of a named range to a file
 

kittronald formulated the question :
Gord,

Figured it out - partly.

Had to use the Goto command to specify the name.

However, when the filename to save to already exists, how do you
specify "Yes" to the overwrite prompt in the macro ?



- Ronald K.

Post the code so we can see what's going on.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Macro to save contents of a named range to a file
 

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="C:\myfolder\" & _
ActiveWorkbook.Name
Application.DisplayAlerts = True


Gord

On Sun, 1 May 2011 19:02:56 -0700 (PDT), kittronald
wrote:

Gord,

Figured it out - partly.

Had to use the Goto command to specify the name.

However, when the filename to save to already exists, how do you
specify "Yes" to the overwrite prompt in the macro ?



- Ronald K.

Macro to save contents of a named range to a file
 

Gord,
It's interesting how the way a Q is asked makes/breaks how we answer
it. Your reply hits the bullseye (now that I see it) but didn't even
think that because the post implied a prompt being responded to, and so
I thought maybe the OP was looking for a way to determine if the answer
to the prompt was vbYes. IOW, there was nothing to suggest looking for
a way to supress the overwrite alert.

So who's having a 'bad day' now?<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Macro to save contents of a named range to a file
 

Gord and Garry,

Initially, if the file I was writing to already existed, the macro
would cause the following dialog message prompt to appear:

"A filename 'C:\Temp\TEST.txt already exists in this location.
Do you want to replace it ?"

After inserting the "Application.DisplayAlerts = False" line, I've
verified the macro successfully overwrites the existing file -
effectively dismissing the overwrite prompt.

Here is the code:

Sub Macro_SaveAs_File()
'
' Macro_SaveAs_File Macro
'

'
Application.Goto Reference:="DATA"
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Temp\TEST.txt",
FileFormat:=xlText, _
CreateBackup:=False
Application.DisplayAlerts = True
End Sub

Thanks for the help Gord.



- Ronald K.