Save After Query Export to Excel 1

[Sumnor]

I have an Access database running on a Citrix server that allows users to successfully export query results to Microsoft Excel. I use the following line in a module:

DoCmd.OutputTo acQuery, "CourseOpty", "MicrosoftExcel(*.xls)", "", True, ""

The problem is my users do not want to save the file before they see the results in Excel, and that is what they are required to do as it is coded. I'm just not sure what I need to adjust/insert to simply allow the users to view their query results in Excel and then decide where they want to save the results.

Something tells me this is a simple adjustment, but my frustration is not allowing me to see the solution. Many thanks, in advance, for assistance with this.

 

[InkyRich]

You could try allowing it to save the file and then delete if they dont want to save it.

Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[Sumnor]

Thanks for your reply.

Since the save would default to a network drive, and mapping drives properly and/or searching for files is a bit too difficult for some of our endusers they really don't want a save until they have reviewed the data in Excel. I can easily display the query results in the database, but there are reasons why that isn't a good enough solution. I know...

So, I'm really interested in just pushing it to Excel without an initial save.

Thanks for any responses - I truly appreciate it.

Jennifer

 

[bubba100]

There are other ways of doing this and this might be one of the longer ways, but here goes.
What follows is the code that is attached to a button on a form that creates an Excel spreadsheet. The purpose of the form(actually there are several related forms used) is to select a table to extract information. Select fields from that table. Sort based on fields of your selection. Finally, filter the information. That is creating a query. The code that gets inserted in Me.Txtfinal is a select query. You can set a recordset to be a table, a query is also a recordset. This creates an Excel workbook called Book1. It won't be saved anywhere, till you tell it.



Private Sub cmdExcel_Click()
On Error GoTo Err_cmdExcel_Click
Dim oExcel As Excel.Application
'Dim oExcel As Object
Dim db As Database
Dim RS As Recordset
Dim bRunning As Boolean
Dim iAnswer As Integer
Dim iColumn As Integer
Dim sData As String
Dim fldLoop As Field
Dim fldCount As Integer
On Error GoTo Err_cmdExcel_Click
Screen.MousePointer = 11
Set db = CurrentDb
Set RS = db.OpenRecordset(Me.txtFinal)
If RS.EOF Then
Screen.MousePointer = 0
Set db = Nothing
Beep
MsgBox "This resulted in 0 records found.", vbInformation, "No Records Found"
Exit Sub
End If
RS.MoveLast
RS.MoveFirst
iAnswer = 6
If RS.RecordCount > 1000 Then
Screen.MousePointer = 0
iAnswer = MsgBox("This resulted in " & RS.RecordCount & " records. Continue to Excel?", vbQuestion + vbYesNo, "High Record Count")
End If
If iAnswer = 6 Then
bRunning = True
Screen.MousePointer = 11
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
On Error GoTo Err_cmdExcel_Click
If oExcel Is Nothing Then
Set oExcel = CreateObject("Excel.application")
bRunning = False
End If
On Error GoTo Err_cmdExcel_Click
DoEvents
oExcel.Visible = True
oExcel.Workbooks.Add
oExcel.Range("a2").CopyFromRecordset RS
iColumn = 1
For Each fldLoop In RS.Fields
sData = fldLoop.Name
'sData = Me.txtColumns 'Parse(Me.txtColumns, ",", 1) 'parse?
' Do While sData & "" <> ""
oExcel.Cells(1, iColumn) = sData
iColumn = iColumn + 1
' sData = Me.txtColumns 'Parse(Me.txtColumns, ",", iColumn) 'parse?
Next
'Loop
End If

Exit_cmdExcel_Click:
Screen.MousePointer = 0
RS.Close
Set RS = Nothing
Set db = Nothing
Set oExcel = Nothing


Exit Sub

 

[Sumnor]

Thank you so much for this response. I'm going to try and implement on my database. It may be long, but if it works for it is worth the effort!

Warm Regards,
Jennifer

 

[bubba100]

Good luck!