I have a macro that saves the template as an xls. file based upon cell
contents. The location is also based upon another cell contents. The macro
works correctly....once. After that, the template utilizes the last
location|filename that the macro used as the current location and filename in
the macro button.
The macro looks like this:
sPath = Range("B8").Value
If Right(sPath, 1) < "\" Then sPath = sPath & "\"
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B9").Value
I am attempting to reset the macro prior to it running by using the "assign
macro" and "macro name" on the toolbar(macro button). I think I need to use
"MsoCommandBarPopup." command to accomplish this, but I can't get it to
work!( or maybe find a way to keep this from happening!) I am new to visual
basic and learning fast..... maybe not fast enough!
Thanks for your help.
Tim

You are heading down the more difficult path to try to change the code
executed by the button. You are far better off to determine if the file
already exists. Check out the Dir function. You want something like this

if len(dir(Path and File )) 0 then 'The file already exists
activeworkbook.save 'so just save
else
activeworkbook.saveas Path and File
end if

HTH

"Tim Richards" wrote:

I have a macro that saves the template as an xls. file based upon cell
contents. The location is also based upon another cell contents. The macro
works correctly....once. After that, the template utilizes the last
location|filename that the macro used as the current location and filename in
the macro button.
The macro looks like this:
sPath = Range("B8").Value
If Right(sPath, 1) < "\" Then sPath = sPath & "\"
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B9").Value
I am attempting to reset the macro prior to it running by using the "assign
macro" and "macro name" on the toolbar(macro button). I think I need to use
"MsoCommandBarPopup." command to accomplish this, but I can't get it to
work!( or maybe find a way to keep this from happening!) I am new to visual
basic and learning fast..... maybe not fast enough!
Thanks for your help.
Tim

Jim,
Thank you for your prompt reply.
I agree with your thought process, I had not considered that approach.
I inserted the code you sent me, and it balks at the "If len" and the "end
if" lines.
Due to the limitations of this text editor I will insert all the code as
written in the macro.I am going to insert numerals to denote each line
properly.

01.)Sub SaveRepairCenterReport()
02.)'
03.)' SaveRepairCenterReport Macro
04.)' Macro recorded 4/15/2005 by Tim Richards
05.)'
06.)If Len(Dir(Path And File)) 0 Then
07.) ActiveWorkbook.Save
08.) ActiveWorkbook.SaveAs Path And File
09.)End If
10.)sPath = Range("B8").Value
11.) If Right(sPath, 1) < "\" Then sPath = sPath & "\"
12.) ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B9").Value
13.)'
14.)End Sub

In your reply I interpeted the"(Dir(Path And File))" to be the (Dir(Path And
File)) that the macro button is re-inserting, and therefore I am not actually
denoting (Dir(Path And File)) criteria. In addition, as noted previously I
learning VB as fast as possible and would like your recommendation on a
book(or set) that will serve me well.
Thanks again for all your input.
Tim

"Jim Thomlinson" wrote:

You are heading down the more difficult path to try to change the code
executed by the button. You are far better off to determine if the file
already exists. Check out the Dir function. You want something like this

if len(dir(Path and File )) 0 then 'The file already exists
activeworkbook.save 'so just save
else
activeworkbook.saveas Path and File
end if

HTH

"Tim Richards" wrote:

I have a macro that saves the template as an xls. file based upon cell
contents. The location is also based upon another cell contents. The macro
works correctly....once. After that, the template utilizes the last
location|filename that the macro used as the current location and filename in
the macro button.
The macro looks like this:
sPath = Range("B8").Value
If Right(sPath, 1) < "\" Then sPath = sPath & "\"
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B9").Value
I am attempting to reset the macro prior to it running by using the "assign
macro" and "macro name" on the toolbar(macro button). I think I need to use
"MsoCommandBarPopup." command to accomplish this, but I can't get it to
work!( or maybe find a way to keep this from happening!) I am new to visual
basic and learning fast..... maybe not fast enough!
Thanks for your help.
Tim