Thanks, Bernie.

That sounds promising, but I don't have the time to look at it in detail
just now. It'll have to wait for another day.

I don't know how to count the number of entries in an Access table either,
but your code seems to do the job once it's in Excel.

--
Ian
--
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
I have no idea how to count items from an Access table, but for the Excel
Part

A lot depends on what objects you have defined:
With oExcel
With oXLWkBk
With oXLWkSht

Lets's assume you have oExcel defined as the Excel Application, and have
SheetA as an object: oXLWkShtA, and the data appears in column A of that
sheet. Lets's also assume you have SheetB as an object: oXLWkShtB.

Set oXLWkShtA = oExcel.Workbooks("Book1.xls").Worksheets("SheetA")
Set oXLWkShtB = oExcel.Workbooks("Book2.xls").Worksheets("SheetB")
myCount =
oXLWkShtA.Range("A:A").SpecialCells(xlCellTypeCons tants).Cells.Count
oXLWkShtB.Range("A:A").Resize(1, myCount).EntireColumn.Insert
oXLWkShtA.Range("A:A").SpecialCells(xlCellTypeCons tants).Cells.Copy
oXLWkShtB.Range("A1").PasteSpecial Transpose:=True


HTH,
Bernie
MS Excel MVP


"Ian" wrote in message
...
Sorry for the cross-posting, but I'm not sure where this is better
placed.

I need to take the data from an Access table and turn it into column
headers in an Excel spreadsheet.

I can easily export the data to a spreadsheet (call it sheet A), but it
appears as a column.

I then need to determine how many entries there are in the column, insert
that number of columns in another spreadsheet in a different workbook
(call it sheet B) and transpose the column in sheet A to populate row 1
in sheet B.

My question is, is there an easier way to do this, perhaps by determining
the number of entries in the table and exporting this directly into the
spreadsheet row?

Failing that, I know how to determine how many rows are used in sheet A,
but how do I transpose it to sheet B?

This is all to be done using Access VBA.

Hope you can help.

--
Ian
--