Agresso Budget Loading from Excel

Actually this could be used for a variety of purposes but I wrote this for Agresso.

This is designed for a sheet with the data in column d , e and f which was required by period and transposes for loading.

Use the x variable to define the offset relative position, this allows you to copy the code down for each column and only change 1 value.

If time had allowed I would have written this as a loop using column numbers. But you can change this if you wish..

Sub Create_Agresso_BudgetLoad()
'Macro to create Agresso Load
Dim tmpArray(5000, 8), I As Integer, y As Integer
Dim tmpCode, tmpCount, x, tmpTrue
Range("D3").Select
 
I = 1
x = 0
 
tmpCode = ActiveCell.Value
tmpCount = 1
 
Do While I < 300
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Value <> 0 And ActiveCell.Offset(0, (-3 - x)).Value <> "" Then
        y = 1
 
        Do While y < 13
            tmpArray(tmpCount, 1) = ActiveCell.Offset(0, (-3 - x)).Value
            tmpArray(tmpCount, 2) = ActiveCell.Offset(0, (-2 - x)).Value
            tmpArray(tmpCount, 3) = tmpCode
            tmpArray(tmpCount, 4) = ""
            tmpArray(tmpCount, 5) = ""
            tmpArray(tmpCount, 6) = ActiveCell.Offset(0, (-1 - x)).Value
            tmpArray(tmpCount, 7) = ActiveCell.Offset(0, 0).Value / 12
            tmpArray(tmpCount, 8) = "2011" & Right("00" & y, 2)
            tmpCount = tmpCount + 1
            y = y + 1
        Loop
    End If
    I = I + 1
Loop
 
Range("E3").Select
I = 1
x = 1
 
tmpCode = ActiveCell.Value
 
 
Do While I < 300
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Value <> 0 And ActiveCell.Offset(0, (-3 - x)).Value <> "" Then
        y = 1
 
        Do While y < 13
            tmpArray(tmpCount, 1) = ActiveCell.Offset(0, (-3 - x)).Value
            tmpArray(tmpCount, 2) = ActiveCell.Offset(0, (-2 - x)).Value
            tmpArray(tmpCount, 3) = tmpCode
            tmpArray(tmpCount, 4) = ""
            tmpArray(tmpCount, 5) = ""
            tmpArray(tmpCount, 6) = ActiveCell.Offset(0, (-1 - x)).Value
            tmpArray(tmpCount, 7) = ActiveCell.Offset(0, 0).Value / 12
            tmpArray(tmpCount, 8) = "2011" & Right("00" & y, 2)
            tmpCount = tmpCount + 1
            y = y + 1
        Loop
    End If
    I = I + 1
Loop
 
 
Range("F3").Select
I = 1
x = 2
 
tmpCode = ActiveCell.Value
 
 
Do While I < 300
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Value <> 0 And ActiveCell.Offset(0, (-3 - x)).Value <> "" Then
        y = 1
 
        Do While y < 13
            tmpArray(tmpCount, 1) = ActiveCell.Offset(0, (-3 - x)).Value
            tmpArray(tmpCount, 2) = ActiveCell.Offset(0, (-2 - x)).Value
            tmpArray(tmpCount, 3) = tmpCode
            tmpArray(tmpCount, 4) = ""
            tmpArray(tmpCount, 5) = ""
            tmpArray(tmpCount, 6) = ActiveCell.Offset(0, (-1 - x)).Value
            tmpArray(tmpCount, 7) = ActiveCell.Offset(0, 0).Value / 12
            tmpArray(tmpCount, 8) = "2011" & Right("00" & y, 2)
            tmpCount = tmpCount + 1
            y = y + 1
        Loop
    End If
    I = I + 1
Loop
 
 
 
Sheets("Setup").Activate
Range("a2").Select
ActiveCell.Value = "Depart"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Project"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Account"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Budget"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Company"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Text"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Period"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Budget"
Range("a3").Select
 
 
y = 1
Do While y < 5000
    For I = 1 To 8
        ActiveCell.Offset(y, I - 1).Value = tmpArray(y, I)
    Next I
    y = y + 1
Loop
 
 
 
 
 
End Sub
,

Pat