Lucky0683
06-29-2018, 03:25 AM
Hi,
I really appreciate your help in this as I am new to VBA. I have an Infopath form that basically stores data that users fill in, in XML format. Then, it will be passed to Excel to treat the data. Some data remains constant throughout e.g. Names and Date. However, as the form is trying to accommodate multiple transactions to be recorded within the same date, some data is unique to each transaction e.g. Price/Currency.
As such I am trying to create a code such that data that is unique to each transaction will be looped over from 1 to 10 (max amount of transactions that can be recorded within the same form) and they are now renamed as 'Price 1', 'Price 2' etc. All this while, data that is the same (e.g. name and date) still remains in the table and each transaction is saved into the Database before moving on to the next transaction's data. If there is no more transaction, the code should stop running.
When i click run, it prompts me for the name of the macro and does not run at all. I'm not sure how to go about editing it too. Will really appreciate your help. Thank you!
I have tried the following code:
Sub sub_inputData(dicData AsDictionary)
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
Dim price as Long
Dim currency As String: currency = vbNullString
Dim exchangeRate as String: exchangeRate = vbNullString
Dim remark as String: remark = vbNullString
For j =1To10
price = price & dicData ("price" & CStr (j))&"|"
price = price ("rPriceManual").value 'and at this point, other functions will be called to convert the prices to different currencies etc,aslongas 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)ToUBound(vTemp,1)
If vTemp(i,1)="currency"And dicData(dl_currency)<> vbNullString Then
vTemp(i,3)= currency
End if
If vTemp (i,2) = "remark" then
vTemp(i,3)=Remark
EndIf
If vTemp(i,2)="exchangeRate"Then
vTemp(i,3)= exchangeRate
EndIf
Next i
Next j
End Sub
I really appreciate your help in this as I am new to VBA. I have an Infopath form that basically stores data that users fill in, in XML format. Then, it will be passed to Excel to treat the data. Some data remains constant throughout e.g. Names and Date. However, as the form is trying to accommodate multiple transactions to be recorded within the same date, some data is unique to each transaction e.g. Price/Currency.
As such I am trying to create a code such that data that is unique to each transaction will be looped over from 1 to 10 (max amount of transactions that can be recorded within the same form) and they are now renamed as 'Price 1', 'Price 2' etc. All this while, data that is the same (e.g. name and date) still remains in the table and each transaction is saved into the Database before moving on to the next transaction's data. If there is no more transaction, the code should stop running.
When i click run, it prompts me for the name of the macro and does not run at all. I'm not sure how to go about editing it too. Will really appreciate your help. Thank you!
I have tried the following code:
Sub sub_inputData(dicData AsDictionary)
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
Dim price as Long
Dim currency As String: currency = vbNullString
Dim exchangeRate as String: exchangeRate = vbNullString
Dim remark as String: remark = vbNullString
For j =1To10
price = price & dicData ("price" & CStr (j))&"|"
price = price ("rPriceManual").value 'and at this point, other functions will be called to convert the prices to different currencies etc,aslongas 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)ToUBound(vTemp,1)
If vTemp(i,1)="currency"And dicData(dl_currency)<> vbNullString Then
vTemp(i,3)= currency
End if
If vTemp (i,2) = "remark" then
vTemp(i,3)=Remark
EndIf
If vTemp(i,2)="exchangeRate"Then
vTemp(i,3)= exchangeRate
EndIf
Next i
Next j
End Sub