Would someone please add some comments to the code below? I'm having difficulties following it. Where is the ODBC connection part of the code? What does the Refresh BackgroundQuery:=False do? The macro does not always refresh data.


Public Const WtDsnName As String = "WTODGeo"
Public Const WtDsnServerAddress As String = "servername"
Public Const WtDsnServerPort As String = "xxxx"

Public Type returnStatus
success As Boolean
errorText As String
End Type

Sub GetCurrentMonth()
Range("$A$2").Select
ActiveCell.FormulaR1C1 = GetWebTrendsDate()
End Sub

Public Function GetWebTrendsDate() As String
' Formats the date in a manner that WebTrends likes.
GetWebTrendsDate = Format(Now(), "yyyy") & ".m" & Format(Now(), "mm")
End Function

Sub GeoWTDataRefresh()
Dim wtDate As String
Dim results As returnStatus
Dim returnQuery As String

wtDate = Sheets("GeoData").Range("$A$2").Value
Range("$A$5").Select

'Call the refresh function
results = RefreshUsingInsheetDate(wtDate, returnQuery)


If results.success = True Then
'debug: uncomment this msgbox if you want a confirmation that the refresh was successful
MsgBox ("Success" & Chr(13) & "Date retrieved: " & wtDate & Chr(13) & Chr(13) & "QueryString: " & returnQuery)
Else
'debug: uncomment this msgbox if you want a confirmation that the refresh was successful
MsgBox ("Failure" & Chr(13) & results.errorText & Chr(13) & "Date attempted: " & wtDate & Chr(13) & Chr(13) & "QueryString: " & returnQuery)
End If



Exit Sub
ErrorHandler:

End Sub

Function RefreshUsingInsheetDate(wtDate As String, ByRef queryString As String) As returnStatus

On Error GoTo ErrorHandler

'the Geo data query
'inserts the passed date

With Selection.QueryTable
.CommandText = Array( _
"SELECT VisitorSummary_0.Name, VisitorSummary_0.Value, VisitorSummary_0.TimePeriod, VisitorSummary_0.StartDate, VisitorSummary_0.EndDate FROM VisitorSummary VisitorSummary_0 WHERE (VisitorSummary_0.TimePeriod='" & wtDate & "')")
'FROM CampaignTypesSummaryByCampaign CampaignTypesSummaryByCampaign_0 WHERE (CampaignTypesSummaryByCampaign_0.TimePeriod='" & wtDate & "')")
.Refresh BackgroundQuery:=False
End With


'Set return values Success
RefreshUsingInsheetDate.success = True
queryString = Selection.QueryTable.CommandText


Exit Function

ErrorHandler:
'Set return values Failure
queryString = Selection.QueryTable.CommandText
RefreshUsingInsheetDate.success = False
RefreshUsingInsheetDate.errorText = Application.ODBCErrors.Item(1).ErrorString

End Function