Hi,
I need quite a bit of help here as I am new to VBA and XML. I have an Infopath form that basically stores data that users fill in in XML format. Then it will be passed to the Excel to treat the data.
There are some data that will be repeated throughout the Infopath form, e.g. Name, Dates etc. However, there will be data that will require users to keep inputting as it is unique to each transaction e.g. Price, Currency etc.
As such, the previous code should allow for those data that are repeated to continue working as per normal (kindly advise me if this is not true), but I would have to tweak the codes to allow excel to run the data that users have to keep inputting. The data that is unique to each transaction will now be renamed as 'Price1', 'Price2'....
So basically, I would need the repeated data to stay in the excel throughout as the added data changes (e.g. Price1, Currency1 etc gets added to the table) and is saved into the Database before looping on to the next Price2 etc... If there isn't anymore, then the code should stop running so that it does not take up so much time.
What I have so far is this:
Sub sub_inputData(dicData As Dictionary)
Dim ws As Worksheet: Set ws = ActiveSheet
Dim i As Integer
Dim j As Integer
Dim vTemp As Variant
Range("rInputStart").Parent.Calculate
vTemp = Range(Range("rInputStart").Offset(1), Range("rInputStart").End(xlDown).Offset(0, 2)).value
ReDim price(j) As Long
Dim currency As String: currency = vbNullString
Dim exchangeRate as string: exchangeRate = vbNullString
Dim remark as string: remark = vbNullString
For j = 1 To 10
price(j) = Range("rPriceManual").value --> and at this point, other functions will be called to convert the prices to different currencies etc, as long as the prices are inserted into the table correctly
currency = currency & dicData("dl_currency" & CStr(j)) & "|"
exchangeRate = (exchangeRate & dicData("exchange_rate" & CStr(j)) & "|") / 100
Remark = remark & dicData("remarks" & CStr(j)) & "|"
For i = LBound(vTemp, 1) To UBound(vTemp, 1)
If vTemp(i, 1) = "currency" And dicData(dl_currency) <> vbNullString Then
vTemp(i, 3) = currency
If vTemp(i, 2) = "remark" Then
vTemp(i, 3) = Remark
End If
If vTemp(i, 2) = "exchangeRate" Then
vTemp(i, 3) = exchangeRate
End If
Next i
Next j
End Sub
It does not work properly and I'm not sure whether im doing the best and most efficient way.Please kindly assist me in this as I am really lost at this. Thank you in advance!
I need quite a bit of help here as I am new to VBA and XML. I have an Infopath form that basically stores data that users fill in in XML format. Then it will be passed to the Excel to treat the data.
There are some data that will be repeated throughout the Infopath form, e.g. Name, Dates etc. However, there will be data that will require users to keep inputting as it is unique to each transaction e.g. Price, Currency etc.
As such, the previous code should allow for those data that are repeated to continue working as per normal (kindly advise me if this is not true), but I would have to tweak the codes to allow excel to run the data that users have to keep inputting. The data that is unique to each transaction will now be renamed as 'Price1', 'Price2'....
So basically, I would need the repeated data to stay in the excel throughout as the added data changes (e.g. Price1, Currency1 etc gets added to the table) and is saved into the Database before looping on to the next Price2 etc... If there isn't anymore, then the code should stop running so that it does not take up so much time.
What I have so far is this:
Sub sub_inputData(dicData As Dictionary)
Dim ws As Worksheet: Set ws = ActiveSheet
Dim i As Integer
Dim j As Integer
Dim vTemp As Variant
Range("rInputStart").Parent.Calculate
vTemp = Range(Range("rInputStart").Offset(1), Range("rInputStart").End(xlDown).Offset(0, 2)).value
ReDim price(j) As Long
Dim currency As String: currency = vbNullString
Dim exchangeRate as string: exchangeRate = vbNullString
Dim remark as string: remark = vbNullString
For j = 1 To 10
price(j) = Range("rPriceManual").value --> and at this point, other functions will be called to convert the prices to different currencies etc, as long as the prices are inserted into the table correctly
currency = currency & dicData("dl_currency" & CStr(j)) & "|"
exchangeRate = (exchangeRate & dicData("exchange_rate" & CStr(j)) & "|") / 100
Remark = remark & dicData("remarks" & CStr(j)) & "|"
For i = LBound(vTemp, 1) To UBound(vTemp, 1)
If vTemp(i, 1) = "currency" And dicData(dl_currency) <> vbNullString Then
vTemp(i, 3) = currency
If vTemp(i, 2) = "remark" Then
vTemp(i, 3) = Remark
End If
If vTemp(i, 2) = "exchangeRate" Then
vTemp(i, 3) = exchangeRate
End If
Next i
Next j
End Sub
It does not work properly and I'm not sure whether im doing the best and most efficient way.Please kindly assist me in this as I am really lost at this. Thank you in advance!
