Transpose to different sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian - 19 Jul 2007 16:11 GMT

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.

Signature

Ian
--

Reply to this Message

Joel - 19 Jul 2007 17:52 GMT

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.

> Sorry for the cross-posting, but I'm not sure where this is better placed.
>
[quoted text clipped - 19 lines]
>
> Hope you can help.

Reply to this Message

Ian - 19 Jul 2007 18:34 GMT

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.

Signature

Ian
--

>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
[quoted text clipped - 32 lines]
>>
>> Hope you can help.

Reply to this Message

Bernie Deitrick - 19 Jul 2007 17:54 GMT

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

> Sorry for the cross-posting, but I'm not sure where this is better placed.
>
[quoted text clipped - 16 lines]
>
> Hope you can help.

Reply to this Message

Ian - 19 Jul 2007 18:40 GMT

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.

Signature

Ian
--

>I have no idea how to count items from an Access table, but for the Excel
>Part
[quoted text clipped - 44 lines]
>>
>> Hope you can help.

Reply to this Message