Macro to save contents of a named range to a file

[kittronald]

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.

 

[Gord Dibben]

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

 

[kittronald]

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.

 

[kittronald]

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.

 

[GS]

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.

 

[Gord Dibben]

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


Gord

 

[GS]

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>

 

[kittronald]

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.