Link to home
Create AccountLog in
Avatar of bluerocket

asked on

Export to Excel --> Prompt for Save Location --> Open Spreadsheet or Folder

Using VBA, I would like to export a query, "qryExport", allow the user to choose the save path, and then either open the excel spreadsheet or open the folder where the spreadsheet is saved (either will suffice).

The procedure below works great, except that it doesn't prompt the user for the path (I've put in a test name & path on this one).  I've also tried docmd.outputto, but it just saves and won't open the spreadsheet ...

Sub exportToXl()
Dim db As DAO.Database, rs As DAO.Recordset
Dim i As Integer, j As Integer
Dim ssql As String, xlFile As String
Dim xlObj As Object
Dim Sheet As Object
Dim filename As String
filename = "test"
xlFile = "c:\apogee\exports\" & filename & ".xls"
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("qryExport")
Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.add ' xlFile, , True
Set Sheet = xlObj.ActiveWorkbook.Sheets(1)
'copy the headers
Dim iRow, iCol
iRow = 1
    For iCol = 0 To rs.fields.Count - 1
        Sheet.Cells(iRow, iCol + 1).value = rs.fields(iCol).name
Sheet.Range("A2").CopyFromRecordset rs  'this copy just the data
xlObj.Visible = True
xlObj.ActiveWorkbook.SaveAs xlFile
Set Sheet = Nothing
Set xlObj = Nothing
MsgBox ""
End Sub

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image


xlObj.Visible = True

Application.Dialogs(xlDialogSaveAs).Show  ' add this line
'xlObj.ActiveWorkbook.SaveAs xlFile
Set Sheet = Nothing
Set xlObj = Nothing
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of bluerocket


Thanks to both of you!