Part 1: Send HTTP Request For Each Row In Excel Table

Constants

Use constants to make it easier to read and modify your code as names change.

Public Const TblDataName = "tblData" Public Const TblDataLoadColumn = "Load?" Public Const TblDataMessageColumn = "Message" Public Const TblDataNumURLColumn = "URL" Public Const TblDataTimestampColumn = "Message Timestamp" Public Sub LoadRecords() 'some code .... Set tbl = ActiveSheet.ListObjects(TblDataName) colNumLoad = getColNum(TblDataLoadColumn) colNumMessage = getColNum(TblDataMessageColumn) colNumURL = getColNum(TblDataNumURLColumn) colNumTimestamp = getColNum(TblDataTimestampColumn)

This setup will allow you to easily update your string references without have to review every line of code.

Avoid Using ActiveSheet

Set tbl = ActiveSheet.ListObjects("tblData")

Using ActiveSheet makes your code fragile, easy to break, and limits code reuse. It is a best practice to change your Worksheet's CodeName and reference the Worksheets by their CodeNames.

I like to add references to my ListObjects as properties of their worksheets.

Function getColNum can be removed

Here is the correct way to retrieve the ListColumn Index:

Function CONCATVARS

Functions names should be Pascal case. I alternate between Pascal and camelCase but never all uppercase. Only constants and Enums should be all upper case (although I have been converted to using Pascal case for them also).

varConcat is very descriptive if you compare it to its context and figure out its meaning. However, you can deduce the usage of text and str without knowing its context. For such a short block of code I prefer using s. Using shorter simpler names often make the code easier to read.

Function ConcatVars(tbl As ListObject, RowNum As Integer) As String Dim Column As ListColumn Dim s As String For Each Column In tbl.ListColumns If Column.Name Like "v_*" Then s = s & IIf(Len(s) > 0, "&", "") _ & Mid(Column.Name & "=" & Column.Range.Cells(RowNum).Value, 3) End If Next ConcatVars = s End Function

Refactored Code

Option Explicit Public Const TblDataName = "tblData" Public Const TblDataLoadColumn = "Load?" Public Const TblDataMessageColumn = "Message" Public Const TblDataNumURLColumn = "URL" Public Const TblDataTimestampColumn = "Message Timestamp" Public Sub LoadRecords() Rem Refreshes the Concatenated Variables column Application.CalculateFull Dim message As String, response As String Dim n As Long With DataSheet.GetTblData .ListColumns(TblDataMessageColumn).Range.Interior.colorIndex = 0 For n = 1 To .ListRows.Count If UCase(.ListColumns(TblDataLoadColumn).DataBodyRange(n).Value) = "Y" Then response = getHTTP(.ListColumns(TblDataNumURLColumn).DataBodyRange(n).Value) 'Send an HTTP request to Maximo using the value in the URL column .ListColumns(TblDataMessage).DataBodyRange(n) = response Rem Put the current date into the Message Timestamp column. Note: This is the Excel date, not a date from Maximo. .ListColumns(TblDataTimestampColumn).DataBodyRange(n) = Now() With .ListColumns(TblDataMessageColumn).DataBodyRange(n) message = Left(response, 7) 'Return a message (created, updated, or error) and store it in the Message column. .Interior.colorIndex = Switch(message = "Created", 43, message = "Updated", 37, True, 3) End With End If Next End With End Sub

Addendum

I added a sample. It shows how I would setup the project and demonstrates a couple of different techniques for working with ListObjects.

Table Demo