Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Location
    Brooklyn
    Posts
    25

    Unanswered: Importing access query into Excel with VBA

    Hello,
    I have a process that Importing access query into Excel, but I have to change path everytime I run that process.
    Is there are a way to have a Directory window from which I may choose directory/folder to output that query to?

    Thank you for your help....


    ''----Set name of the Excel file with Director name
    strName = lstAccExec.Column(0)
    ''----Remove any extra chars
    Label18.Caption = "Now producing report for " & strName
    strName = Replace(Replace(Replace(strName, ".", "_"), ",", ""), " ", "_")
    ''-----
    strName = "Shipments'09_" & lstAccExec.Column(0)

    strPath = "D:\Shipments\Ship Month End Style 2009\Jan\" + strName + ".xls"

    txtAccExec = lstAccExec.Column(0)

    DoCmd.OutputTo acOutputQuery, "Shipments'09", acFormatXLS, strPath, False

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Check out the FileDialog property. Here's a sample of some code I have that does exactly what you're trying to do (I think).
    Code:
        'Use Common Dialog to get destination file location
        Dim dlgBrowse As FileDialog, _
            strFilePath As String
        Set dlgBrowse = Application.FileDialog(msoFileDialogSaveAs)
        With dlgBrowse
            .AllowMultiSelect = False
            If Nz(strFilePath, "") <> "" Then .InitialFileName = strFilePath
            .Show
            If .SelectedItems.Count <> 0 Then strFilePath = .SelectedItems(1)
        End With
        DoCmd.Hourglass True
        Set dlgBrowse = Nothing
        If Nz(strFilePath, "") = "" Then GoTo ExitSub  'If hits cancel
    Note that when the user hits the Cancel button on the CD, it still returns a zero length string,or a null string, can't remember which of the top of my head. Either way, that's what the last line is for.
    Me.Geek = True

Posting Permissions

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