Help with Query to Excel via Access (1 Viewer)

C

china99boy

Registered User.
Local time
Today, 15:21
Joined
Apr 27, 2006
Messages
161
Okay, I tried to tackle it on my own. But I think I am confusing myself. I copied the sql from my query and used that as the strSQL, but I get a compile error. I need the user to be able to select a date or between dates. How can change the vba coding to reflex this.

What I am trying to accomplish is, when the user select a date or date range and then a department it will export only that department data to the appropriate tab in the excel workbook. But if they select "All", it will export all departments. The call center department will needs to be broken down by location 801 & 3808 and position AO & CCR for each, while the other departments 004 and 007, should be divided by AO and FSO.

I added my dba and the code I currently working with in command button.

Thanks for any help provided.

Code: 
Private Sub Command36_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strDate As String
Dim strPosition As String
Dim strDepartment As String
Dim strLocation As String

strDate = [Forms]![frmAgentSummaryExport]![txtBusinessDate]

Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]

strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\user1\Desktop/Temp.xlsx")

Case "Call Center"
    strDepartment = "Call Center"
    
Case "004"
    strDepartment = "004"
    
Case "007"
    strDepartment = "007"
    
Case Else ' Export All
    strDepartment = "Call Center"
   strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
    Set ws = wb.Worksheets(strDepartment)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").CopyFromRecordset rs

    strDepartment = "004"
    strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
    Set ws = wb.Worksheets(strDepartment)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").CopyFromRecordset rs
    
    strDepartment = "007"
    strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
    Set ws = wb.Worksheets(strDepartment)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").CopyFromRecordset rs
        
    GoTo ExitMySub
         
End Select

    strSQL = strSQL & " Where VolumeCat = '" & strDepatment & "'"
        Set ws = wb.Worksheets(strDepartment)
        Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").CopyFromRecordset rs

ExitMySub:
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
 

Attachments

  • Historical Reporting.accdb
    608 KB · Views: 95
Last edited:
L

lman

New member
Local time
Today, 15:21
Joined
Feb 22, 2011
Messages
9
What the error is telling you is you can't have any statements between your SELECT CASE and CASE "Call Center" statements. so the following lines of code are invalid

Code: 
Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]

strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\user1\Desktop/Temp.xlsx")

Case "Call Center"
    strDepartment = "Call Center"

your will have to move the StSQL, Set Xlapp and Set wb statements, can you execute them before you begin your SELECT CASE statement? So your code will look like this instead

Code: 
strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\user1\Desktop/Temp.xlsx")

Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]

Case "Call Center"
    strDepartment = "Call Center"
 
Last edited:
C

china99boy

Registered User.
Local time
Today, 15:21
Joined
Apr 27, 2006
Messages
161
Thank you Iman, made the change and was able to compile ok. I now get a Run-time error '3078': The Microsoft Jet database engine can't find the input table or query: qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab: Make sure it exits and that its......... It stops here so I can't see the remainder of the error. It debugs at the following line of code.

Code: 
Case Else ' Export All
    strDepartment = "Call Center"
   strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
    Set ws = wb.Worksheets(strDepartment)
    [COLOR="RED"]Set rs = CurrentDb.OpenRecordset(strSQL)[/COLOR]    ws.range("A2").CopyFromRecordset rs(strSQL)


Below is my current code for the command button.

Code: 
Private Sub Command36_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strDate As String
Dim strPosition As String
Dim strDepartment As String
Dim strLocation As String

strDate = [Forms]![frmAgentSummaryExport]![txtBusinessDate]

strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\user1\Desktop/Temp.xlsx")

Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]

Case "Call Center"
    strDepartment = "Call Center"
    strPosition = "AO"
    strLocation = "801"
    
Case "004"
    strDepartment = "004"
    
Case "007"
    strDepartment = "007"
    
Case Else ' Export All
    strDepartment = "Call Center"
   strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
    Set ws = wb.Worksheets(strDepartment)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").CopyFromRecordset rs

    strDepartment = "004"
    strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
    Set ws = wb.Worksheets(strDepartment)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").CopyFromRecordset rs
    
    strDepartment = "007"
    strSQL = "qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web FROM qryAgentSummary_Crosstab;"
    Set ws = wb.Worksheets(strDepartment)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").CopyFromRecordset rs
        
    GoTo ExitMySub
         
End Select

    strSQL = strSQL & " Where Department = '" & strDepatment & "'"
        Set ws = wb.Worksheets(strDepartment)
        Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").CopyFromRecordset rs

ExitMySub:
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
 
DCrake

DCrake

Remembered
Local time
Today, 20:21
Joined
Jun 8, 2005
Messages
8,626
You are not prefixing your strSQL statements with SELECT
 
C

china99boy

Registered User.
Local time
Today, 15:21
Joined
Apr 27, 2006
Messages
161
Your correct. Thanks for pointing that out. Really trying to do as much without help. I now get Runtime 3061 : Too few parameters......This happens on the same line of code as previous post.

Why would it even stop on this section when I haven't even selected the "All" on the drop down form before clicking the command button? I only want it to export All criteria only when that "All" is selected. Am I going about this process completely incorrectly?

Thanks
 
DCrake

DCrake

Remembered
Local time
Today, 20:21
Joined
Jun 8, 2005
Messages
8,626
Code: 
strSQL = "[COLOR="Blue"]Select[/COLOR] [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM [COLOR="Red"]qryAgentSummary_Crosstab[/COLOR];"

You only need to refer to the source once if there is only one source object.
 
C

china99boy

Registered User.
Local time
Today, 15:21
Joined
Apr 27, 2006
Messages
161
Thanks Dcrake. Learning as I go. Should that change fix my problem? I still get the same error.
 
C

china99boy

Registered User.
Local time
Today, 15:21
Joined
Apr 27, 2006
Messages
161
Hi All,

I made some changes to my code with the help from another user on this forum. I can't seem to figure out how to fix my code to make the user enter a single date or a date range. Also trying to get it to allow the user to select "Call Center, 004, 007, 216, or All" I continue to recieve Runtime 3061 : Too few parameters. Below is my updated code.

Thanks for everyone that help with this.

Code: 
Private Sub Command36_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strDate As String
Dim strPosition As String
Dim strDepartment As String
Dim strLocation As String
Dim strVolCat As String

Dim asRef(3) As String
Dim i As Integer
Dim bCopyAll As Boolean ' True equals - Copy All
    bCopyAll = False
    

strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"
Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\s11979n\Desktop\Temp.xlsx")


    asRef(0) = "Call Center"
    asRef(1) = "004"
    asRef(2) = "007"
    asRef(3) = "216"
    
    Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]
    Select Case [Forms]![frmAgentSummaryExport]![txtBusinessDate]
    

    Case "Call Center"
        strDepartment = "Call Center"
        strPosition = "AO"
        strPosition = "CCR"
    
    
    Case "004"
        strDepartment = "004"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case "007"
        strDepartment = "007"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case "216"
        strDepartment = "216"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case Else ' Copy ALL
        bCopyAll = True
    
    End Select
    
        For i = 0 To 3
            If bCopyAll = True Then
                If i = 0 Then
                    strDepartment = asRef(i)
                Else
                    strDepartment = strDepartment & ", " & asRef(i)
                End If
            Else
                i = 3
            End If
            
            strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"
                        
            Set ws = wb.Worksheets(strDepartment)
            Set rs = CurrentDb.OpenRecordset(strSQL)
            ws.range("A2").CopyFromRecordset rs
    
        Next i

        bCopyAll = False
        
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xlapp = Nothing

End Sub
 
C

china99boy

Registered User.
Local time
Today, 15:21
Joined
Apr 27, 2006
Messages
161
I got some help on another forum, Thanks for all the help.
 
You must log in or register to reply here.

Similar threads

zelarra821
Solved Export to Excel and leave the workbook open
Replies
5
Views
292
zelarra821
zelarra821
C
Solved i want to change this code to merge two excel files to one sheet side by side.
Replies
2
Views
309
camiramzi
C
Sam Summers
Inserting subform/Table data into specific fields in Excel
2
Replies
22
Views
1,639
Gasman
Gasman
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,216
Cotswold
Cotswold
M
Export multiple access query to same workbook two sheets
Replies
9
Views
736
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom