Trying to create .XLS based on field 1

[platypus71]

I haven't a clue how to do this, if it is even possible.

I have an existing query that generates a large report. This report then gets split into smaller files based on the field [Reporting Level3].

If it is possible, how would one go about creating the various .XLS files based on the results of field [Reporting Level3] (this is a person's name)?

The end result should be 15 files named PersonName.xls where PersonName is the results of [Reporting Level3].

I'm at a total loss here, but would like to be able to help out the person who does the manual breakdown of the large file.

 

[Remou]

You could use the existing query to build a new query that you can use with TransferSpreadsheet. Very roughly:

strSQL="SELECT DISTINCT [Reporting Level3] FROM qry"

Set rs=CurrentDB.OpenRecordset(strSQL)

While Not rs.EOF
'Assuming numeric [Reporting Level3].
    strSQL="SELECT * FROM qry WHERE [Reporting Level3]=" & rs![Reporting Level3]

    If DLookup("Name", "MSysObjects", "Name= 'qryNew'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("qryNew")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("qryNew", strSQL)
    End If
    
    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"qryNew",rs![Reporting Level3] & ".xls",True
    
    rs.MoveNext
Loop

 

[platypus71]

Stupid question #1 to add to this:

When you enter in your strSQL line, how do you type in more text than one line allows? When I hit enter, it puts an end-quote on the line. I am still working on this, but the first problem I ran into was that.

 

[Remou]

strSQL="This line is too long , so I will add these " _
& " and continue here."

strSQL="This line is too long , so I will "
strSQL = strSQL & "create multiple lines."