CODEWITHSUNDEEP
stringvbaexcel
ExoticBirdsMerchant
ExoticBirdsMerchant

Reputation: 1536

VBA procedure outputs a varying number of String Variables to be Declared and Reused

Here we have a procedure supplied to Codo by Mr Tim Williams which prints on the Immediate window something we really-really want. I made some mini modifications from the original copy and i have the comment-word MODIFICATION next to them

Sub MAGAZINE()

    Dim IE As InternetExplorer ' MODIFICATION
    Dim els, el, colDocLinks As New Collection
    Dim lnk
    Dim Ticker As String ' MODIFICATION

    Set IE = New InternetExplorer 'MODIFICATION

    IE.Visible = True

    Ticker = Worksheets("Sheet1").Range("A1").Value 'MODIFICATION

    loadpage IE, "https://www.sec.gov/cgi-bin/browse-edgar?" & _
                  "action=getcompany&CIK=" & Ticker & "&type=10-Q" & _   'MODIFICATION by putting the Ticker String Variable and then Concatenate accordingly
                  "&dateb=&owner=exclude&count=20"                       

    Set els = IE.Document.getelementsbytagname("a")
    For Each el In els
        If Trim(el.innertext) = "Documents" Then
            colDocLinks.Add el.href
        End If
    Next el

    For Each lnk In colDocLinks
        loadpage IE, CStr(lnk)
        For Each el In IE.Document.getelementsbytagname("a")
            If el.href Like "*[0-9].xml" Then                'MODIFICATION
                Debug.Print el.innertext, el.href
            End If
        Next el
    Next lnk

End Sub

Sub loadpage(IE As Object, url As String)
    IE.Navigate url
    Do While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
End Sub

You can notice in the fourth modification/addition that a person can simply type the Stock Ticker on cell A1 and fire off the code

Ticker = Worksheets("Sheet1").Range("A1").Value 'MODIFICATION

Now the thing is that if we put different stock tickers we get different number of lines printed on the Immediate window.

For example by typing in cell A1 the ticker CRR we get 11 results

CRR

Now if we type in cell A1 the ticker MSFT we get 14 results

MSFT

Now the crux of the issue is that these string values need to be inserted to the RIFLE macro and although i could assing the values i get from every loop iteration in String Variables what throws a monkey wrench in my mind is that the MAGAZINE macro as it is logical does not have an exact number of lines printed on the immediate window. You can see that actually in the preceding two pictures...

So how is it possible when MAGAZINE macro has an outcome of say 6 Lines; these to be assigned to 6 String Variables and not have 30 String Variables always declared wrecking havoc in memory and when the outcome is 14 Lines; these to be assigned to 14 String Variables.

How could this be adjusted on Running-Time so the Rifle always be loaded with correct number of rounds?

Because i will not stop there; then i have plans to plug these String Variables in the RIFLE macro provided by user2140261 and shown below...

Sub RIFLE()
Dim strXMLSite As String
Dim objXMLHTTP As MSXML2.XMLHTTP
Dim objXMLDoc As MSXML2.DOMDocument
Dim objXMLNodexbrl As MSXML2.IXMLDOMNode
Dim objXMLNodeDIIRSP As MSXML2.IXMLDOMNode

Set objXMLHTTP = New MSXML2.XMLHTTP
Set objXMLDoc = New MSXML2.DOMDocument

strXMLSite = "http://www.sec.gov/Archives/edgar/data/10795/000119312513456802/bdx-20130930.xml"

objXMLHTTP.Open "POST", strXMLSite, False
objXMLHTTP.send
objXMLDoc.LoadXML (objXMLHTTP.responseText)

Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("xbrl")

Set objXMLNodeDIIRSP = objXMLNodexbrl.SelectSingleNode("us-gaap:DebtInstrumentInterestRateStatedPercentage")

Worksheets("Sheet1").Range("A1").Value = objXMLNodeDIIRSP.Text
End Sub

If you could plug this fluctuating number of String Values coming out of the MAGAZINE macro into String Variables in the RIFLE macro that would midwife the whole problem.

The RIFLE macro here has from it's original form the strXMLSite String Variabe.

UPDATE I am currently trying to load it into an array and then unload it...

Upvotes: 1

Views: 522

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Here's one suggestion:

Sub MAGAZINE()

    Dim IE As InternetExplorer ' MODIFICATION
    Dim els, el, colDocLinks As New Collection
    Dim lnk, res
    Dim Ticker As String ' MODIFICATION
    Dim colXMLPaths As New Collection '<<<EDIT

    Set IE = New InternetExplorer 'MODIFICATION

    IE.Visible = True

    Ticker = Worksheets("Sheet1").Range("A1").Value 'MODIFICATION

    LoadPage IE, "https://www.sec.gov/cgi-bin/browse-edgar?" & _
                  "action=getcompany&CIK=" & Ticker & "&type=10-Q" & _
                  "&dateb=&owner=exclude&count=20"

    Set els = IE.Document.getelementsbytagname("a")
    For Each el In els
        If Trim(el.innertext) = "Documents" Then
            colDocLinks.Add el.href
        End If
    Next el

    For Each lnk In colDocLinks
        LoadPage IE, CStr(lnk)
        For Each el In IE.Document.getelementsbytagname("a")
            If el.href Like "*[0-9].xml" Then
                Debug.Print el.innertext, el.href
                colXMLPaths.Add el.href  '<<<EDIT
            End If
        Next el
    Next lnk

    'EDIT: ADDED
    For Each lnk In colXMLPaths
        res = RIFLE(CStr(lnk))
        With Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            .NumberFormat = "@"
            .Value = Ticker
            .Offset(0, 1).Value = lnk
            .Offset(0, 2).Value = res
        End With
    Next lnk

End Sub

Function RIFLE(sURL As String)
    Dim strXMLSite As String
    Dim objXMLHTTP As New MSXML2.XMLHTTP
    Dim objXMLDoc As New MSXML2.DOMDocument
    Dim objXMLNodexbrl As MSXML2.IXMLDOMNode
    Dim objXMLNodeDIIRSP As MSXML2.IXMLDOMNode

    RIFLE = "???"
    objXMLHTTP.Open "GET", sURL, False  '<<EDIT: GET not POST
    objXMLHTTP.send
    objXMLDoc.LoadXML (objXMLHTTP.responseText)

    Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("xbrl")

    Set objXMLNodeDIIRSP = objXMLNodexbrl.SelectSingleNode _
            ("us-gaap:DebtInstrumentInterestRateStatedPercentage")

    If Not objXMLNodeDIIRSP Is Nothing Then
        RIFLE = objXMLNodeDIIRSP.Text
    End If
End Function

Sub LoadPage(IE As Object, url As String)
    IE.Navigate url
    Do While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
End Sub

Upvotes: 2

Related Questions