VBA Save As Function

[davidgottlieb]

Is there any way that I can create a button in a UserForm that when clicked, the Save As dialog box comes up? If so, how?

Thank You

[alex_read]

David, on the toolbox for designing / adding the controls to the form, right click & choose "addittional controls option.

Drag the control on the form & create the object as you would a text box. on the button, type :

commondialog1.showsave

and volia! (ask if you want example one created).
Hope this helps U.

Alex Read

[davidgottlieb]

I'm not sure what you mean. When I click on additional controls, it comes up with a list of controls. Remember, I am working in VBA not VB 6. Could you explain?

[Arcom]

How about some FLAGS???

Use: CommonDialog.Flags = flagname

For "flagname" use one of the following:

cdlOFNAllowMultiselect - enable multiple file selection
cdlOFNHideReadOnly - hides the "Read Only" checkbox
cdlOFNLongNames - to enable long file names
cdlOFNOverwritePrompt - asks user if to overwrite the file (if that file exists)

There are more constants available, but these are my most used

See HELP for more details.

[davidgottlieb]

Ive got that part figured out. Now, I need to know why is it when the Save As dialog box comes up, I hit cancel instead of save, and it comes up with a debug error message?
Help me?

[DakotaKid]

Get rid of ALL that stuff. Don't use OCX's when there is a relatively simple API call you can use. Below is all the code you need, just paste all of it into a module. You could put it into a Form's module, but change the Public Declare statements to Private Declare. To use the function, just do something like this:

Dim sPath As String
sPath = pfGetFileName(Me.Hwnd, "Input")

Be sure to check to see if sPath = "" before you try to use the file!

Option Explicit

Public Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
     (lpOpenfilename As OPENFILENAME) As Long

Public Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Public Function pfGetFileName (hWnd As Long, sTitle As String) As String
    'Use a system API call to show the 
    '    standard Save File dialog

    Dim tOpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
        
    sFilter = "Text (*.TXT)" & Chr(0) & "*.TXT" & Chr(0)
    sFilter = sFilter & "All Files (*.*)" & Chr(0) &  "*.*" & Chr(0)
    
    With tOpenFile
        .lStructSize = Len(tOpenFile)
        .hwndOwner = hWnd
        'in Access use this line
        '.hwndOwner = Application.hWndAccessApp
        .hInstance = 0
        .lpstrFilter = sFilter
        .nFilterIndex = 1
        .lpstrFile = String$(255, 0)
        .nMaxFile = 255
        .lpstrFileTitle = .lpstrFile
        .nMaxFileTitle = .nMaxFile
        .lpstrInitialDir = ""
        .lpstrTitle = "Select the " & sTitle & " file"
        .flags = 0
        .lpstrFilter = sFilter
        lReturn = GetSaveFileName(tOpenFile)
        
        'Return filename
        If lReturn <> 0 Then
            pfGetFileName = Left$(.lpstrFile, InStr(1, .lpstrFile, Chr$(0)) - 1)
        End If
    End With

End Function

[Arcom]

Why not use OCX controls?

Just set the CancelError property to False and you want get any error messages.

[DakotaKid]

Originally posted by Arcom
Why not use OCX controls?

Just set the CancelError property to False and you want get any error messages.

Yes, and if you set CancelError to False you won't know if the user pressed Cancel. The common way around this is to just check the FileTitle property. Unfortunately, if a user selects a file then clicks Cancel the FileTitle property still gets populated. The only sure way to know if the user clicked Cancel is to use the CancelError property, which will produce a trappable error, or the API call.

A common practice by non-professional developers (and some who call themselves professionals) is to use OCX's wherever possible. There are multiple reasons not to do this. First, it adds to the requirements for your application. Second, loading the OCX's takes extra time. And lastly, it rquires more resources when you use an OCX. Using API calls makes your applications more self-contained, less resource-intensive and faster. I'm not saying I'm opposed to using OCX's, I write them and use them all the time. But I need a wrapper for a few API calls that are pretty simple I just use a VB class and compile it into my application. The class is reusable and adding the overhead of an OCX just isn't necessary.

[Arcom]

Yesssss, I'm aware of all that...

BTW: I only use Windows Common Controls 6 if required