Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: picking up 'Save As...' filename (Was '"clicking'

    So this code brings up a "Save as..." dialog box.
    Code:
        DoCmd.OutputTo 0, "Users", "MicrosoftExcelBiff8(*.xls)", , True, , 0
    And if you hit the "cancel" button instead of "save" it faisl with this error:
    "The output action was cancelled"
    How can I trap this error so that when you hit cancel you get no messagebox?

    Ta!

    - GeorgeV
    ------------------------------------------------------------------
    EDIT: bugger it - I hit save before I finished my title... sorry!
    George
    Home | Blog

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    On Error Resume Next

    Right before your line of code.
    Inspiration Through Fermentation

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Or handle it in your error trap (2501).
    Paul

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thanks for te suggestions peeps!
    On Error Resume Next works just fine.

    pbaldy - would your solution be something like:

    Code:
    Err_cmd_Click:
    If err.number = 2501 then
    ...
    ---------------------------------------------
    EDIT: Yes, this works
    - GeorgeV
    Last edited by gvee; 02-01-07 at 05:47.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I am using an OutputTo to send a table to an excel spreadsheet and open Excel automatically.
    Code:
        DoCmd.OutputTo 0, "Users", "MicrosoftExcelBiff8(*.xls)", , True, , 0
    Now, here's my new problem
    Error number 2302 occurs when a file with the same filename is already open.
    E.g.
    - User runs the export and saves it as users.xls. This file is then opened.
    - If the user runs the export a second time and saves it as users.xls
    - The file is then supposed to open, but cant because an instance of the same name is already open

    I've trapped the error but I want to pick up the save as filename so I can put in a useful error like: "Filename " & SavedAsFileName & " is already open"

    Any ideas?

    - GeorgeV
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    If you are opening Excel once done then I would automate from the beginning and not bother with proprietry stuff like OutputTo. This lot does everything you need and more (e.g. it checks to see if the excel file exists before saving and generates a unique name if it does).
    http://www.dbforums.com/showpost.php...81&postcount=4

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Attached screenshot.
    I want to intercept the filename as highlighted

    - GeorgeV
    Attached Thumbnails Attached Thumbnails save as.bmp  
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No idea - and another good reason (IMHO) to avoid using docmd methods like OutputTo. Please check out the link - you might be able to get rid of the whole problem altogether. Everytime someone starts getting ambitious using OutputTo or TransferToSpreadsheet they seem to run into problems - these are simple methods not intended for sophisticated changes or tweaks.

    So OutputTo opens up a common dialog box when called does it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I know it's a little ambitious but I'm a perfectionist
    I've latched on to this idea - just to make things a little more user friendly!

    Sometimes perfection is not needed ^_^

    I'll take a look at the link when I can and let you all know how it goes!

    Cheers Pootle and Co.
    George
    Home | Blog

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here's a thought....

    You are not supplying the filename argument for the outputto.
    That's what is triggering the SaveAs dialog box. I suppose you
    are doing that to allow the user to specify WHERE the file goes.
    If you always put the file in the same location -like c:\ - then all you
    need is an InputBox prompting the user for the file name. That would
    give you a string to put into the filename argument, and also give you
    the filename for your second problem.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The user has an option to change the name of the file and it always saves to a default directory through some cunning jiggery.
    George
    Home | Blog

  12. #12
    Join Date
    Sep 2004
    Posts
    1

    Use the API to open a save file dialog

    The identification of a filename and location can be done via an API call.
    This code calls the Windows API save file dialog.
    I lifted this code from a bestselling book on Access VBA programming (in 98)
    I know that access now supports this natively - but I have never bothered to use it.

    ' --------------------------------------------------
    Option Compare Database 'Use database order for string comparisons
    Option Explicit

    Function DBtoExtractTo(varDirectory As Variant)
    ' Comments :
    ' Parameters : varDirectory -
    ' Returns : -
    ' Created :
    ' Modified :
    '
    ' --------------------------------------------------------
    Dim szFilter As String
    Dim Retval As Variant
    Dim lngFlags As Long
    Dim varFileName As Variant
    Dim szDirectory As String


    On Error GoTo DBtoExtractTo_EH
    '===============================
    ' Specify that the chosen file must already exist
    ' don't change directories
    ' when you're done, and open the file as read-only.
    ' Also, don't bother displaying the
    ' read-only box. It'll only confuse people.


    varFileName = dhFileDialog(strFilename:=CStr(varDirectory), strFilter:="Access (*.MDB)" & _
    vbNullChar & "*.MDB" & vbNullChar & vbNullChar, _
    strDialogTitle:="Create Transfer File", _
    lngFlags:=f_o_p_OFN_HIDEREADONLY _
    Or gOFN_READONLY _
    Or gOFN_OVERWRITEPROMPT _
    Or gOFN_SHAREAWARE _
    Or gOFN_FILEMUSTEXIST, _
    fOpenFile:=False)

    DBtoExtractTo = varFileName
    Exit Function

    DBtoExtractTo_EH:


    Error_Handler "FileOpen1", "DBtoExtractTo", Erl, Err, Error$
    Resume

    End Function

    ' --------------------------------------------------
    Option Explicit

    ' From "VBA Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 1997; Sybex, Inc. All rights reserved.

    ' Examples from Chapter 12

    Private Type mtypOPENFILENAME
    lngStructSize As Long ' Size of structure
    hwndOwner As Long ' Owner window handle
    hInstance As Long ' Template instance handle
    strFilter As String ' Filter string
    strCustomFilter As String ' Selected filter string
    intMaxCustFilter As Long ' Len(strCustomFilter)
    intFilterIndex As Long ' Index of filter string
    strFile As String ' Selected filename & path
    intMaxFile As Long ' Len(strFile)
    strFileTitle As String ' Selected filename
    intMaxFileTitle As Long ' Len(strFileTitle)
    strInitialDir As String ' Directory name
    strTitle As String ' Dialog title
    lngFlags As Long ' Dialog flags
    intFileOffset As Integer ' Offset of filename
    intFileExtension As Integer ' Offset of file extension
    strDefExt As String ' Default file extension
    lngCustData As Long ' Custom data for hook
    lngfnHook As Long ' LP to hook function
    strTemplateName As String ' Dialog template name
    End Type

    Declare Function GetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (ofn As mtypOPENFILENAME) As Boolean
    Declare Function GetSaveFileName Lib "comdlg32.dll" _
    Alias "GetSaveFileNameA" (ofn As mtypOPENFILENAME) As Boolean

    ' Open/Save dialog flags
    Global Const gOFN_READONLY = &H1
    Global Const gOFN_OVERWRITEPROMPT = &H2
    Global Const gOFN_HIDEREADONLY = &H4
    Global Const gOFN_NOCHANGEDIR = &H8
    Global Const gOFN_SHOWHELP = &H10
    Global Const gOFN_NOVALIDATE = &H100
    Global Const gOFN_ALLOWMULTISELECT = &H200
    Global Const gOFN_EXTENSIONDIFFERENT = &H400
    Global Const gOFN_PATHMUSTEXIST = &H800
    Global Const gOFN_FILEMUSTEXIST = &H1000
    Global Const gOFN_CREATEPROMPT = &H2000
    Global Const gOFN_SHAREAWARE = &H4000
    Global Const gOFN_NOREADONLYRETURN = &H8000
    Global Const gOFN_NOTESTFILECREATE = &H10000
    Global Const gOFN_NONETWORKBUTTON = &H20000
    Global Const gOFN_NOLONGNAMES = &H40000
    ' Flags for hook functions and dialog templates
    'Global Const OFN_ENABLEHOOK = &H20
    'Global Const OFN_ENABLETEMPLATE = &H40
    'Global Const OFN_ENABLETEMPLATEHANDLE = &H80
    ' Windows 95 flags
    Global Const gOFN_EXPLORER = &H80000
    Global Const gOFN_NODEREFERENCELINKS = &H100000
    Global Const gOFN_LONGNAMES = &H200000

    ' Custom flag combinations
    Global Const gDhOFN_OPENEXISTING = gOFN_PATHMUSTEXIST Or gOFN_FILEMUSTEXIST Or gOFN_HIDEREADONLY
    Global Const gDhOFN_SAVENEW = gOFN_PATHMUSTEXIST Or gOFN_OVERWRITEPROMPT Or gOFN_HIDEREADONLY
    Global Const gDhOFN_SAVENEWPATH = gOFN_OVERWRITEPROMPT Or gOFN_HIDEREADONLY

    Private Declare Function GetActiveWindow Lib "user32" () As Long


    ' NOTE: You may have some Microsoft Windows API functions defined in an
    ' existing Microsoft Access library; therefore, your declarations may be
    ' duplicates. If you receive a duplicate procedure name error message,
    ' remove or comment out the declarations statement in your code.




    Function dhFileDialog( _
    Optional strInitDir As String, _
    Optional strFilter As String = _
    "All files (*.*)" & vbNullChar & "*.*" & _
    vbNullChar & vbNullChar, _
    Optional intFilterIndex As Integer = 1, _
    Optional strDefaultExt As String = "", _
    Optional strFileName As String = "", _
    Optional strDialogTitle As String = "Open File", _
    Optional lngHwnd As Long = -1, _
    Optional fOpenFile As Boolean = True, _
    Optional ByRef lngFlags As Long = _
    gDhOFN_OPENEXISTING) As Variant
    ' Comments :
    ' Parameters : strInitDir
    ' strFilter
    ' intFilterIndex
    ' strDefaultExt
    ' strFileName
    ' strDialogTitle
    ' lngHwnd
    ' fOpenFile
    ' lngFlags -
    ' Returns : Variant -
    ' Created :
    ' Modified :
    '
    ' --------------------------------------------------------

    On Error GoTo err_dhFileDialog
    ' Wrapper function for the GetOpenFileName API function.
    ' Displays the common open/save as dialog and returns
    ' the file(s) selected by the user.

    ' From "VBA Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 1997; Sybex, Inc. All rights reserved.

    ' In:
    ' strInitDir (Optional)
    ' Inital directory.
    ' strFilter (Optional)
    ' File filter as null delimited/double-null
    ' terminated string.
    ' intFilterIndex (Optional, default = 1)
    ' Initial filter index.
    ' strDefaultExt (Optional)
    ' Default file extension if none specified.
    ' strFilename (Optional)
    ' Initial file name for dialog.
    ' strDialogTitle (Optional, default = "Open File")
    ' Dialog title.
    ' hwnd (Optional, default = -1)
    ' Handle of dialog owner window.
    ' fOpenFile (Optional, default = True)
    ' If True, displays Open dialog, if False,
    ' displays Save As dialog.
    ' lngFlags (Optional)
    ' Flags for API function (see declarations section).
    ' Out:
    ' lngFlags
    ' Returns flags set by the API function after closing
    ' the dialog.
    ' Return Value:
    ' Name of the file or files chosen by the user.
    ' Note:
    ' If you allow multi-select, returned string will
    ' be the directory name followed by a space-delimited
    ' list of files.
    ' Example:
    ' strFile = dhFileDialog(strFilter:="All files" & _
    ' vbNullChar & "*.*" & vbNullChar & vbNullChar)

    Dim ofn As mtypOPENFILENAME
    Dim strFileTitle As String
    Dim fResult As Boolean

    ' Fill in some of the missing arrguments
    If strInitDir = "" Then
    strInitDir = CurDir
    End If
    If lngHwnd = -1 Then
    lngHwnd = GetActiveWindow()
    End If

    ' Set up the return buffers
    strFileName = strFileName & String(255 - Len(strFileName), 0)
    strFileTitle = String(255, 0)

    ' Fill in the OPENFILENAME structure members
    With ofn
    .lngStructSize = Len(ofn)
    .hwndOwner = lngHwnd
    .strFilter = strFilter
    .intFilterIndex = intFilterIndex
    .strFile = strFileName
    .intMaxFile = Len(strFileName)
    .strFileTitle = strFileTitle
    .intMaxFileTitle = Len(strFileTitle)
    .strTitle = strDialogTitle
    .lngFlags = lngFlags
    .strDefExt = strDefaultExt
    .strInitialDir = strInitDir
    .hInstance = 0
    .strCustomFilter = String(255, 0)
    .intMaxCustFilter = 255
    .lngfnHook = 0
    End With

    ' Call the right function
    If fOpenFile Then
    fResult = GetOpenFileName(ofn)
    Else
    fResult = GetSaveFileName(ofn)
    End If

    ' If successful, return the filename,
    ' otherwise return Null
    If fResult Then
    ' Return any flags to the calling procedure
    lngFlags = ofn.lngFlags

    ' Return the result
    If (ofn.lngFlags And gOFN_ALLOWMULTISELECT) = 0 Then
    dhFileDialog = dhTrimNull(ofn.strFile)
    Else
    dhFileDialog = ofn.strFile
    End If
    Else
    dhFileDialog = Null
    End If

    Exit Function

    err_dhFileDialog:
    Error_Handler "_basCommonDialog", "dhFileDialog", Erl, Err, Error$
    Resume
    End Function

Posting Permissions

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