Macro help


R

Rita

I am somewhat familiar with macros and understand how to record one. I am
using Excel 2007. I am trying to create a macro: (file, save as, "file
name", and then have Excel finish with the "save" function), but I keep
getting the following error message. Please help! Thanks.

"the following features cannot be saved in macro-free workbooks: VB
project. To save a file with these features, click no and then choose a
macro enabled file type in the file list. To continue saving as a macro-free
workbook click yes."

I have already chosen "enable all macros" in the trust center.
 
Ad

Advertisements

R

Rita

I figured it out! Now I need to know how to name the file a different name
than the original and save it as a new one. Does anyone know the code for
this? Thanks.
 
Ad

Advertisements

J

JLatham

I think you're probably already there! The same .SaveAs function also
accepts a new filename:

ActiveWorkbook.SaveAs _
Filename:="C:\Users\JLatham\Documents\NewName.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

And you can substitute a variable for the filename, allowing you to generate
one before executing the command.

Assuming you want to put it into the same folder with the original you can
get the original path from the workbook's .FullName
filePath=Left(ThisWorkbook.FullName, _
InstrRev(ThisWorkbook.FullName,Application.PathSeparator))

and build up some new name:
NewName = "MyNewFileName_" & Year(Now()) & Month(Now()) & Day(Now()) & ".xlsm"
'put the two together
NewName = filePath & NewName

Then execute your .FileSave using NewName for the Filename:= parameter, as:
Filename:=NewName
 

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