Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Macro to Open File, Delete Contents, Save New File | Excel Discussion (Misc queries) | |||
Copy & Paste Named Range Contents | Excel Programming | |||
write a macro like the /file copy combine named range in lotus 12 | Excel Programming | |||
macro to save named range as text | Excel Programming | |||
How to save file in a macro taking new name from cell contents? | Excel Discussion (Misc queries) |