Link to home
Create AccountLog in
Avatar of bluerocket
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 ...

Help?
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
    Next
 
 
Sheet.Range("A2").CopyFromRecordset rs  'this copy just the data
xlObj.Visible = True
 
xlObj.ActiveWorkbook.SaveAs xlFile
Set Sheet = Nothing
xlObj.Quit
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



try

xlObj.Visible = True

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

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

ASKER

Thanks to both of you!