Results 1 to 4 of 4
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Excel BuiltIn Dialogs (VBA/Excel/XP)

    I get a message like

    <pre>A file named C:harvey.XLS already exists in this location.
    Do you want to replace it?</pre>



    Instead of showing this message, I want to capture the event {don't know how} and throw up my own dialog suggesting some alternate file names to our user. He could still overwrite, but we want to suggest something else.

    I'm pretty sure it is possible, cannot remember syntax.
    Alan

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel BuiltIn Dialogs (VBA/Excel/XP)

    hi Alan,

    untested:

    <pre>sub savemyfile
    dim MyFile as string
    myfile="C:harvey.XLS"
    if len(dir(myfile))=0 then
    'file doesn't exist
    activeworkbook.saveas myfile
    else
    msgbox "file already exists"
    'present filename alternatives or cancel
    end if
    end sub
    </pre>


    if you want to use an event, then use Workbook_BeforeSave.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel BuiltIn Dialogs (VBA/Excel/XP)

    You mean something like this?

    <pre>Option Explicit

    Sub GetOpenFileNameExample()
    Dim vFilename As Variant
    Dim sPath As String
    sPath = "c:windowstemp"
    ChDrive sPath
    ChDir sPath
    vFilename = Application.GetOpenFilename("text files (*.txt),*.txt", , _
    "Please select the file to import", , False)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    If CStr(vFilename) = "" Then Exit Sub
    If Dir(CStr(vFilename)) <> "" Then
    MsgBox "Exists!!!"
    Else
    ' Now do something with vfilename, like:
    ' Workbook.OpenText Filename:=vFilename
    End If
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Excel BuiltIn Dialogs (VBA/Excel/XP)

    I used Pieter's routine and jump out to a new UserForm where I suggest 5 names and if none of them are good, the user has the option of making up his own.

    <pre>Workbooks.Add template:=[TemplateDir] & [TemplateName]
    myFile = Tom & Cooper & AprilS & daPlace & daNewFileName
    If Len(Dir(myFile)) = 0 Then
    ActiveWorkbook.SaveAs Filename:=Tom & Mix _
    & WillRoger & daPlace & daNewFileName
    Else
    'IF EXISTS -- Hijack dialog and suggust different
    AlternateNameRoutine
    ActiveWorkbook.SaveAs Filename:=Tom & Mix _
    & WillRoger & daPlace & daNewerFileName 'Newer file name
    End If
    ActiveSheet.Unprotect PwD
    </pre>



    Everybody is happy; thanks so much!
    Alan

Posting Permissions

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