Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
I have an excel file that I always save as a text file for importing into
another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
If you save the file as a normal workbook, then the recorded macro will be saved
with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
Hi debbie
To keep it as simple as I possibly can: record an empty macro (just start recroding then stop) got to macro then "step into" macro and copy and paste following code into it: ActiveWorkbook.SaveAs _ FileFormat:=xlText good luck john "DebbieSue" wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
Debbie Sue,
If you need to save the changes to both the text file and the Excel file, then try Sub SaveFileToTextFile() Application.DisplayAlerts = False ThisWorkbook.Save ThisWorkbook.SaveAs _ FileFormat:=xlText ThisWorkbook.SaveAs _ FileFormat:=xlNormal Application.DisplayAlerts = False End Sub HTH, Bernie MS Excel MVP "DebbieSue" wrote in message ... I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
And you're re-opening up the .xls* file--not the text file, right???
If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
I tried the first two suggestions and it still didn't work. I'm sure I'm
doing something wrong, but I don't know what it is. I'm running excel 2003. I don 't know if that makes a difference. Details: After I open my file I go to Record Macro, name it and then I have to delete a line in my spreadsheet. After that I just go to Save As and click Text and save it to "This workbook". I have to save it to a file on my desktop. End of story. I looked for it and it's not there. I did go back into the file and did a Save As but I wasn't really sure where I should be saving it to. I don't have a Type called Normal Workbook, just MS Excel Worksheet. If that makes a difference. "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
I got it!!!!! I wasn't clicking Step into, I was clicking Edit. I knew it was
me. Thanks for all the help!! "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
ok. Can you tell me if I can save this Macro to work in ANY excel file I have
open? "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
Glad you got it working.
If you have the code that you like finished, you can: Start a new workbook Copy that code into a new module in that new workbook's project. Then save that new workbook with a nice name: DebbieSueUtils.xls Then open this file whenever you need to run the macro (or any of the macros that you create in the future!). You'll be able to activate your data workbook/worksheet and run your macro (tools|macros|macro or alt-f8). (You could even use a shortcut key (if you have a good memory!).) Personally, I like a nicer interface--maybe a new item on the Worksheet Menu bar--or a separate toolbar. If you want to go this route, you could save that DebbieSueUtil workbook as an addin (it's an option in the file|saveAs dialog). For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm DebbieSue wrote: ok. Can you tell me if I can save this Macro to work in ANY excel file I have open? "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
ps. If you don't have the macro saved, you can:
Start a new workbook Activate your data workbook/worksheet Record the macro--but save the macro in the new workbook--not the workbook with the data. Then save this new workbook and the process is the same. Dave Peterson wrote: Glad you got it working. If you have the code that you like finished, you can: Start a new workbook Copy that code into a new module in that new workbook's project. Then save that new workbook with a nice name: DebbieSueUtils.xls Then open this file whenever you need to run the macro (or any of the macros that you create in the future!). You'll be able to activate your data workbook/worksheet and run your macro (tools|macros|macro or alt-f8). (You could even use a shortcut key (if you have a good memory!).) Personally, I like a nicer interface--maybe a new item on the Worksheet Menu bar--or a separate toolbar. If you want to go this route, you could save that DebbieSueUtil workbook as an addin (it's an option in the file|saveAs dialog). For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm DebbieSue wrote: ok. Can you tell me if I can save this Macro to work in ANY excel file I have open? "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
I created the other workbook and I keep it open and go to the actual workbook
I want to use the macro in. It is listed under Macros, but when I run it, it doesn't save where I want it to. Actually, I'm not sure where it is saving it to. "Dave Peterson" wrote: Glad you got it working. If you have the code that you like finished, you can: Start a new workbook Copy that code into a new module in that new workbook's project. Then save that new workbook with a nice name: DebbieSueUtils.xls Then open this file whenever you need to run the macro (or any of the macros that you create in the future!). You'll be able to activate your data workbook/worksheet and run your macro (tools|macros|macro or alt-f8). (You could even use a shortcut key (if you have a good memory!).) Personally, I like a nicer interface--maybe a new item on the Worksheet Menu bar--or a separate toolbar. If you want to go this route, you could save that DebbieSueUtil workbook as an addin (it's an option in the file|saveAs dialog). For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm DebbieSue wrote: ok. Can you tell me if I can save this Macro to work in ANY excel file I have open? "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
It's time to share your recorded macro and the details of where you want it
saved (and how the name should be created???). DebbieSue wrote: I created the other workbook and I keep it open and go to the actual workbook I want to use the macro in. It is listed under Macros, but when I run it, it doesn't save where I want it to. Actually, I'm not sure where it is saving it to. "Dave Peterson" wrote: Glad you got it working. If you have the code that you like finished, you can: Start a new workbook Copy that code into a new module in that new workbook's project. Then save that new workbook with a nice name: DebbieSueUtils.xls Then open this file whenever you need to run the macro (or any of the macros that you create in the future!). You'll be able to activate your data workbook/worksheet and run your macro (tools|macros|macro or alt-f8). (You could even use a shortcut key (if you have a good memory!).) Personally, I like a nicer interface--maybe a new item on the Worksheet Menu bar--or a separate toolbar. If you want to go this route, you could save that DebbieSueUtil workbook as an addin (it's an option in the file|saveAs dialog). For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm DebbieSue wrote: ok. Can you tell me if I can save this Macro to work in ANY excel file I have open? "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
Here is the Macro: I want it to save to a specific folder on my desktop, but
it is saving to My Documents. Sub OtherTextFiles() ' ' OtherTextFiles Macro ' Macro recorded 9/3/2009 by Debbie Powers ' ' Keyboard Shortcut: Ctrl+o ActiveWorkbook.SaveAs _ FileFormat:=xlText End Sub "Dave Peterson" wrote: It's time to share your recorded macro and the details of where you want it saved (and how the name should be created???). DebbieSue wrote: I created the other workbook and I keep it open and go to the actual workbook I want to use the macro in. It is listed under Macros, but when I run it, it doesn't save where I want it to. Actually, I'm not sure where it is saving it to. "Dave Peterson" wrote: Glad you got it working. If you have the code that you like finished, you can: Start a new workbook Copy that code into a new module in that new workbook's project. Then save that new workbook with a nice name: DebbieSueUtils.xls Then open this file whenever you need to run the macro (or any of the macros that you create in the future!). You'll be able to activate your data workbook/worksheet and run your macro (tools|macros|macro or alt-f8). (You could even use a shortcut key (if you have a good memory!).) Personally, I like a nicer interface--maybe a new item on the Worksheet Menu bar--or a separate toolbar. If you want to go this route, you could save that DebbieSueUtil workbook as an addin (it's an option in the file|saveAs dialog). For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm DebbieSue wrote: ok. Can you tell me if I can save this Macro to work in ANY excel file I have open? "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
This copies the activesheet to a new workbook. Then you can save that as text
and not touch the existing workbook. Since you didn't say how the filename should be determined, I'm always writing to the desktop\somefolder\myfilenamehere.txt file. The displayalerts = false says to not bother you with the "do you want to overwrite the file" messages. Option Explicit Sub OtherTextFiles() 'Keyboard Shortcut: Ctrl+o Dim myPath As String Dim wks As Worksheet 'so you don't have to hardcode the path to desktop myPath = CreateObject("WScript.Shell").SpecialFolders("Desk Top") Set wks = ActiveSheet wks.Copy 'copy to a new workbook, so original isn't touched. With ActiveSheet.Parent Application.DisplayAlerts = False .SaveAs Filename:=myPath & "\somefolder\" & "myfilenamehere.txt", _ FileFormat:=xlText Application.DisplayAlerts = True .Close savechanges:=False 'close this new workbook without saving End With End Sub And when you're verifying the output, open the .txt file in Notepad. DebbieSue wrote: Here is the Macro: I want it to save to a specific folder on my desktop, but it is saving to My Documents. Sub OtherTextFiles() ' ' OtherTextFiles Macro ' Macro recorded 9/3/2009 by Debbie Powers ' ' Keyboard Shortcut: Ctrl+o ActiveWorkbook.SaveAs _ FileFormat:=xlText End Sub "Dave Peterson" wrote: It's time to share your recorded macro and the details of where you want it saved (and how the name should be created???). DebbieSue wrote: I created the other workbook and I keep it open and go to the actual workbook I want to use the macro in. It is listed under Macros, but when I run it, it doesn't save where I want it to. Actually, I'm not sure where it is saving it to. "Dave Peterson" wrote: Glad you got it working. If you have the code that you like finished, you can: Start a new workbook Copy that code into a new module in that new workbook's project. Then save that new workbook with a nice name: DebbieSueUtils.xls Then open this file whenever you need to run the macro (or any of the macros that you create in the future!). You'll be able to activate your data workbook/worksheet and run your macro (tools|macros|macro or alt-f8). (You could even use a shortcut key (if you have a good memory!).) Personally, I like a nicer interface--maybe a new item on the Worksheet Menu bar--or a separate toolbar. If you want to go this route, you could save that DebbieSueUtil workbook as an addin (it's an option in the file|saveAs dialog). For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm DebbieSue wrote: ok. Can you tell me if I can save this Macro to work in ANY excel file I have open? "Dave Peterson" wrote: And you're re-opening up the .xls* file--not the text file, right??? If you're using xl2007, make sure you save the workbook as a macro enabled workbook (*.xlsm). If you're not using xl2007, you may want to share the exact details of what you're doing. DebbieSue wrote: I tried what you said and it still didn't save it. "Dave Peterson" wrote: If you save the file as a normal workbook, then the recorded macro will be saved with that workbook. So record your macro when you save it as a text file. Stop recording. Save the file once more as a normal workbook. DebbieSue wrote: I have an excel file that I always save as a text file for importing into another program. I was wondering if a Macro could be made to do this? I have tried to record the Macro as I do it, but it didn't even save it. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I NEED A MACRO!!!!
You could even go a step further and paste your code into a "before save"
event so that it automatically saves as a text file whenever the workbook is saved in the normal fashion: alt f11 to open the editor, double click "this workbook", select "workbook" in the left drop down box and "before save" in the right drop down box, and paste code there. Be brave give it a go! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |