Multi-select List Box output to Excel File

[Adam]

Team... I have some VB code in Access '03 that takes selections from a
multi-select list box and outputs to a report based upon a query. What
I'm trying to do is to place another button on my form where the user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.

Private Sub ok_Click()

'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"

strReport = "rptAvgCostMile"
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

DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub

 

[Ken Snell \(MVP\)]

Using your strWhere3 string, build a full SQL statement and save it as a
temporary query. Then export that query. Then delete that temporary query.

Here is some sample code that may get you started; it assumes that you've
finished building the strWhere3 string:


Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


--

Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from a
multi-select list box and outputs to a report based upon a query. What
I'm trying to do is to place another button on my form where the user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.

Private Sub ok_Click()

'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"

strReport = "rptAvgCostMile"
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

DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub

 

[Adam]

Using your strWhere3 string, build a full SQL statement and save it as a
temporary query. Then export that query. Then delete that temporary query.

Here is some sample code that may get you started; it assumes that you've
finished building the strWhere3 string:

Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code

--

Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from a
multi-select list box and outputs to a report based upon a query. What
I'm trying to do is to place another button on my form where the user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.

Private Sub ok_Click()

'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"

strReport = "rptAvgCostMile"
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

DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -

- Show quoted text -

Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:

DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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

I'll continue to try and figure this out!

 

[Ken Snell \(MVP\)]

strReport must contain a string that is the name of the query or table that
you want to export. It will not export a Report object. So, if you want to
export the data that a report would show, set strReport to the name of the
query that is the RecordSource for that report.

--

Ken Snell
<MS ACCESS MVP>

Using your strWhere3 string, build a full SQL statement and save it as a
temporary query. Then export that query. Then delete that temporary
query.

Here is some sample code that may get you started; it assumes that you've
finished building the strWhere3 string:

Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code

--

Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from a
multi-select list box and outputs to a report based upon a query. What
I'm trying to do is to place another button on my form where the user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.

Private Sub ok_Click()

'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"

strReport = "rptAvgCostMile"
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

DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -

- Show quoted text -

Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:

DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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

I'll continue to try and figure this out!

 

[Adam]

strReport must contain a string that is the name of the query or table that
you want to export. It will not export a Report object. So, if you want to
export the data that a report would show, set strReport to the name of the
query that is the RecordSource for that report.

--

Ken Snell
<MS ACCESS MVP>

Using your strWhere3 string, build a full SQL statement and save it as a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from a
multi-select list box and outputs to a report based upon a query. What
I'm trying to do is to place another button on my form where the user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -

Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:

DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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

I'll continue to try and figure this out!- Hide quoted text -

- Show quoted text -

Okay, I changed strReport to reference the query:

strReport = "qryAvgCostMile"
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

Unfortunately I'm still getting the same error, Run-time error 13,
type mismatch. Any suggestions?

 

[Ken Snell \(MVP\)]

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"


What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"


What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"


--

Ken Snell
<MS ACCESS MVP>

strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.

--

Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save it as
a
temporary query. Then export that query. Then delete that temporary
query.

Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:

Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code

Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from
a
multi-select list box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.

Private Sub ok_Click()

'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"

strReport = "rptAvgCostMile"
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

DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -

- Show quoted text -

Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:

DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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

I'll continue to try and figure this out!- Hide quoted text -

- Show quoted text -

Okay, I changed strReport to reference the query:

strReport = "qryAvgCostMile"
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

Unfortunately I'm still getting the same error, Run-time error 13,
type mismatch. Any suggestions?

 

[Adam]

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

--

Ken Snell
<MS ACCESS MVP>

strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.
--
Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save itas
a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9,_
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from
a
multi-select list box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause outof
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -
Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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
I'll continue to try and figure this out!- Hide quoted text -
- Show quoted text -

Okay, I changed strReport to reference the query:

strReport = "qryAvgCostMile"

...

read more »- Hide quoted text -

- Show quoted text -

Ahh, fixed that issue, now I'm getting, "Run-time error 2495, The
action or method requires a Table Name argument" I'm looking online
for that error, but most articles seem to point to incorrect spelling
of a table in the VBA which I don't think is right in this case.

 

[Adam]

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

Ken Snell
<MS ACCESS MVP>

news:[email protected]...

On Oct 16, 12:12 pm, "Ken Snell \(MVP\)"
strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.
--
Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save it as
a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selectionsfrom
a
multi-select list box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -
Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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
I'll continue to try and figure this out!- Hide quoted text -
- Show quoted text -
Okay, I changed strReport to reference the query:
strReport = "qryAvgCostMile"

...

read more »- Hide quoted text -

Show quoted text -

Ahh, fixed that issue, now I'm getting, "Run-time error 2495, The
action or method requires a Table Name argument" I'm looking online
for that error, but most articles seem to point to incorrect spelling
of a table in the VBA which I don't think is right in this case.

 

[Adam]

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

--

Ken Snell
<MS ACCESS MVP>

strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.
--
Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save itas
a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9,_
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from
a
multi-select list box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause outof
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -
Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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
I'll continue to try and figure this out!- Hide quoted text -
- Show quoted text -

Okay, I changed strReport to reference the query:

strReport = "qryAvgCostMile"

...

read more »- Hide quoted text -

- Show quoted text -

Ahh, fixed that issue, now I'm getting, "Run-time error 2495, The
action or method requires a Table Name argument" I'm looking online
for that error, but most articles seem to point to incorrect spelling
of a table in the VBA which I don't think is right in this case.

 

[Adam]

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

--

Ken Snell
<MS ACCESS MVP>

strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.
--
Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save it as
a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from
a
multi-selectlist box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in themulti-selectbox, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -
Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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
I'll continue to try and figure this out!- Hide quoted text -
- Show quoted text -

Okay, I changed strReport to reference the query:

strReport = "qryAvgCostMile"

...Ahh, fixed that issue, now I'm getting, "Run-time error 2495, The
action or method requires a Table Name argument" I'm looking online
for that error, but most articles seem to point to incorrect spelling
of a table in the VBA which I don't think is right in this case.

 

[Adam]

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

--

Ken Snell
<MS ACCESS MVP>

strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.
--
Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save it as
a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from
a
multi-selectlist box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in themulti-selectbox, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out of
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -
Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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
I'll continue to try and figure this out!- Hide quoted text -
- Show quoted text -

Okay, I changed strReport to reference the query:

strReport = "qryAvgCostMile"

...Ahh, fixed that issue, now I'm getting, "Run-time error 2495, The
action or method requires a Table Name argument" I'm looking online
for that error, but most articles seem to point to incorrect spelling
of a table in the VBA which I don't think is right in this case.

 

[Adam]

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

--

Ken Snell
<MS ACCESS MVP>

strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.
--
Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save itas
a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9,_
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections from
a
multi-select list box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report, only
I'm not familiar enough w/ VB to do that. Here is the working code I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause outof
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -
Thank you for the help. Now when I run the code, I get an error, "Run-
time Error '13' Type mismatch". Debugger opens to the line below which
is one I changed to try and output to an Excel file:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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
I'll continue to try and figure this out!- Hide quoted text -
- Show quoted text -

Okay, I changed strReport to reference the query:

strReport = "qryAvgCostMile"

...

read more »- Hide quoted text -

- Show quoted text -

Okay, I fixed that last error but now I'm getting Run-time error 2495,
The action or method requires a Table Name argument. I don't reference
any tables directly, only through the SQL, so I'm not sure what's
throwing it but it highlighted on:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

 

[Ken Snell \(MVP\)]

Do you have a query named "qryAvgCostMile" in your database file?

--

Ken Snell
<MS ACCESS MVP>

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

--

Ken Snell
<MS ACCESS MVP>

strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.
--
Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save it
as
a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9,
_
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections
from
a
multi-select list box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report,
only
I'm not familiar enough w/ VB to do that. Here is the working code
I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out
of
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -
Thank you for the help. Now when I run the code, I get an error,
"Run-
time Error '13' Type mismatch". Debugger opens to the line below
which
is one I changed to try and output to an Excel file:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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
I'll continue to try and figure this out!- Hide quoted text -
- Show quoted text -

Okay, I changed strReport to reference the query:

strReport = "qryAvgCostMile"

...

read more »- Hide quoted text -

- Show quoted text -

Okay, I fixed that last error but now I'm getting Run-time error 2495,
The action or method requires a Table Name argument. I don't reference
any tables directly, only through the SQL, so I'm not sure what's
throwing it but it highlighted on:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

 

[Adam]

Do you have a query named "qryAvgCostMile" in your database file?

--

Ken Snell
<MS ACCESS MVP>

Sorry - I missed the fact that you wrote the TransferSpreadsheet syntax
incorrectly.

What I posted as a generic example:
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"

What you posted:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What you should be using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

Ken Snell
<MS ACCESS MVP>

news:[email protected]...

On Oct 16, 12:12 pm, "Ken Snell \(MVP\)"
strReport must contain a string that is the name of the query or table
that
you want to export. It will not export a Report object. So, if you want
to
export the data that a report would show, set strReport to the name of
the
query that is the RecordSource for that report.
--
Ken Snell
<MS ACCESS MVP>

On Oct 12, 2:42 pm, "Ken Snell \(MVP\)"
Using your strWhere3 string, build a full SQL statement and save it
as
a
temporary query. Then export that query. Then delete that temporary
query.
Here is some sample code that may get you started; it assumes that
you've
finished building the strWhere3 string:
Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
EXCEL files
----------------------------------------------------------
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT * FROM ReportTable WHERE " & _
strWhere3 & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & "WhatDoIWantToCallThis"
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9,
_
strTemp, "C:\FolderName\" & "WhatIsTheFileName" & ".xls"
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
--
Ken Snell
<MS ACCESS MVP>

Team... I have some VB code in Access '03 that takes selections
from
a
multi-select list box and outputs to a report based upon a query.
What
I'm trying to do is to place another button on my form where the
user
can make the same selections in the multi-select box, but have the
command button output to an Excel file rather than the report,
only
I'm not familiar enough w/ VB to do that. Here is the working code
I
have which output to an existing report if anyone can assist:
I know I have to take the coding that builds the where clause out
of
the click event and put it in a function, just not sure how.
Private Sub ok_Click()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
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\#"
strReport = "rptAvgCostMile"
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
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
- Show quoted text -
Thank you for the help. Now when I run the code, I get an error,
"Run-
time Error '13' Type mismatch". Debugger opens to the line below
which
is one I changed to try and output to an Excel file:
DoCmd.TransferSpreadsheet strReport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"
Here is the complete code:
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()
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
strReport = "rptAvgCostMile"
DoCmd.TransferSpreadsheet strReport, 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
I'll continue to try and figure this out!- Hide quoted text -
- Show quoted text -
Okay, I changed strReport to reference the query:
strReport = "qryAvgCostMile"

...

read more »- Hide quoted text -

- Show quoted text -

Okay, I fixed that last error but now I'm getting Run-time error 2495,
The action or method requires a Table Name argument. I don't reference
any tables directly, only through the SQL, so I'm not sure what's
throwing it but it highlighted on:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"- Hide quoted text -

- Show quoted text -

Yes, I double checked the spelling: qryAvgCostMile. I also tried to
put the table name that SQL was referencing before the query name, but
it did not help so I removed the table name.

 

[Ken Snell \(MVP\)]

Do you have a query named "qryAvgCostMile" in your database file?

Okay, I fixed that last error but now I'm getting Run-time error 2495,
The action or method requires a Table Name argument. I don't reference
any tables directly, only through the SQL, so I'm not sure what's
throwing it but it highlighted on:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"- Hide quoted text -

- Show quoted text -

Yes, I double checked the spelling: qryAvgCostMile. I also tried to
put the table name that SQL was referencing before the query name, but
it did not help so I removed the table name.


Let's see if strReport is actually the string that you think it is. Add this
code line just before the TransferSpreadsheet step:

MsgBox "strReport= '" & strReport & "'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What do you see in the MsgBox when it appears?

 

[Adam]

Yes, I double checked the spelling: qryAvgCostMile. I also tried to
put the table name that SQL was referencing before the query name, but
it did not help so I removed the table name.

Let's see if strReport is actually the string that you think it is. Add this
code line just before the TransferSpreadsheet step:

MsgBox "strReport= '" & strReport & "'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

What do you see in the MsgBox when it appears?

The message box showed this first: strReport= "
Then when I hit okay, it gave me the runtime error 2495 again. Just as
a check, this is where I placed the code:

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"

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

 

[Adam]

The message box showed this first: strReport= "
Then when I hit okay, it gave me the runtime error 2495 again. Just as
a check, this is where I placed the code:

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"

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- Hide quoted text -

- Show quoted text -

Just a note, I had this line below in there once, not twice, and it
still gave me the same message, "strReport= "

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strReport, "C:\" & "AvgCostMile" & ".xls"

 

[Ken Snell \(MVP\)]

Change this line:
strReport = qryAvgCostMile

to this:
strReport = "qryAvgCostMile"

 

[Adam]

Change this line:
strReport = qryAvgCostMile

to this:
strReport = "qryAvgCostMile"

--

Ken Snell
<MS ACCESS MVP>










- Show quoted text -

Okay, did that, then it threw Runtime Error 13, type mismatch.

 

[Ken Snell \(MVP\)]

on which line did it throw the error?