I NEED A MACRO!!!!

[DebbieSue]

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]

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]

I tried what you said and it still didn't save it.

 

[Atishoo]

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

 

[Bernie Deitrick]

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

 

[Dave Peterson]

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]

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.

 

[DebbieSue]

I got it!!!!! I wasn't clicking Step into, I was clicking Edit. I knew it was
me. Thanks for all the help!!

 

[DebbieSue]

ok. Can you tell me if I can save this Macro to work in ANY excel file I have
open?

 

[Dave Peterson]

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

ok. Can you tell me if I can save this Macro to work in ANY excel file I have
open?

 

[Dave Peterson]

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.

 

[DebbieSue]

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]

It's time to share your recorded macro and the details of where you want it
saved (and how the name should be created???).

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.

 

[DebbieSue]

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]

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("DeskTop")

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.

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

 

[Atishoo]

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!