VBA Code For Macro?

scobad · #1 (**permalink**) 06-09-04, 16:04

I am Tring to HAVE a MACRO that does the following:

When the Button is Selected:
1. The File Prompts USER to save in a Directory of their CHOICE.
2 Automatically Names the SAVED File in the following Scheme>
SES_TimeCard_###

The ### is the value of cell C2

Here is My Code:

ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\SES_Time_061204.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.Dialogs(xlDialogSendMail).Show
Range("A1").Select
End Sub

Thank you !!!

DavidCoutts · #2 (**permalink**) 06-10-04, 08:07

Here is a soloution to your problem

Code:

Private Sub cmdSave_Click()
    Dim MyFile As String
    Dim SaveFile As String
    Dim MyCheck As String
    'get the filename
    MyFile = "SES_TimeCard_" & Range("C2").Value
    'get the save location and name
    SaveFile = Application.GetSaveAsFilename(MyFile, , , SaveLocation)
    'strip off the directory path to check filename
    MyCheck = Right(SaveFile, Len(SaveFile) - InStrRev(SaveFile, "\"))
    MyCheck = Left(MyCheck, InStrRev(MyCheck, ".") - 1)
    If MyCheck <> MyFile Then
        'if filename changed put back to original filename
        SaveFile = Left(SaveFile, InStrRev(SaveFile, "\")) & MyFile
        MsgBox "Saving file as ..." & Chr(10) & SaveFile
    End If
    'save the file
    ActiveWorkbook.SaveAs SaveFile, xlNormal
    Application.Dialogs(xlDialogSendMail).Show
End Sub

HTH

David

scobad · #3 (**permalink**) 06-10-04, 11:08

1. When you hit the button - The save box pops up. When you hit save you get:

Run-time Error 1004:
Microsoft Excel cannot access the file c:\documents and settings\sb\desktop\SES_TimeCard_06/12/04

Several Reasons Why
- File or path does NOT exist
- used by another program
Close and tray again.

I think It is BECAUSE cell C2 is a DATE
Format (06/12/04) I need to convert to string 06_12_04

Also, When I select Debug - This line is YELLOW:
ActiveWorkbook.SaveAs SaveFile, xlNormal

Internet Services

Web Development

Digital Marketing

Consumer Tech