Dialogbox VBA

[simon.frangenberg]

I am Working on the following code:

Dim resp As Long
Dim sFilename As Variant
Dim newName As String

newName = "I:\Klant Directory\book1.xls"

sFilename = Application.GetSaveAsFilename( _
InitialFileName:=newName)
If sFilename = False Then Exit Sub

If Dir(sFilename) <> "" Then
resp = MsgBox(prompt:="File already exists. Do you want to
overwrite?", Buttons:=vbYesNo)
If resp = vbNo Then
Exit Sub
End If
End If

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sFilename
Application.DisplayAlerts = True


Instead of book1; I want to save excel files in the following format:
subject-client-date-author (e.g. Report-Vbacorp-20080818-MF).
Furthermore, if I run the macro, I want to have a dialogbox that pups
up with date and author already filled out as in the above mentioned
example (thus: now() and MF). Moreover, I want to be able to fill out
in the dialog box the subject and client. Dialog box example:

Subject:
Client:
Date: default format(now(),”yyyymmdd”)
Author: default MF

Save Cancel

I do not know how to fix the code above in order to achieve this, doe
anyone have a clue?

 

[strive4peace]

Hi Simon,

this is an Access forum -- seems like you have an Excel question ...

but, to give you some food for thought ...

if you want the file to be called something other than Book1, then you
need to change your code to construct the filename. You can do
something like this:

Dim file_path As String
Dim file_name As String

file_path = "I:\Klant Directory\"

file_name = "Report-Vbacorp-" _
& format(Date(), "yyyymmdd") & "-MF" _
& ".xls"

then you can do this:

file_path & file_name

to reference the full file specification

just as the date can be concatenated in, so can the other values for
subject, client, and author

if these values are stored in cells, you can reference them -- but
please, if you are doing this in Excel, ask this question in an Excel
newsgroup


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*