Excel Macro to the Rescue!

I'm currently importing fixed assets into the system from an Excel spreadsheet. This is one of the reasons why I like developing for ERPs such as Ax 2009 as I find both the data itself and the process of importing the data very interesting. After learning X++ I started to love learning about how a company 'works'.

The process of exporting/importing data is undertaken via the Administration area, Period panel. The functionality that it offers us is both scary and wonderful. We're exporting Journal lines (adquiring fixed assets) and updating their dates of adquisition to those of an open financial year. The oldest asset we have was bought in 1942!

I'll not document the whole process here, but in the exported Excel document we has data from the LedgerJournalTrans and LedgerJournalTrans_Asset entities, filtered by a Daily identifier. We create a new daily in the production system and then need to update the relevant field in the Excel spreadsheet to correspond with the new identifier. The problem was that in the Excel spreadsheet the LedgerJournalTrans_Asset.RefRecId field was effectively a numeric value in a text formatted column.

I couldn't just update the initial cell and then drag down the value (incrementing by one for each new cell) to the bottom of the sheet.  The column was formatted as text.  I tried doing this in a new numeric column and when I posted the values (without formatting) on top I had difficulty importing the sheets again into the system.  I think it had something to do with how the numbers were presented (I maybe changed the formatting of the cell) and have still not divined the reason.  In the end I resorted to an Excel Macro to update my values, starting from the 'A7' cell:

Sub SelectNextTextNumValue()
'
' SelectNextTextNumValue Macro
' Increase the LedgerJournalTrans_Asset.RefRecId for each row
'
    Dim continue As Boolean
    Dim iCnt As Integer
    Dim sRec As String
    Dim dRec As Double    
     
    iCnt = 1
    continue = True
    sRec = "1638000000"
    
    Range("A7").Select

    Do While continue = True
        ActiveCell.Offset(1, 0).Range("A1").Select
        If ActiveCell.FormulaR1C1 = "" Then
            continue = False
        Else
            dRec = CDbl(sRec)
            dRec = dRec + 1
            sRec = CStr(dRec)
            ActiveCell.FormulaR1C1 = CStr(sRec)        
        End If
        iCnt = iCnt + 1
    Loop

End Sub

Note: Just had to buy the other people using the Ax development instance a coffee as the import appears to hang everyone elses client until it had finished. It's not my fault!?