replicating an excel macro in access

[chrisbarnett.werneth]

hi

i'm a teacher and have created a reporting database. for my database
to work i have an reports table that looks like this that has the
following fields.

Admin No : unique pupil number
Subject : subject studied
Set : class name that pupil is in for that subject
Attain : attainment grade
Classwork : letter grade
Homework : letter grade
Behaviour : letter grade


However i get the information from an internet database which i can
export data from in the form of an excel file.

The excel table is however in the following format: (reading as columns
across)

Admin No
Subject 1 Set
Subject 1 Attain
Subject 1 Class
Subject 1 Home
Subject 1 Beh
Subject 2 Set
Subject 2 Attain
Subject 2 Class
etc approx 34 subjects

So i created the following routine in Excel to convert the data to fit
my Access table.


Sub Convert_Assessment_Datatable()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim subjectname As String
Dim lastcol As Long

Set curWks = ActiveSheet
Set newWks = Worksheets.Add

newWks.Range("A1").Resize(1, 7).Value = Array("Admin", "Subject",
"Set", "Attain", "Class", "Hwk", "Beh")
oRow = 2
newWks.Range("A2").Resize(1, 4).Value = Array(1000, "delete",
"this", "row")
oRow = 3
With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iRow = FirstRow To LastRow
For iCol = 6 To lastcol Step 5
If IsEmpty(.Cells(iRow, iCol)) Then
'do nothing
Else
oRow = oRow + 1
newWks.Cells(oRow, "A").Value = .Cells(iRow,
"A").Value
newWks.Cells(oRow, "B").Value = Left$(.Cells(1,
iCol + 1).Value, Len(.Cells(1, iCol + 1).Value) - 10)
newWks.Cells(oRow, "C").Value = .Cells(iRow,
iCol).Value
newWks.Cells(oRow, "D").Value = .Cells(iRow, iCol +
1).Value
newWks.Cells(oRow, "E").Value = .Cells(iRow, iCol +
2).Value
newWks.Cells(oRow, "F").Value = .Cells(iRow, iCol +
3).Value
newWks.Cells(oRow, "G").Value = .Cells(iRow, iCol +
4).Value


End If
Next iCol
Next iRow
End With

End Sub

What i'd really like to do (i need to let other users work this
database without my help) is set it up so that i can import the
original Excel table into Access and then run the convert routine as a
macro so that the table conversion is done all within the Access
database.

I hope this makes sense and thanks for any help that you can give

thanks
Chris Barnett

 

[Steve Schapel]

Chris,

In Excel, that is called a macro. In Access, macros are a quite
different species, and distinct from VBA procedures. This could be done
in a macro, but a VBA procedure would be preferable as it can cater to
looping code.

So, assuming you have first used the TransferSpreadsheet method to
import the data from Excel into a table in your Access database with the
field names you mentioned, I think it would then look something like this...

Dim i As Integer
For i = 1 To 34
CurrentDb.Execute "INSERT INTO YourNormalisedTable ( [Admin No],
Subject, Set, Attain, Classwork, Homework, Behaviour )" & _
" SELECT [Admin No], " & i & ", [Subject " & i & " Set],
[Subject " & i & " Attain], [Subject " & i & " Class], [Subject " & i &
" Home], [Subject " & i & " Beh]" & _
" FROM YourImportedTable", dbFailOnError
Next i

Presumably you will need a lookup table of some sort to identify the
subjects according to number 1-34.