## Creating an Agresso upload file from Excel

Nov 23

2010

2010

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 |

Comment