Macro to save contents of a named range to a file

K

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.
 
G

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
 
K

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.
 
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.
 
G

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.
 
G

Gord Dibben

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


Gord
 
G

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>
 
K

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top