swiftninja
10-13-2014, 07:00 AM
I have been working hard learning and applying VBA using VBE in Excel 2010 for the past few weeks.
I have learned so much from all the books I've read and from all the helpful threads posted here on VBA Express forums.
However, I am having difficulty with arrays, specifically in this instance.
My Goal:
I am trying to optimize this VBA code to output data parsed from XML from the web into an array and then output the array values once to a specific range instead of outputting the data to each individual cell for each completed loop.
Here is the code:
Sub OutputData(xmlResults As MSXML2.DOMDocument, Pos As Integer)
Dim OutputSheet As Worksheet
OutputSheet = Worksheets("OutputSheet")
Row# = Counter
For Each Item In xmlResults
'Grab data from these input cells and output it to OutputSheet
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
OutputSheet.Cells(Row#, 1).Value = Worksheets("InputSheet").Cells(Pos, 4).Value
OutputSheet.Cells(Row#, 2).Value = Worksheets("InputSheet").Cells(Pos, 6).Value
OutputSheet.Cells(Row#, 8).Value = Worksheets("InputSheet").Cells(Pos, 7).Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
OutputSheet.Cells(Row#, 3).Value = GetVal(Item, "Product/Title")
OutputSheet.Cells(Row#, 14).Value = GetVal(Item, "Product/Genre")
OutputSheet.Cells(Row#, 5).Value = GetVal(Item, "Product/PartNumber")
OutputSheet.Cells(Row#, 13).Value = GetVal(Item, "Product/Weight")
OutputSheet.Cells(Row#, 10).Value = GetVal(Item, "Product/Length")
OutputSheet.Cells(Row#, 11).Value = GetVal(Item, "Product/Width")
OutputSheet.Cells(Row#, 12).Value = GetVal(Item, "Product/Height")
OutputSheet.Cells(Row#, 4).Value = GetVal(Item, "Product/Manufacturer")
OutputSheet.Hyperlinks.Add Anchor:=OutputSheet.Cells(Row#, 16), Address:="ExampleUrl" & Query, _
ScreenTip:="Link To Page", _
TextToDisplay:="Item Page"
Worksheets("Table").Cells(Row#, 25).Value = "OK"
Next
Worksheets("InputSheet").Cells(Pos, 8).Value = "Ok"
End Sub
I have learned so much from all the books I've read and from all the helpful threads posted here on VBA Express forums.
However, I am having difficulty with arrays, specifically in this instance.
My Goal:
I am trying to optimize this VBA code to output data parsed from XML from the web into an array and then output the array values once to a specific range instead of outputting the data to each individual cell for each completed loop.
Here is the code:
Sub OutputData(xmlResults As MSXML2.DOMDocument, Pos As Integer)
Dim OutputSheet As Worksheet
OutputSheet = Worksheets("OutputSheet")
Row# = Counter
For Each Item In xmlResults
'Grab data from these input cells and output it to OutputSheet
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
OutputSheet.Cells(Row#, 1).Value = Worksheets("InputSheet").Cells(Pos, 4).Value
OutputSheet.Cells(Row#, 2).Value = Worksheets("InputSheet").Cells(Pos, 6).Value
OutputSheet.Cells(Row#, 8).Value = Worksheets("InputSheet").Cells(Pos, 7).Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
OutputSheet.Cells(Row#, 3).Value = GetVal(Item, "Product/Title")
OutputSheet.Cells(Row#, 14).Value = GetVal(Item, "Product/Genre")
OutputSheet.Cells(Row#, 5).Value = GetVal(Item, "Product/PartNumber")
OutputSheet.Cells(Row#, 13).Value = GetVal(Item, "Product/Weight")
OutputSheet.Cells(Row#, 10).Value = GetVal(Item, "Product/Length")
OutputSheet.Cells(Row#, 11).Value = GetVal(Item, "Product/Width")
OutputSheet.Cells(Row#, 12).Value = GetVal(Item, "Product/Height")
OutputSheet.Cells(Row#, 4).Value = GetVal(Item, "Product/Manufacturer")
OutputSheet.Hyperlinks.Add Anchor:=OutputSheet.Cells(Row#, 16), Address:="ExampleUrl" & Query, _
ScreenTip:="Link To Page", _
TextToDisplay:="Item Page"
Worksheets("Table").Cells(Row#, 25).Value = "OK"
Next
Worksheets("InputSheet").Cells(Pos, 8).Value = "Ok"
End Sub