code to SAVE

[alex]

Hi experts,

using Excel '03...

My company is trying to save its xls documents (and other MS docs) in
a particular format; e.g., Program_Desc_Date_Initials.

Is there a way to code Excel (possibly placing the code in Book.xlt)
that prompts/asks the user (when the user executes SAVE or SAVE AS) to
enter the appropriate program, description, date, and his/her
initials. I'm thinking four input boxes (one after the other).

This data from the user would be concatenated and would form the file
name (now located in the Save As dialog box). The user would then
simply choose a location.

Thoughts?

alex

 

[alex]

Hi experts,

using Excel '03...

My company is trying to save its xls documents (and other MS docs) in
a particular format; e.g., Program_Desc_Date_Initials.

Is there a way to code Excel (possibly placing the code in Book.xlt)
that prompts/asks the user (when the user executes SAVE or SAVE AS) to
enter the appropriate program, description, date, and his/her
initials.  I'm thinking four input boxes (one after the other).

This data from the user would be concatenated and would form the file
name (now located in the Save As dialog box).  The user would then
simply choose a location.

Thoughts?

alex

Can someone tell me if I posted this question to the wrong group?

Thanks,
alex

 

[Susan]

yes, you have the right group. and i know what you describe can be
done within the macro - don't know about using the saveas dialogue or
before_saveas event but i suspect that can also be done, too (just
don't know how). as an example:

sProgram = inputbox1.value
sDesc = inputbox2.value
sDate = inputbox3.value
sInitials = inputbox4.value

wb.SaveAs Filename:=sProgram & "_" & sDesc & "_" & sDate & "_" &
sInitials & ".xls"

might get you started.
sorry can't be of more help.
susan

 

[Susan]

here's a link to a post that may be of additional help:
http://groups.google.com/group/micr...l=en&lnk=gst&q=saveas+dialog#9a83fde91588835b

try searching the newsgroup for the different components of your
problem.
susan

 

[Susan]

last one, i promise

http://groups.google.com/group/micr...q=file+name+in+saveas+dialog#3cdae68355e0257d

susan

 

[alex]

last one, i promise

http://groups.google.com/group/microsoft.public.excel.programming/bro...

susan






- Show quoted text -

Thanks for your help Susan. I'll take a look at these and see if I
can extrapolate something.

alex

 

[alex]

Thanks for your help Susan.  I'll take a look at these and see if I
can extrapolate something.

alex- Hide quoted text -

- Show quoted text -

I've got most of the code to work and pasted it below:

Sub xlsSaveAs()

Dim SaveName1 As String
Dim SaveName2 As String
Dim SaveName3 As String
Dim SaveName4 As String
Dim Why As Integer

Why = MsgBox("Would You Like To Use Special Formatting?", vbYesNo,
"File Formatting")

If Why = vbYes Then
SaveName1 = InputBox("PLEASE ENTER PROGRAM
DESCRIPTION")

Application.Dialogs(xlDialogSaveAs).Show SaveName1

Else
Application.Dialogs(xlDialogSaveAs).Show

End If
End Sub

I have a problem, however, with concatenating the results of all my
input boxes. I can show them; e.g.:

If Why = vbYes Then
SaveName1 = InputBox("PLEASE ENTER PROGRAM
DESCRIPTION")
SaveName2 = InputBox("PLEASE ENTER DESC")
SaveName3 = InputBox("PLEASE ENTER DATE")
SaveName4 = InputBox("PLEASE ENTER INITIALS")

but I cannot combine the results.

I also thought about creating one UserForm with four textboxes. I can
call the Form, but I don't know enough to pull the user defined text
from the textboxes.

thoughts?
alex

 

[alex]

I've got most of the code to work and pasted it below:

Sub xlsSaveAs()

Dim SaveName1 As String
Dim SaveName2 As String
Dim SaveName3 As String
Dim SaveName4 As String
Dim Why As Integer

Why = MsgBox("Would You Like To Use Special Formatting?", vbYesNo,
"File Formatting")

            If Why = vbYes Then
                    SaveName1 = InputBox("PLEASE ENTER PROGRAM
DESCRIPTION")

                    Application.Dialogs(xlDialogSaveAs).Show SaveName1

            Else
                    Application.Dialogs(xlDialogSaveAs).Show

    End If
End Sub

I have a problem, however, with concatenating the results of all my
input boxes.  I can show them; e.g.:

If Why = vbYes Then
                   SaveName1 = InputBox("PLEASE ENTER PROGRAM
DESCRIPTION")
                    SaveName2 = InputBox("PLEASE ENTER DESC")
                      SaveName3 = InputBox("PLEASEENTER DATE")
                        SaveName4 = InputBox("PLEASE ENTER INITIALS")

but I cannot combine the results.

I also thought about creating one UserForm with four textboxes.  I can
call the Form, but I don't know enough to pull the user defined text
from the textboxes.

thoughts?
alex- Hide quoted text -

- Show quoted text -

Hello all.

Could someone tell me the code to extract the "User name" from Excel?
Located under Tools | Options | General |

Instead of requiring the user to enter his/her initials, I could
concatenate the Program&_&Description&_&Date&_&User name.

alex