Excel to access

  • Thread starter Thread starter Joseph Meehan
  • Start date Start date
J

Joseph Meehan

Solo said:
Hi

I have 300 excel files.Now i want them to be maintained as database.
I have tried to import them but my first COLUMN A in excel are the
headers not the first ROW.
how do i transfer the data into access. or if there is any other
database that can be used to import the data.


Solo
Click to expand...

I am not an Excel person, but I believe you can rotate them in Excel.
 
Hi

I have 300 excel files.Now i want them to be maintained as database. I have
tried to import them but my first COLUMN A in excel are the headers not the
first ROW.
how do i transfer the data into access. or if there is any other database
that can be used to import the data.


Solo
 
Solo,
I don't understand what you mean completely but try using the 'transpose' function in you spreadsheet before you convert into access. Use the help function and type in transpose function it will explain it all there. Hope this helps.
Ian
 
Select All, Copy, create a new book, select a region n columns x m rows where n is the rows in the original excel and m is the columns in the original file and then Paste Special and check the Transpose checkbox.
It should do the job.
 
Jamie said:
...


I don't see it in the help. Please post a copy.

Jamie.
Click to expand...
TRANSPOSE

Returns a vertical range of cells as a horizontal range, or vice versa.
TRANSPOSE must be entered as an array formula in a range that has the same
number of rows and columns, respectively, as an array has columns and rows.
Use TRANSPOSE to shift the vertical and horizontal orientation of an array
on a worksheet.

Syntax

TRANSPOSE(array)

Array is an array or range of cells on a worksheet that you want to
transpose. The transpose of an array is created by using the first row of
the array as the first column of the new array, the second row of the array
as the second column of the new array, and so on.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How?

1.. Create a blank workbook or worksheet.
2.. Select the example in the Help topic. Do not select the row or column
headers.


Selecting an example from Help

3.. Press CTRL+C.
4.. In the worksheet, select cell A1, and press CTRL+V.
5.. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the Tools menu, point
to Formula Auditing, and then click Formula Auditing Mode.


1
2
A B C
Data Data Data
1 2 3
Formula Description (Result)
=TRANSPOSE($A$2:$C$2) Value from first column (1)
Value from second column (2)
Value from third column (3)


Note The formula in the example must be entered as an array formula. After
copying the example to a blank worksheet, select the range A5:A7 starting
with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the
formula is not entered as an array formula, the single result is 1.

Example 2

Some functions, such as LINEST, return horizontal arrays. LINEST returns a
horizontal array of the slope and Y-intercept for a line. The following
formula returns a vertical array of the slope and Y-intercept from LINEST.

The example may be easier to understand if you copy it to a blank worksheet.

How?

1.. Create a blank workbook or worksheet.
2.. Select the example in the Help topic. Do not select the row or column
headers.


Selecting an example from Help

3.. Press CTRL+C.
4.. In the worksheet, select cell A1, and press CTRL+V.
5.. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the Tools menu, point
to Formula Auditing, and then click Formula Auditing Mode.


1
2
3
4
5
A B
Known y Known x
1 0
9 4
5 2
7 3
Formula Description (Result)
=TRANSPOSE(LINEST(A2:A5,B2:B5,,FALSE)) Slope (2)
Y-intercept (1)


Note The formula in the example must be entered as an array formula. After
copying the example to a blank worksheet, select the range A7:A8 starting
with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the
formula is not entered as an array formula, the single result is 2.
 
Thanks Joseph, Your post triggered me into remembering 'Transpose' is
a worksheet function. I was looking in MSDN's VBA help!

Jamie.

--
 
Back
Top