asked on

how to export the contents of a subdatasheet to excel using vba

Experts, right now am using VBA to automatically run a query that creates a table with subdatasheets. The program then automatically exports the primary table to excel. My question is, how do i reference the subdatasheets so that i can automatically export these to excel as well? Below is the code i am currently running to export the primary sheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PRIMARYTABLE", sFn, True, "6B_LinkFileReferences!A1:J1"

Open in new window

BitRunner303

If the subdatasheets are queries or tables they can be exported using the same function.
If it's a form or report though, use the DoCmd.OutputTo command to export:

e.g.

DoCmd.OutputTo acOutputForm,"MySubDataSheet",acFormatXLS,"C:\MyFile.xls",True

Yaniv Schiff

ASKER

How do i know what the subdatasheet name is, i can't figure out where/if i named it? Below is the code i use to create the subdatasheet.

SetFieldProperty CurrentDb.TableDefs![LNK_Link Files grouped by Serial Number with MinMax Dates], "SubdatasheetName", dbText, "LNK_Link Files by Link File Name"
SetFieldProperty CurrentDb.TableDefs![LNK_Link Files grouped by Serial Number with MinMax Dates], "LinkChildFields", dbText, "Serial"
SetFieldProperty CurrentDb.TableDefs![LNK_Link Files grouped by Serial Number with MinMax Dates], "LinkMasterFields", dbText, "Serial"

Open in new window

ASKER CERTIFIED SOLUTION

BitRunner303

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer