macro is not saved in project but in a project.txt version

J

Janis

I'm getting an error message when I start to save a macro. Its confusing
because it saves the active workbook as a text file. This is scary becuase I
don't notice it and its not the project module I'm storing my work in. I
have an open blank workbook that I'm storing my macros in since my Personal
Workbook doesn't work. I run the maros from there but I have an active
worksheet open that I want to run the macros on.
----error message----
"The selected file type does not support workbooks that contain multiple
sheets.
* to save only the active sheet, click OK.
*To save all sheets, save them individuall using a different file name for
each or choose a file type that supports multiple sheets."

----macro-------

Sub saveIndesign()
'Appends date to filename so as to not write over an existing file

' saveIndesign Macro

Const fPath As String = "Mac OS X:jrough:Documents:"
Dim fName As String
Dim myFileName As String
myFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) &
Format(Now, "yyyymmdd_hhmmss") & ".txt"

fName = fPath & myFileName
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=19
MsgBox "File Saved to " & fName
End Sub

----
P.S. I just want to save this excel spreadsheet as a space delimited file
for imort to an Indesign plug-in but I keep trying to save my macro changes
in the regular project module and like I said when I save this macro it saves
the file as a texxt file okay, but then it doesn't save the work in the blank
project module only the text version of it.

thanks,
 
D

Dave Peterson

I don't get that prompt in xl2003 (wintel version).

But this may suppress the warning:

application.displayalerts = false
'do the save
application.displayalerts = true

====
But remember that that Txt file won't contain your code (or it won't on wintel,
anyway). That .Txt file is just plain old text--no formatting, no formulas, no
headers/footers, no code!

You'll have to keep that code in a different workbook--either personal.xls or
something else that you'll open when you need to run that procedure.
 
J

Janis

thanks, I wonder if it would work if I just changed the name of this workbook
to personal.xls?
Anyway i finally figured out what it was doing. It is saving the file I
have the macro in instead of the file I want to save.
 
D

Dave Peterson

Your code uses Activeworkbook.

So the file saved will depend on which workbook is active.
 

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