Transpose to different sheet

[Ian]

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.

 

[Guest]

I would go the other way. Don't export from access, instead import from
excel. Record a new macro in excel, then go to data - import external data -
new database query.

I it esier to read tables in excel then in access. Once to get your macro
post the results and we can give you futher assistance.

 

[Bernie Deitrick]

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(xlCellTypeConstants).Cells.Count
oXLWkShtB.Range("A:A").Resize(1, myCount).EntireColumn.Insert
oXLWkShtA.Range("A:A").SpecialCells(xlCellTypeConstants).Cells.Copy
oXLWkShtB.Range("A1").PasteSpecial Transpose:=True


HTH,
Bernie
MS Excel MVP

 

[Ian]

Thanks, Joel.

I'm much more familiar with Excel than Access and I have already done most
of the work towards formatting the data and the calculations required in the
resulting spreadsheet. I will bear your suggestions in mind if I don't get
anywhere with Bernie's suggestion.

 

[Ian]

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.