export access into separate excel worksheet by value

[Uriya Levy]

Hi all,
Im seeking the web for a long time and found nothing to help my case...
I have 1 table called 'Schollership' with fields like 'Scholler_name','Entitled','info' and more...
I would like to export this table into one spreadsheet excel with separaded worksheets by 'Entitled' field.
for Example:
if the table looks like-
Entitled1,Scholler_name1,info1...
Entitled1,Scholler_name2,info2...
Entitled2,Scholler_name3,info3...
---
the spreadsheet will contain two worksheets: Entitled1,Entitled2 and will have all the fields assosiated with it.

Thanks for any reply...

[NeoPa]

You will need to use DoCmd.TransferSpreadsheet() for each worksheet. The data for each sheet must be that produced by a query of the same name as you require for the sheet, so the query should have a WHERE clause to match the required data. As the queries are all similar in other respects though, it may make sense to use a query template and simply create (and subsequently delete) each query as you need it.

[Uriya Levy]

Thank you NeoPa for reply,
I've looked at DoCmd.TransferSpreadsheet() command manual (http://msdn.microsoft.com/en-us/libr...ffice.10).aspx)
but Im little confused how to use it...Im really a novice in all of this...
can you give me an example using the data example I've post in the start of the thread?

assuming I have 20 "Entitled" so I will have to create 20 queries that each one of them will give me one of the Entitled?
and then I'll run:

DoCmd.TransferSpreadsheet acExport,,[query_name],"C:\try.xlsx", True

thanks,

[NeoPa]

I will assume [Entitled] is a string field (You don't say in your post). If it's a numeric field then the literal value in the WHERE clause will need to be lose the single-quotes (' - See Quotes (') and Double-Quotes (") - Where and When to use them).

We start off then, with the assumption that you already have a query template (I'll refer to it as [qryTemplate]) that has SQL of :

SELECT [Entitled]
     , [Scholler_Name]
     , [Info]
FROM   [Schollership]

At this point I'll show the procedure wrapper. The code that surrounds the code that needs to execute (represented here simply as ...). I'll assume you're running this behind a Command Button on a form (called cmdExport) :

Option Compare Database
Option Explicit
 
Private Sub cmdExport_Click()
    Dim strEnts As String, strSQL As String, strFName As String
    Dim cdb As DAO.Database
    Dim varEnt As Variant
 
    On Error GoTo Error_cmdExport
    '...
    Exit Sub
 
Error_cmdExport:
    Call MsgBox(Prompt:="Error {" & Err & "}" & vbNewLine & Err.Description, _
                Buttons:=vbOKOnly Or vbExclamation, _
                Title:="cmdExport")
End Sub

The first step of the process is to initialise any variables that require it :

    strFName = "C:\Try.xlsx"
    strEnts = ""

The next step is to create a list of the values of [Entitled] from the table. As the length of the list is not known in advance using standard arrays would be cumbersome, so I'll illustrate using a string instead, which is naturally flexible in length (up to 65536 characters approx) :

    strSQL = "SELECT DISTINCT [Entitled] FROM [Schollership]"
    With cdb.OpenRecordset(Name:=strSQL, Type:=dbOpenSnapshot)
        Call .MoveFirst
        Do While Not .EOF
            strEnts = strEnts & "," & !Entitled
            Call .MoveNext
        Loop
        Call .Close
    End With
    strEnts = Mid(strEnts, 2)

Now we have the list of the existing values for [Entitled] we can create some looping code that will do the work for all of them (This part is pretty straightforward in itself) :

    For Each varEnt In Split(strEnts, ",")
        ...
    Next varEnt

The payload is in the last piece of code that fits in there in place of the ellipsis (...) :

        Call DoCmd.CopyObject(NewName:=varEnt, _
                              SourceObjectType:=acQuery, _
                              SourceObjectName:="qryTemplate")
        Set cdb = CurrentDb
        With cdb.QueryDefs(varEnt)
            strSQL = Replace(.SQL, ";", "") & "WHERE [Entitled] = '%E'"
            .SQL = Replace(strSQL, "%E", varEnt)
            Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                           TableName:=varEnt, _
                                           Filename:=strFName, _
                                           HasFieldNames:=True)
        End With
        Call DoCmd.DeleteObject(Objecttype:=acQuery, ObjectName:=varEnt)
        Set cdb = Nothing

[NeoPa]

While testing the code outlined in the previous post (#4) I discovered, to my considerable surprise, that the cdb object that had previously been set to CurrentDb (The original version of the post had cdb set once only at the initialisation stage), was not able to reference the newly created queries in the database. To get around this I had to ensure cdb was set every time through the loop after an object had been created. CurrentDb() called after the creation of the object (and therefore cdb set afterwards) was able to reference the new object.

I developed and tested this in Access 2003, so I cannot confirm everything will be perfectly smooth-running in later versions, but I see no reason to assume otherwise.

[Uriya Levy]

thank you,I will check it right away...I'll post the results

[Uriya Levy]

Thank you!!
it works!
I did some minor adjustments but nothing serious.
it works as a charm..thank you again...

[NeoPa]

Pleased to hear it Uriya :-)