About Miguel Zapico Expertise I can answer question about how to use scripts to consolidate data, connect different systems and automate tasks. I have no experience on using VBScript on web programming.
Experience I have been using VBScript and Windows Scripting Host as my swiss tool for the last 6 years.
Organizations New York PC users group (NYPC)
Independant Computer Consultants Association (ICCA)
Education/Credentials Microsoft MCSE in Windows NT
Expert: Miguel Zapico Date: 8/10/2006 Subject: Using Save As with VBA and Input Boxes
Question Hi Miguel
That piaece of code does infact still do logical comparisions and is saving the file as true, what I need is for the file save name to be stored as a variable, and then, when using the save as function, for the variable to be called and inserted into the file name path.
I have made some changes to the script including the rewrite you advised, the variable SaveFile has replaced the saveFileName function, and it has now been stored as a string.
When debuging it keeps coming back to the script used to call the variable into the Save As function, see below revised script:
Sub Test()
Dim Employeename As String
Dim Reportdate As Variant
Dim SaveFile As String
Employeename = InputBox("Enter name")
Reportdate = InputBox("Enter date inthe following format 01.01.1900")
Workbooks.Add
SaveFile = "Friday Communication created by " & Employeename & " on " & Reportdate
MsgBox "Save as " & SaveFile
If vbOK Then
ActiveWorkbook.SaveAs Filename:="Y:Work<SaveFile>.xls"
End If
End Sub
On another note, can you confirm how I can get the Reportdate variable to equate to "()today" or todays date, I don't really want an input box for auditing reasons.
DB
-------------------------
Followup To
Question -
I need to know how to use input boxes to generate a file name that VB can then use to save the file as, so far I have:
Sub Test()
Dim Employeename As String
Dim Reportdate As Variant
Employeename = InputBox("Enter name")
Reportdate = InputBox("Enter date inthe following format 01.01.1900")
Workbooks.Add
fileSaveName = ("Friday Communication created by ") & (Employeename) & (" on ") & (Reportdate)
MsgBox "Save as " & fileSaveName
If vbOK Then
ActiveWorkbook.SaveAs Filename:="Y:Work\" < fileSaveName > ".xls"
End If
It does save a copy of the file but it saves it as TRUE.xls, clearly I am missing a trick, can you help?
Answer -
I think the trick is on the use of parenthesis to concatenate text, the code is doing logical comparisions instead. Try changing the sentence to:
fileSaveName = "Friday Communication created by " & Employeename & " on " & Reportdate
Hope this helps,
Miguel.
Answer You can encode everything on the fileSaveName, including the extension. Something like:
fileSaveName = "Y:\Work\Friday Communication created by " & Employeename & " on " & Reportdate & ".xls"
and then do directly:
ActiveWorkbook.SaveAs Filename:=fileSaveName.
For your second question, use Format and Now functions, something like:
Reportdate = Format(Now,"DD.MM.YYYY")
