Loop Application.Run with variable parameter

[WilliamL83]

Hi all

I would like to loop Application.Run
The aim is to run an excel addin called "bbs_remote" which requires 4 parameters i.e. [Class], [Qualifier], [Category], [Identifier]. These are currently named ranges on a sheet. they are strings.
One of the parameters [Qualifier], I want Application.Run to loop through a number of [Qualifier] while keeping the other parameters constant.
Here are 3 examples of [Qualifier]
INT_LPL_BB1_2017
INT_LPL_BB1_2018
INT_LPL_BB1_2019

Here is some code that does not have a loop yet;

Sub Capture_Data()

Dim x As Variant
Dim a As Variant
Dim arrCommodData() As Variant
Dim count As Integer
Dim rowcount As Integer
Dim iCols As Integer
Dim subcount As Integer
Dim thePath As String
Dim strColName As String

x = Application.Run("bbs_remote", 0, [Class], [Qualifier], [Category], [Identifier])
[Data].ClearContents
[Headings].ClearContents

If IsError(x) Then
** *MsgBox "Invalid data specification. *Please check your input and try again."
** *Exit Sub
End If

Any help at all will be much appreciated, thank you!

[xlnitwit]

Hi,

Perhaps this

Sub Capture_Data()

Dim x As Variant
Dim a As Variant
Dim arrCommodData() As Variant
Dim count As Integer
Dim rowcount As Integer
Dim iCols As Integer
Dim subcount As Integer
Dim thePath As String
Dim strColName As String
Dim idents, ident

idents = array("INT_LPL_BB1_2017", "INT_LPL_BB1_2018", "INT_LPL_BB1_2019")
for each ident in idents
x = Application.Run("bbs_remote", 0, [Class], [Qualifier], [Category], ident)
[Data].ClearContents
[Headings].ClearContents

If IsError(x) Then
   MsgBox "Invalid data specification. *Please check your input and try again."
   Exit Sub
End If
next ident

[WilliamL83]

Thanks for your help. I tried this and it's almost there!

I've attached the full code below, basically there are 3 sections.
First section is getting the data and naming it x
Second section is writing a selected set of heading names from x
Third section is writing the data from x under the selected headings

I have put the Next qual, right at the bottom of this procedure. So it will loop back up to the First section after each set of data is done.
However since the headings are already there, running the second section actually keeps writing the Heading names offset by 1 cell from 1st run. Is it possible to skip the Second section and move straight onto Third section?

On the Third section I want to write data from x 2nd run offset by a row (basically on the row directly under the first run, and so forth)

Sub Capture_Data()

Dim x As Variant
Dim a As Variant
Dim arrCommodData() As Variant
Dim count As Integer
Dim rowcount As Integer
Dim iCols As Integer
Dim subcount As Integer
Dim thePath As String
Dim strColName As String
Dim quals, qual

'First section is getting the data
quals = Array("INT_LPL_BB1_2017", "INT_LPL_BB1_2018", "INT_LPL_BB1_2019")
For Each qual In quals
x = Application.Run("bbs_remote", 0, [Class], qual, [Category], [Identifier])
Debug.Print x


'Second section is to get heading names from x and writing them into cells
iCols = Application.Run("bbs_getwidth", CLng(x))
Debug.Print iCols
For count = 1 To iCols
    strColName = Application.Run("bbs_getcolname", CLng(x), count - 1)
    If strColName <> "INSTANCE" And strColName <> "timestamp" And strColName <> "BBS_DATE" Then
        subcount = subcount + 1
        Range("Headings").Cells(1, subcount) = strColName
    End If
Next count


rowcount = Application.Run("bbs_getdepth", CLng(x))
Debug.Print rowcount
If rowcount = 0 Then
    MsgBox "Data table is empty."
    Exit Sub
End If

'Third section is to get the values from x and write them under the required heading
For count = 1 To rowcount
    For Each a In [Headings]
        If a.Value <> "" Then
            a.Offset(count, 0) = Application.Run("bbs_getvalue", CLng(x), count - 1, a.Value)
        End If
    Next a
 Next count
 
Next qual

End Sub

Any help at all will be much appreciated, thank you!