Multi-select List Box output to Excel File

  • Thread starter Thread starter Adam
  • Start date Start date
on which line did it throw the error?

--

Ken Snell
<MS ACCESS MVP>







- Show quoted text -
Click to expand...

Okay, although I'm not sure what I did, it's at least writing an Excel
file to my C: drive, the only problem is it's including the entire
query rather than my selections in the list box. Right now when you
hit the command button, a message box pops up saying: strReport =
'qryAvgCostMile'. I hit OK, then another message box pops up with:
Error 0 - . At that point it writes the file to my C: drive. Here is
the code for that section:

Private Sub cmdAvgCostMileXLS_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
MsgBox "strReport= '" & strReport & "'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub
 
on which line did it throw the error?

--

Ken Snell
<MS ACCESS MVP>







- Show quoted text -
Click to expand...

Okay, although I'm not sure what I did, it's at least writing an Excel
file to my C: drive, the only problem is it's including the entire
query rather than my selections in the list box. Right now when you
hit the command button, a message box pops up saying: strReport =
'qryAvgCostMile'. I hit OK, then another message box pops up with:
Error 0 - . At that point it writes the file to my C: drive. Here is
the code for that section:

Private Sub cmdAvgCostMileXLS_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
MsgBox "strReport= '" & strReport & "'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub
 
OK - Let's change that code section to this so that you stop before you get
to the error handler:

Private Sub cmdAvgCostMileXLS_Click()
On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub


OK - now the problem appears to be in your query "qryAvgCostMile". Post the
SQL statement of this query and let's see what it's supposed to be doing.
 
OK - Let's change that code section to this so that you stop before you get
to the error handler:

Private Sub cmdAvgCostMileXLS_Click()
On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub

OK - now the problem appears to be in your query "qryAvgCostMile". Post the
SQL statement of this query and let's see what it's supposed to be doing.

--

Ken Snell
<MS ACCESS MVP>







- Show quoted text -
Click to expand...

Should it be referencing the list box (lstCustName) selections or the
query? I would think both, but I don't see where it's looking at the
list box. Here is the SQL:

SELECT ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE,
ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME,
Count(ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE) AS Frequency,
Sum(ODSPROD_T_TSI_FBSUM2.TOTAL_FREIGHT_PAID) AS [Freight Cost],
Sum(ODSPROD_T_TSI_FBSUM2.TOTAL_MILEAGE) AS Miles, [Miles]/[Freight
Cost] AS [Avg Cost/Mile]
FROM ODSPROD_T_TSI_FBSUM2
GROUP BY ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE,
ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME,
ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS
HAVING (((ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS) Like "Approv*" Or
(ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS) Like "Pai*"))
ORDER BY ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME;
 
on which line did it throw the error?

--

Ken Snell
<MS ACCESS MVP>







- Show quoted text -
Click to expand...

Okay, although I'm not entrely sure what I did, it's at least writing
an Excel file to my C: drive, only problem is it's including
everything in the query, not what I select in the list-box. When I hit
the command button, I first get a message box that says: strReport =
'qryAvgCostWeight'. When I hit OK, the another message box pops up and
says: Error 0 -. At that point the message box goes away and it writes
the file. Now I just have to figure out how to pass my selections from
the list box to output the file rather than the entire query, and get
rid of those messages and I'm in business!

Here's the code for that section:

Private Sub cmdAvgCostMileXLS_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostWeight"
MsgBox "strReport= '" & strReport & "'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub
 
on which line did it throw the error?

--

Ken Snell
<MS ACCESS MVP>







- Show quoted text -
Click to expand...

Okay, although I'm not sure what I did, it's at least writing an Excel
file to my C: drive, the only problem is it's including the entire
query rather than my selections in the list box. Right now when you
hit the command button, a message box pops up saying: strReport =
'qryAvgCostMile'. I hit OK, then another message box pops up with:
Error 0 - . At that point it writes the file to my C: drive. Here is
the code for that section:

Private Sub cmdAvgCostMileXLS_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
MsgBox "strReport= '" & strReport & "'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub
 
on which line did it throw the error?

--

Ken Snell
<MS ACCESS MVP>







- Show quoted text -
Click to expand...

Okay, although I'm not sure what I did, it's at least writing an Excel
file to my C: drive, the only problem is it's including the entire
query rather than my selections in the list box. Right now when you
hit the command button, a message box pops up saying: strReport =
'qryAvgCostMile'. I hit OK, then another message box pops up with:
Error 0 - . At that point it writes the file to my C: drive. Here is
the code for that section:

Private Sub cmdAvgCostMileXLS_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
MsgBox "strReport= '" & strReport & "'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub
 
OK - Let's change that code section to this so that you stop before you get
to the error handler:
Click to expand...
Private Sub cmdAvgCostMileXLS_Click()
On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub
Click to expand...
OK - now the problem appears to be in your query "qryAvgCostMile". Post the
SQL statement of this query and let's see what it's supposed to be doing.

Ken Snell
<MS ACCESS MVP>
Click to expand...
- Show quoted text -
Click to expand...

Should it be referencing the list box (lstCustName) selections or the
query? I would think both, but I don't see where it's looking at the
list box. Here is the SQL:

SELECT ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE,
ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME,
Count(ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE) AS Frequency,
Sum(ODSPROD_T_TSI_FBSUM2.TOTAL_FREIGHT_PAID) AS [Freight Cost],
Sum(ODSPROD_T_TSI_FBSUM2.TOTAL_MILEAGE) AS Miles, [Miles]/[Freight
Cost] AS [Avg Cost/Mile]
FROM ODSPROD_T_TSI_FBSUM2
GROUP BY ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE,
ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME,
ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS
HAVING (((ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS) Like "Approv*" Or
(ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS) Like "Pai*"))
ORDER BY ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME;- Hide quoted text -

- Show quoted text -
Click to expand...

Ken,

I finally figured it out! Thanks for all the help and patience, I
appreciate it. Here's the code that did the trick:

Private Function BuildWhereClause() As String
'On Error Goto Err_Handler

Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CUSTOMER_NAME] IN (" & Left$(strWhere2, lngLen)
& ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
BuildWhereClause = strWhere3
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End Function

Private Sub cmdAvgCostMileXLS_Click()
Dim qd As DAO.QueryDef
Set qd = Nothing
CurrentDb.QueryDefs.Delete "qryAvgCostMileXLS"
On Error Resume Next 'to ignore the error
CurrentDb.QueryDefs.Delete "qryAvgCostMileXLS "
On Error GoTo Err_Handler 'to break if there is an error
Set qd = CurrentDb.CreateQueryDef("qryAvgCostMileXLS", _
"Select * from qryAvgCostMile where " &
BuildWhereClause())
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryAvgCostMileXLS", "C:\" & "AvgCostMile" & ".xls"
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub
 
OK -- my apologies, in all the effort to get the TransferSpreadsheet action
properly coded, I forgot that you originally were trying to filter a report
and then export that report.

What we will need to do is create a temporary query that is properly
filtered, and then we'll export that query.


Private Sub cmdAvgCostMileXLS_Click()

On Error Goto Err_Handler
Dim strQuery As String 'Name of query to export.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for query.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CUSTOMER_NAME] IN (" & Left$(strWhere2, lngLen)
& ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2

End If

strQuery = "qryAvgCostMile"

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query with WHERE filter
' that will be used for exporting data
strSQL = "SELECT * FROM [" & strQuery & "] WHERE " & _
strWhere3 & ";"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQName, "C:\" & "AvgCostMile" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
Err.Clear
On Error Resume Next
qdf.Close
Set qdf = Nothing
dbs.QueryDefs.Delete strQName
dbs.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>




Adam said:
OK - Let's change that code section to this so that you stop before you
get
to the error handler:

Private Sub cmdAvgCostMileXLS_Click()
On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "qryAvgCostMile"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdAvgCostMileXLS_Click"
End If
End Sub

OK - now the problem appears to be in your query "qryAvgCostMile". Post
the
SQL statement of this query and let's see what it's supposed to be doing.

--

Ken Snell
<MS ACCESS MVP>







- Show quoted text -
Click to expand...

Should it be referencing the list box (lstCustName) selections or the
query? I would think both, but I don't see where it's looking at the
list box. Here is the SQL:

SELECT ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE,
ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME,
Count(ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE) AS Frequency,
Sum(ODSPROD_T_TSI_FBSUM2.TOTAL_FREIGHT_PAID) AS [Freight Cost],
Sum(ODSPROD_T_TSI_FBSUM2.TOTAL_MILEAGE) AS Miles, [Miles]/[Freight
Cost] AS [Avg Cost/Mile]
FROM ODSPROD_T_TSI_FBSUM2
GROUP BY ODSPROD_T_TSI_FBSUM2.CLOSE_OUT_DATE,
ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME,
ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS
HAVING (((ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS) Like "Approv*" Or
(ODSPROD_T_TSI_FBSUM2.FREIGHT_BILL_STATUS) Like "Pai*"))
ORDER BY ODSPROD_T_TSI_FBSUM2.CUSTOMER_NAME;
Click to expand...
 
Back
Top