Transpose to different sheet
 

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.

Transpose to different sheet
 

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.

"Ian" wrote:

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
--

Transpose to different sheet
 

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
--

Transpose to different sheet
 

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
--
"Joel" wrote in message
...
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.

"Ian" wrote:

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
--

Transpose to different sheet
 

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
--