Transposing Excel Data --> Access 2000

[Steve]

Hi all;

I have several spreadsheets that have to be imported into an Access 2000
tables. This would be easy using linked tables of course, but the data in
Excel is in Columns, and I need it in rows in Access. Is there any way to
bring this data in and transpose it?

Thanks in Advance.
Steve

 

[Joe Fallon]

Actually it is easier to transpose it in Excel first.

Transpose rows and columns
Show All
Hide All
Data from the top row of the copy area (copy area: The cells that you
copy when you want to paste data into another location. After you copy
cells, a moving border appears around them to indicate that they've been
copied.) appears in the left column of the paste area (paste area: The
target destination for data that's been cut or copied by using the Office
Clipboard.), and data from the left column appears in the top row.

1.. Select the cells that you want to switch.
2.. Click Copy .
3.. Select the upper-left cell of the paste area. The paste area
must be outside the copy area.
4.. Click the arrow to the right of Paste and then click Transpose.

 

[John Nurick]

Hi Steve,

Consider the following, which I think are in ascending order of
unpleasantness:

1) Use Excel's own TRANSPOSE() worksheet function to change rows to
columns. Then use Copy and Paste Special.. Values to change the
TRANSPOSE() formula into ordinary values which Access is more likely to
import successfully. Finally do the import.

2) Import into Access as is and then use crosstab queries to perform the
transposition.

3) Write VBA code that reads the worksheet column by column, getting the
value of each cell and building up a SQL INSERT INTO ... VALUES
statement that will append the corresponding record to the Access table.