Using values to create spreadsheets and group data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table... items. With fields... id, title, container, etc... I was
looking for a way to export the data into Excel, where each value in the
container field creates a spreadsheet with that name. Then, all of the items
with that container would be exported to that container's spreadsheet. So
all the items are grouped by spreadsheets with the container as the
spreadsheet name. I would perfer to export with programming, but keep in
mind, I am not a pro :)
 
Please see this previous post, which accomplishing the same type of
operation.

http://groups.google.com/group/micr...sferspreadsheet&rnum=7&hl=en#80392e0b2d302cb6

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a table... items. With fields... id, title, container, etc... I was
looking for a way to export the data into Excel, where each value in the
container field creates a spreadsheet with that name. Then, all of the
items
with that container would be exported to that container's spreadsheet. So
all the items are grouped by spreadsheets with the container as the
spreadsheet name. I would perfer to export with programming, but keep in
mind, I am not a pro :)
 
David,
Thanks for the link, it pointed me in the right direction and taught me how
the transferspreadsheet command worked. If you could help me understand how
to modify the code to do something similar, it would be greatly appreciated.
OK... here is my current code:

Public Function ExportSheets() As Boolean
Dim db As Database
Dim sSQL As String
Dim rs As Recordset
Dim qdf As QueryDef

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT DISTINCT ItemID FROM tblItems;")

Do Until rs.EOF
Set qdf = db.CreateQueryDef()
sSQL = "SELECT tblItems.* FROM tblItems WHERE tblItems.ItemID=" &
rs("ItemID") & ";"
qdf.SQL = sSQL
qdf.Name = "ItemID" & rs("ItemID")
db.QueryDefs.Append qdf
Application.RefreshDatabaseWindow
DoCmd.TransferSpreadsheet acExport, , qdf.Name, "H:\TestExport.xls"
db.QueryDefs.Delete qdf.Name
Application.RefreshDatabaseWindow
Set qdf = Nothing
rs.MoveNext
Loop

ExportSheets = True

Set rs = Nothing
Set db = Nothing

End Function

Here is what I would like it to do:
I have a query called qryExportContainers:
SELECT tblItems.*, tblContainers.EmbarkNumber, tblContainers.ContainerName
FROM tblContainers INNER JOIN tblItems ON tblContainers.ContainerID =
tblItems.ContainerID;

If the function could create a spreadsheet named after each ContainerName
and then Items that are contained in that container could be added to that
container's spreadsheet.

I tried all day yesterday changeing the before mentioned code, but keeped
getting errors like "Container1" already exsists". And it wouldn't let me
have spaces in the container name.

Sorry this reply is so long and specific, but I am just starting with Visual
Basic and SQL. Thanks
 
Back
Top