Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Oakland, CA

    Unanswered: Export qry to Excel wh dialog box and suggested file name

    Hello, it might be harder than it seems. I'm trying to output qry into Excel but I have multiple users on a network and the output needs to be stored locally. So the path is not always the same if the user wants to put it on his/her desktop. So, I do need the user to have a dialog window pop up. However, I would like to suggest a file name to be outputted. Right now, it's just a query name. I would like suggest the query name plus today's date. how can I do this. Currently I have:

    DoCmd.OutputTo acOutputQuery, "qryExportAll", "MicrosoftExcelBiff8(*.xls)", "", False, "", 0

    The quotations before ,False are the place where you normally put the path. But I do want the dialog winodow "Save As..." to pop up with a different suggested file name than my query. So how can I do that?

  2. #2
    Join Date
    Oct 2003
    The last time I used save as or open dialog boxes, they were active x controls that would pass back the filename including the path. I would store that into a variable which would get used in my code.

    For Opening a file, I made a form called _OpenDialog that had the MSComDlg.CommonDialog.1 control and added the following code to the form open:
    Private Sub Form_Open(Cancel As Integer)
    Dim getfile As String
    Dim activefile As String
    Dim Message As String
    Dim st1 As String, frm As Form
    Dim Current
    Dim Yr
    If Nz(Me.OpenArgs, "Voters") = "Sup" Then
    st1 = "All Files (*.*)|*.*|Supp Files|*.sup"
    Set frm = Forms!ImportSupData
    st1 = "All Files (*.*)|*.*|Notification Files|*.vnf"
    Set frm = Forms!ImportSupData
    End If
    Current = DLookup("[Value]", "UserSettings", "[Code] = 'CurrentDatabaseFile' ")

    On Error Resume Next
    CommonDialog1.CancelError = True
    On Error GoTo ErrHandler
    CommonDialog1.flags = 8198 'cdlOFNCreatePrompt + cdlOFNOverwritePrompt + cdlOFNHideReadOnly
    CommonDialog1.Filter = st1
    CommonDialog1.FilterIndex = 2
    CommonDialog1.InitDir = left(Current, InStr(Current, "") - 7)
    CommonDialog1.DialogTitle = "Open Import File"
    getfile = CommonDialog1.FileName
    CommonDialog1.FileName = ""
    frm!FName = getfile
    DoCmd.Close acForm, "_OpenDialog"

    Exit Sub

    getfile = ""
    'DoCmd.Close acForm, "QuickMessage"
    DoCmd.Close acForm, "_OpenDialog"
    End Sub

Posting Permissions

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