gmayor and westconn1, I appreciate the help and I think you've almost gotten me there. Thanks for the piggyback ride!
If I use the portion in between the asterisks and pass strData to a MsgBox, I get the raw info, so that's working.
Function MyData(strData As String) As Variant
Dim vWords() As String, vLines() As String, myURL As String
Dim i As Long, j As Long
'************************
Dim WinHTTPReq As Object
Set WinHTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
myURL = "Full URL String"
WinHTTPReq.Open "GET", myURL, False
WinHTTPReq.Send
strData = WinHTTPReq.responseText
'************************
'replace the field separators with commas
strData = Replace(strData, Chr(27) & Chr(9), Chr(44))
'replace the record separators with vbCr (Chr(13)
strData = Replace(strData, Chr(27) & Chr(13) & Chr(10), vbCr)
'Split the data at the line breaks
vLines = Split(strData, vbCr)
'Split the lines at the commas
vWords = Split(vLines(0), Chr(44))
'setup the array to receive the data
ReDim DataArray(UBound(vLines), UBound(vWords))
'Process each line
For i = 0 To UBound(vLines)
'Process each field
vWords = Split(vLines(i), Chr(44))
'add them to the array
For j = 0 To UBound(vWords)
DataArray(i, j) = vWords(j)
Next j
Next i
MyData = DataArray
End Function
gmayor, I did some research on this before asking again, but this is where the Run stops. It's giving an Object Required error on the MsgBox line. I tried seeing what kind of variables I should be using when a MsgBox calls a function, but I'm coming up empty. From what I see, it's asking for the information from MyData that's on the ActiveDocument? I'm confused because there wouldn't be an active document for the WinHTTPReq, and nothing will get passed until the data is formatted and can be placed appropriately in the address/subject of a new email.
'Read a field
Sub Test()
'Call the array and supply it with the source of the string - here a document
MsgBox MyData(ActiveDocument.Range.Text)(1, 1) 'Row,Col
End Sub
This all gets called from a UserForm and an OK button, so should I just be referencing the Array when it's time to populate the new email? Below is what I use to populate with what I have so far. How does MyData play into that? Do I need to dim a variable for every "cell" in the array to use it?
With oMail
.Subject = JobNum & Priv & Repl & "_PO" & FOnly & " - " & StrConv(JobNam, vbProperCase) _
& " - " & StrConv(Subj, vbProperCase)
.Recipients.Add ("email at emaildotcom")
.BCC = BidLst
End With