Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Exclamation Unanswered: VBA Code For Macro?

    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>

    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
    End Sub

    Thank you !!!

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Here is a soloution to your problem

    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
    End Sub


  3. #3
    Join Date
    Jun 2004

    Exclamation Just a Couple of BUGS

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts