the critieria for exporting single query to multiple worksheets

[ice051505]

Hi dear All, I am trying to export single query "DivideGroup" to multiple excel worksheets, however the critieria I transfered to my "DivideGroup" is In (""),


I wanted critieria is In my first record in RecordExcel.grouping like In("bike"), where bike is the first record in "RecordExcel" query, and then second record and so on...

Can anyone take a look at my code here? Thanks a lot!!!

Private Sub ExprDetail_Click()
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim rst As Recordset
Dim recordCnt As Integer
Dim recordNm As Integer
Dim sql As String
Dim arrStr As String
Dim arrCount() As String

Const JOIN_SEP = ""","""
arrStr = """" & Join(arrCount, JOIN_SEP) & """"

Set db = CurrentDb()
Set qdf1 = db.QueryDefs("RecordExcel")
Set qdf2 = db.QueryDefs("DivideGroup")
Set rst = qdf1.OpenRecordset

recordCnt = rst.RecordCount
ReDim arrCount(1 To recordCnt) As String

For recordNm = 1 To recordCnt
   arrCount(recordNm) = rst!grouping
   sql = "TRANSFORM Sum(Format_Step3.SumOfCatch) AS SumOfSumOfCatch " _
        & "SELECT (Format_Step3.TimeGroup)As Week, (RecordExcel.grouping) As Species FROM Format_Step3 INNER JOIN RecordExcel ON " _
        & "Format_Step3.grouping = RecordExcel.grouping WHERE (((RecordExcel.grouping) In (" & arrStr & ")))GROUP BY Format_Step3.TimeGroup, " _
        & "RecordExcel.grouping PIVOT Format_Step3.ClnVar;"
    
   qdf2.sql = sql
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DivideGroup", "C:\Documents and Settings\WangA\My Documents\Export_Excel_Files.xls", True, arrCount(recordNm)
    rst.MoveNext
Next recordNm
    
    
End Sub

[pbaldy]

Not how I would do it, but arrStr is set before it contains anything.

[ice051505]

Right...I try to put

arrStr = """" & Join(arrCount, JOIN_SEP) & """"

in between

 arrCount(recordNm) = rst!grouping

          arrStr = """" & Join(arrCount, JOIN_SEP) & """"

           sql = "TRANSFORM Sum(Format_Step3.SumOfCatch) AS SumOfSumOfCatch " _
        & "SELECT (Format_Step3.TimeGroup)As Week, (RecordExcel.grouping) As Species FROM Format_Step3 INNER JOIN RecordExcel ON " _
        & "Format_Step3.grouping = RecordExcel.grouping WHERE (((RecordExcel.grouping) In (" & arrStr & ")))GROUP BY Format_Step3.TimeGroup, " _
        & "RecordExcel.grouping PIVOT Format_Step3.ClnVar;"

It give me that my sql is error and expect INSECT, SELECT.....but what I need here is crosstab query.

So if this is not a good way to do it, would you please give me some idea of how do you think? Thanks a lot!

Not how I would do it, but arrStr is set before it contains anything.

[pbaldy]

To fix this I'd use this technique to check the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

As to how I'd do it, presuming the first recordset is returning a set of values you want filtered on, I'd get rid of the array and simply use the recordset:

"...WHERE RecordExcel.grouping ='" & rst!grouping & "' GROUP BY..."

[ice051505]

Thank you for the website, it is very useful and I fixed the SQL problem
However, I still have a question on the rst!grouping part, I need to transfer the different grouping to the different worksheets in one excel file, could you provide me some solution or web source show me how to do this in VBA? MANY MANY THANKS!!!

To fix this I'd use this technique to check the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

As to how I'd do it, presuming the first recordset is returning a set of values you want filtered on, I'd get rid of the array and simply use the recordset:

"...WHERE RecordExcel.grouping ='" & rst!grouping & "' GROUP BY..."

[pbaldy]

I know I've exported multiple queries to the same file name with TransferSpreadsheet and had it create multiple sheets. For more control, automation:

http://support.microsoft.com/kb/247412

[ice051505]

Thanks pbaldy, but I didn't find any multiple queries transfer to the same file in the webset,
I use
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DivideGrp", "C:\Documents and Settings\WangA\My Documents\Export_Excel_Files\FNexcel.xls", True, rst!grouping

in my VBA code, but it gives me nothing, nothing happened when I click the export button... Do you think there is anything wrong with my code?
Again, Thank you so much!

I know I've exported multiple queries to the same file name with TransferSpreadsheet and had it create multiple sheets. For more control, automation:

http://support.microsoft.com/kb/247412

[pbaldy]

The last argument is for a range, so I don't think you're providing that. I'd try without.

[ice051505]

I delete the range but my data still transfered on one worksheet, I try to put the code: "Set qdf2=Nothing" above "rst.MoveNext", and my access give me error that the Object variable or With block variable not set...

Thank you so much for your patient....I am so despered now.....

The last argument is for a range, so I don't think you're providing that. I'd try without.

[pbaldy]

Did you get this sorted? If memory serves, you can specify a sheet in the range argument like

"SheetName!"

[ice051505]

Sorry, I was out for meeting the whole week and wasn't able to reply you in time. I got the code work but now my supervisor want me to put a title in the cell A1 and push all the data start from cell A2 in excel...So I think I may need to change my code a lot to accomplish that....
Also, if you have any idea on how to do it, any hits would be appreciate, Thanks a lot!!!

Did you get this sorted? If memory serves, you can specify a sheet in the range argument like

"SheetName!"

[pbaldy]

Your options include modifying the query so it outputs the title first (perhaps a UNION query that adds that above the other query), and using automation to either do the whole process, or open the spreadsheet after running the existing process and inserting a row at the top. Automation gives you a lot of control:

http://support.microsoft.com/kb/247412

[ice051505]

Thanks for the quick reply, I will dig into the automation and post my answer once I figure it out. Thanks again

Your options include modifying the query so it outputs the title first (perhaps a UNION query that adds that above the other query), and using automation to either do the whole process, or open the spreadsheet after running the existing process and inserting a row at the top. Automation gives you a lot of control:

http://support.microsoft.com/kb/247412

[pbaldy]

No problem, post back if you get stuck.

[ice051505]

Hi, hate to bother you, but I did got stucked on how to code the automation
Here is my code:

Private Sub ExprDetail_Click()
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim qdf3 As DAO.QueryDef
Dim rst As Recordset
Dim recordCnt As Integer
Dim recordNm As Integer
Dim sql As String
Dim sqlFull As String
Dim arrStr As String
Dim arrCount() As String
Dim path As String
Dim strPrmp As String
Dim strttl As String
Dim DflPath As String
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

DflPath = "C:\Documents and Settings\WangA\My Documents\Export_Excel_Files\FNexcel.xls"
strPrmp = "Would you like to name your Excel File?"
strttl = "Excel export"
path = InputBox(strPrmp, strttl, DflPath)
 
Const JOIN_SEP = ""","""

Set db = CurrentDb()
Set qdf1 = db.QueryDefs("RecordExcel")
Set rst = qdf1.OpenRecordset
recordCnt = rst.RecordCount

'Creat array for transfer excel
ReDim arrCount(1 To recordCnt) As String
rst.MoveFirst
For recordNm = 1 To recordCnt
    Set qdf2 = db.QueryDefs("DivideGroup")
    Set qdf3 = db.QueryDefs("ExportFullWeek")
    
    arrCount(recordNm) = rst!grouping
    arrStr = """" & arrCount(recordNm) & """"
          sql = "TRANSFORM Sum(Format_Step3.SumOfCatch) AS SumOfSumOfCatch SELECT Format_Step3.TimeGroup AS [Date] " _
           & "FROM Format_Step3 INNER JOIN RecordExcel ON Format_Step3.grouping = RecordExcel.grouping WHERE (((RecordExcel.grouping) In (" & arrStr & "))) " _
           & "GROUP BY Format_Step3.TimeGroup, RecordExcel.grouping ORDER BY Format_Step3.TimeGroup PIVOT Format_Step3.ClnVar;"
      
    qdf2.sql = sql
    sqlFull = "SELECT weeks.Date AS [Week Ending], DivideGroup.* FROM DivideGroup RIGHT JOIN weeks ON DivideGroup.Date = weeks.date " _
             & "WHERE (((weeks.year) Between Year(getstartdate()) And Year(getenddate()))) ORDER BY weeks.Date;"
    qdf3.sql = sqlFull
       
    'Add headers to the worksheet on row 1
    Set oSheet = oBook.Worksheets(recordNm)
    oSheet.Range("A1:E1").Value = "Add Title Test"
    
    'Transfer the Array to the worksheet starting at cell A2
    oSheet.Range("A2:Z1000").Value = qdf3
  
    oBook.SaveAs path
    oExcel.Quit

    rst.MoveNext
    qdf2.Close
    qdf3.Close
    
    Set qdf2 = Nothing
    Set qdf3 = Nothing
   
Next recordNm

End Sub

The line in red always give me error said there is an invalid argument there. I am so not familiar with automation, Would you please have a look at my code and tell me how can I fix it? Thanks!!!!

No problem, post back if you get stuck.