Need help on exporting an Excel Sheet into Access 2000

[Edward S]

I budget for a Project in an Excel sheet as illustrated below. The
months below are usually a 2 year period i.e. 24 months, though it
could be over 24 months depending upon a Project. I then need to
input this in an Access database, where I do a comparison with the
Actual cost. The table “TblBudget” in Access is made of 4
fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
At the moment this method is very cumbersome. I have to manually
input the data one by one rather than a direct input. The reason
being as my spreadsheet is in a tabulated format. It is more
horizontal and I need to import it in a vertical format, I think
you'll understand what I mean!.

I could accomplish this quicker if I could first rearrange the data in
a vertical format with just the 4 fields above, example
CostElement CostCenter Month Amount$.
422100 R3551 Jan-03 $2,000
422100 R3551 Feb-03 $3,000
..
..
422103 R3700 Jan-03 $2,.260

….. and so on
Is there an easier way to handle this. Any help in this regard is
greatly appreciate
Regards
Edward

The Excel file goes like this with the following fields:
CostElement CostCenter Jan-03 Feb-03 Mar-3 Apr-03 ....... Dec-04
422100 R3551 $2,000 $3,000 $0 $3,500 $4,200
422103 R3700 $2,260 $3,300 $0 $4,670 $3,500
456700 R3551 $2,270 $3,500 $300 $5,230 $3,500
456705 R3551 $2,300 $2,300 $300 $4,590 $4,500
456708 T3305 $2,400 $4,500 $550 $3,690 $4,500
456800 R3551 $2,260 $0 $450 $0 $3,500
457890 T3305 $2,500 $0 $350 $6,790 $4,500
457895 R3700 $2,000 $2,300 $350 $5,590 $4,500
457900 R3551 $2,700 $3,650 $60 $5,000 $4,500
457905 R3700 $2,650 $5,700 $330 $5,000 $4,500
..
...
..
650200 T3305 $1,000 $3,300 $300 $3,590 $4,500

 

[BrianB]

This will transfer your data to a new worksheet. Copy/paste into a macr
module & run with your data sheet active :-

'--------------------------------------------------------------
Sub test()
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim FromRow As Long
Dim ToRow As Long
Dim FromCol As Integer
Dim LastRow As Long
'------------------------------
Set FromSheet = ActiveSheet
FromRow = 2 ' first row of data
LastRow = FromSheet.Range("A1").End(xlDown).Row
Set ToSheet = Worksheets.Add
ToRow = 2
'-------------------------------------------------------------
Do
FromCol = 3 'first numeric column
Do
ToSheet.Cells(ToRow, 1).Value = FromSheet.Cells(FromRow
1).Value
ToSheet.Cells(ToRow, 2).Value = FromSheet.Cells(FromRow
2).Value
ToSheet.Cells(ToRow, 3).Value = FromSheet.Cells(FromRow
FromCol).Value
ToRow = ToRow + 1
FromCol = FromCol + 1
Loop While FromSheet.Cells(FromRow, FromCol).Value <> 0
FromRow = FromRow + 1
Loop While FromRow <= LastRow
End Sub
'-----------------------------------------------------------------------