Excel data to Access

P

Piotr

Hi,
I have file with data being modyfied by users from time to time.
In Excel it looks like:
name month (other collumns)
1 2 3 4 5 6
......
Student Michael 234 34 2 67 65 34....

I need ths data into acces like

student Michael 1 234
student Michael 2 34

I cant use Transponation as I need data from every second line in
Excel.

Is teher any good and efficient solution for making such a forms ?

regards
Peter
 
D

Dave Peterson

If I understand correctly, you have names in column A. Test scores in columns
B:??.

You want the name copied to column A, the header for each testscore in column B
and the actual testscore in column C. (I don't understand about every second
line in excel, though.)

But maybe this macro will create a worksheet you can edit to make it look like
you want.

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long

Dim oRow As Long

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 2
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

oRow = 0
For iRow = FirstRow To LastRow
For iCol = FirstCol To LastCol
oRow = oRow + 1
newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
newWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value
newWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value
Next iCol
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top