Export query to excel range on the fly

[TUSSFC]

I have a query which pulls the following fields from 3 related tables:

Stat_Date
StatType_Title
Stat_Data

The number of records this query will return will ALWAYS be 20 records.

I need to export this data to excel, specifically into a cell range as follows:

Stat_Date as the X axis (from cells B4:U4)
StatType_Title as the Y axis (from cells A5:Axx) <- note, Axx represents the last row - the number of StatTypes listed is unknown and could change

The above two fields are column and row headings. The Stat_Data field is the data which relates to the appropriate StateType_Title on the appropriate Stat_Date.

There will only ever be 1 record for each Stat_Date and StatType_Title.

So it kind of looks like a matrix, for example:

___________01/01/2009__02/01/2009__03/01/2009
StatType1__10____________20__________30
StatType2__200___________300_________400
StatType3__3000__________4000________5000

Hopefully I've explained this the best I can. Can anyone point me in the right direction?

Thanks.

 

[jal]

Here's a code sample (uses the table Customers). This is an import but I suppose it will export if you change the first value.

DoCmd.TransferSpreadsheet acImport, 8, "Customers", pathToSpreadsheet, True, "MainSheet!C4:E6"

Another technique is to preassign the same name to the excel range as to your query. The following code should search the sheet for a range named "qryExcelData" and then send the data to that range.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExcelData", pathToXLS, True

 

[TUSSFC]

Hi

Thanks for the reply. I can't state a specific cell range, as I don't know how many Stat Types there will be. There will always be 20 days going horizontally across but the number of StatTypes may be 10 for 1 month. And upon running the export a month later may be 15 (rows).

How can I make it more dynamic? Simply a case of counting the no of rows first and storing the result in a string ... and then using this to compile the export statement?

 

[jal]

I don't do much excel, but I can give you another cut-and-paste from my notebook. Basically it says that if you name the range, you can expand it dynamically. Here goes (hope I got it right).


You can resize the range. For instance suppose it is a named range (in this case named Table1), you can resize it like this (this is a two-column table). The Macro recorder generated this code, well I edited it slightly.
Range("A1").Select 'selects the first cell in the range (first column header)
ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:="=inData!R1C1:R11C2"
The parameter called:

RefersToR1C1:

indicates the use of R1C1 notation (which means "relative to the current cell"). The value passed

"=inData!R1C1:R11C2"
apparently extends the range from the starting cell (which, in relative notation, is R1C1) to the ending cell
R11C2
which means Row 11 Column 2 (relative to the starting cell), in other words 10 rows down and one column to the right. The number of columns has stayed the same (Table1 was originally a two column table) but we have extended the number of rows (assuming Table1 originally had less than ten rows).

 

[TUSSFC]

Thanks for the reply. I think I've managed to do what i need by creating a cross-tab query. I'll admit, i've done a lot of VBA, etc, in access but never touched cross-tab's. Seems to do what I need nicely Thanks for your time though!

 

[DCrake]

If you use Excel Automation via VBA you can use the CopyFrom Recordset functionality by simply referring to the anchor cell.

Set oWB = oXL.Workbooks.Open(szDestination)
Set oWS = oWB.Worksheets(1)
oWS.Range("C9").CopyFromRecordset Rst

 

[jal]

If you use Excel Automation via VBA you can use the CopyFrom Recordset functionality by simply referring to the anchor cell.

Set oWB = oXL.Workbooks.Open(szDestination)
Set oWS = oWB.Worksheets(1)
oWS.Range("C9").CopyFromRecordset Rst

Nice tip. Thanks.