how to : export to excel sheet with where condition

[siamandm]

hello All,
i have a where condition to filter data from a query , used to show the result on report,
but now I want to export the filter data to an excel sheet using this code below ... i don't know how to embed the where condition used shown below:

exportToExcel.jpg

[HansV]

Could you post the code as text instead of as a screenshot? That will make it easier to help you.

[siamandm]

thank you Hans
here it is :

Code: Select all

Private Sub cmdExportExcel_Click()
Const strTemp = "TempQuery" ' or whatever you like
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set dbs = CurrentDb
    ' Get the SQL of the existing query
    Set qdf = dbs.QueryDefs(strReportName & "Query")
    strSQL = qdf.SQL
    ' Now modify strSQL
    strSQL = "select * from qryCal Where "
    ' Create a new temporary query with the modified SQL string
    Set qdf = dbs.CreateQueryDef(Name:=strTemp, SQLText:=strSQL)
    ' Export the temporary query to Excel
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:=strTemp, _
        FileName:=strFilePath & ".xlsx"
    ' Delete the temporary query
    dbs.QueryDefs.Delete strTemp

 
End Sub

Private Sub cmdPrtRpt_Click()
        
            Dim strWhere As String
                        
            If Not IsNull(Me.cboEmployeeFullName) Then
            strWhere = "EmployeeFullName = '" & Me.cboEmployeeFullName.Column(1) & "'"
            End If
            If Not IsNull(Me.txtStartDate) Then
            strWhere = strWhere & " AND WorkingDate>=#" & Format(Me.txtStartDate, "mm/dd/yyyy") & "#"
            End If
            
            If Not IsNull(Me.txtEndDate) Then
            strWhere = strWhere & " AND WorkingDate<=#" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"
            End If
            
          DoCmd.OpenReport REPORTNAME:="rptEmployee", view:=acViewReport, WhereCondition:=strWhere

End Sub

[HansV]

Here you go:

Code: Select all

Private Sub cmdExportExcel_Click()
    Const strTemp = "TempQuery" ' or whatever you like
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strWhere As String
    ' Assemble strSQL
    strSQL = "SELECT * FROM qryCal"
    strWhere = GetWhere
    If strWhere <> "" Then
        strSQL = strSQL & " WHERE " & strWhere
    End If
    ' Create a new temporary query with the SQL string
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef(Name:=strTemp, SQLText:=strSQL)
    ' Export the temporary query to Excel
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:=strTemp, _
        Filename:=strFilePath & ".xlsx"
    ' Delete the temporary query
    dbs.QueryDefs.Delete strTemp
End Sub

Private Sub cmdPrtRpt_Click()
    DoCmd.OpenReport REPORTNAME:="rptEmployee", View:=acViewReport, WhereCondition:=GetWhere
End Sub

Private Function GetWhere() As String
    Dim strWhere As String
    If Not IsNull(Me.cboEmployeeFullName) Then
        strWhere = " AND EmployeeFullName = '" & Me.cboEmployeeFullName.Column(1) & "'"
    End If
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & " AND WorkingDate>=#" & Format(Me.txtStartDate, "mm/dd/yyyy") & "#"
    End If
    If Not IsNull(Me.txtEndDate) Then
        strWhere = strWhere & " AND WorkingDate<=#" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"
    End If
    If strWhere <> "" Then
        GetWhere = Mid(strWhere, 6)
    End If
End Function

[siamandm]

thank you very much for the reply
after running the code i got an error saying that the strFilePath is not defined as a variable so i have defined as below

Code: Select all

dim strFilepath as string

after this I'm getting this error:

error.PNG

[HansV]

You will also have to assign a value to the variable strFilePath - it should be the path and filename of the Excel file, without the extension .xlsx.

Delete the query TempQuery manually. You have to do this only once. After that, the code should work, since it deletes the query at the end of the procedure.

[siamandm]

Thank you very much for the reply, now its working fine, but I have another question, please

now I'm using this code for the file path

Code: Select all

 strFilePath = "C:\Users\" & Environ("username") & "\Documents\"

now what I want is when I export the file as an excel sheet the file name will be the selected employee name from cboEmployeefull name

Code: Select all

FileName:=strFilePath & strEmployeeName & ".xlsx"

I tried this but didn't work

Code: Select all

 
 dim strEmployeeName as String
 strEmployeeName = Me.cboEmployeeFullName(1)

[HansV]

Use

Code: Select all

 strEmployeeName = Me.cboEmployeeFullName.Column(1)

[siamandm]

thank you very much