the critieria for exporting single query to multiple worksheets

[pbaldy]

Not sure you can set it that way, at least with a QueryDef. I open a recordset on the source data (the QueryDef in your case) and do this:




oSheet.range("A2").CopyFromRecordset rst

[ice051505]

I modified my code by using the recordset automation method, but I keep getting error: Operation is not allowed when the object is closed

Here is my code again: blue line indicate the modify I have made in my code, and res line is where the error debug locate. Again, Thanks a lot for you patient and help

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 conn As New ADODB.Connection
Dim rs As Recordset
conn.CursorLocation = adUseClient

'Create a new workbook in Excel
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
          
 Set rs = conn.Execute("ExportFullWeek", , adCmdTable) <---Error is here
   '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").CopyFromRecordset rs
  
   'Save the Workbook and Quit Excel
   oBook.SaveAs path
   oExcel.Quit
   
   rst.MoveNext
   qdf2.Close
   qdf3.Close
    
   Set qdf2 = Nothing
   Set qdf3 = Nothing

Next recordNm
  
End Sub

Not sure you can set it that way, at least with a QueryDef. I open a recordset on the source data (the QueryDef in your case) and do this:


oSheet.range("A2").CopyFromRecordset rst

[pbaldy]

Curious why you didn't open the recordset the way you did the others, and why Execute rather than Open. Also, you should disambiguate the recordset declarations, to avoid any potential confusion between ADO and DAO (though that may not be the cause of this error).

[ice051505]

That is exactly the part I am confused, Do you mean I can use DAO declaration for the whole code? Since I thought the automation can be only referenced by ADO follow the website you provide to me. Also, do you know if I need to change the connection: [conn.CursorLocation = adUseClient] if I want to use DAO reference?

Curious why you didn't open the recordset the way you did the others, and why Execute rather than Open. Also, you should disambiguate the recordset declarations, to avoid any potential confusion between ADO and DAO (though that may not be the cause of this error).

[pbaldy]

Either will work; I normally use DAO. I suspect they wrote that KB when they were on their ADO kick. I'd just open it the way you did the other:

Set rst = qdf1.OpenRecordset

since it appears the recordset is based on that query.

[ice051505]

Thanks a lot for the helpful directions

I will give a try on DAO now, and will post back it if I got stuck or success


Thanks again for your patient!

Either will work; I normally use DAO. I suspect they wrote that KB when they were on their ADO kick. I'd just open it the way you did the other:

Set rst = qdf1.OpenRecordset

since it appears the recordset is based on that query.

[ice051505]

I almost made it, Now I successfully export the data I want to excel with multiple sheet in specific range, the only problem now is the format after transfer the first worksheet is changed. In the first worksheet, the first column is in date format, but in the second and third worksheet the format change to general number itself. I try to use xlWS.Cells(1000, 1).NumberFormat = "dd/mm/yyyy" to rewrite the all column, but it does not work... Do you mind take a look into my code below? Thanks a lot!

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 DAO.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 rs As DAO.Recordset
'Create a new workbook in Excel
Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

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)

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
Set xlWB = xlApp.Workbooks.Add

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
    
Set rs = qdf3.OpenRecordset
Dim i As Integer
Dim iNumCols As Integer
Dim rowNum As Double
iNumCols = rs.Fields.Count

Set xlWS = xlWB.Worksheets(recordNm)
xlWS.Range("A1").Value = "Summary of First Nations Species catch by area between year (" & intstart & ")"
xlWS.Range("A1").Font.Bold = True
   
For rowNum = 0 To iNumCols - 1
xlWS.Cells(2, rowNum + 1).Value = rs.Fields(rowNum).Name
Next
xlWS.Cells(1000, 1).Format = "dd/mm/yyyy"
xlWS.Cells(1000, 2).Format = "dd/mm/yyyy"  '<---I try to use the code to change the format here
xlWS.Range("A3").CopyFromRecordset rs
xlWS.Name = arrCount(recordNm)
   
 rst.MoveNext
qdf2.Close
qdf3.Close
   
Set qdf2 = Nothing
Set qdf3 = Nothing
Set rs = Nothing
   
Next recordNm
xlWB.SaveAs (path)
    
End Sub

Either will work; I normally use DAO. I suspect they wrote that KB when they were on their ADO kick. I'd just open it the way you did the other:

Set rst = qdf1.OpenRecordset

since it appears the recordset is based on that query.

[pbaldy]

Have to run out, but a trick I use is to record a macro doing what you want in Excel, then look at the code created. It will require tweaking to use the Excel objects from Access, but it should give you most of what you want. Try that and post back if you're still stuck. I'll have time later to experiment.

[ice051505]

I haven't try any macro in access to control Excel before, or do you mean build macro in Excel? I am trying to google some information on it, but it would be appreciate if you have time to give me more direction on this part. Thank you soooo much for being so much patient with me all the time :P

Have to run out, but a trick I use is to record a macro doing what you want in Excel, then look at the code created. It will require tweaking to use the Excel objects from Access, but it should give you most of what you want. Try that and post back if you're still stuck. I'll have time later to experiment.

[pbaldy]

I mean in Excel. Depends on version, but in 2010 which I have on this PC, it's on the View tab. In the macro area you'd start recording a macro. Go through your formatting process, then stop the macro, hit F11 to open the VBA window and check out the code. Like I said, you'll have to adapt it. In Excel it may look like

    Columns("C:C").Select
    Selection.NumberFormat = "m/d/yy;@"

and in Access you'd need to add the Excel variable:

    xlApp.Columns("C:C").Select
    xlApp.Selection.NumberFormat = "m/d/yy;@"

Warning, total air code.

[ice051505]

The only thing I am confused is that my excel file is created by the Access process and the name of excel also can be changed during the process, in this case, how should I do the code in Excel? Sorry for so many questions...lol

I mean in Excel. Depends on version, but in 2010 which I have on this PC, it's on the View tab. In the macro area you'd start recording a macro. Go through your formatting process, then stop the macro, hit F11 to open the VBA window and check out the code.

[pbaldy]

I'm not suggesting actually doing the process in Excel, I'm suggesting using Excel to learn what the appropriate code is, then incorporating that into your Access code. You're going to do this on an Excel file then delete the file.

[ice051505]

I finally got what you mean here...lol...Sorry for take such long time and thank you for your explaination :P

Also, do you have any idea how to add worksheets to one workbook, since the default worksheets number is three and I need maybe 5 to 10 worksheets in one workbook, so I need to change the default worksheets number, is that need to be done in macro?

Sorry for so many questions...Thanks a lot for your help!

I'm not suggesting actually doing the process in Excel, I'm suggesting using Excel to learn what the appropriate code is, then incorporating that into your Access code. You're going to do this on an Excel file then delete the file.

[pbaldy]

If you want to change the actual default, that's in the Options area. If you want to add sheets to the workbook you're creating, I'd use the same technique to see how Excel does it then adapt it to your Access code.