External Data Failure VBA Code

[Craig B]

So I coded up this nice macro to extract call by call data from a
Symposium, read Sybase, Database. I use a few variables to take the
place of dates and common words to save typing. I use a for...next loop
to zip through a few days of the month or the entire month which ever,
it creates the SQL on the fly, builds the query table, interrogates
the data base, deletes the query table then gives the sheet and
workbook a unique name saves it, then adds a new workbook and starts
over.

This worked fine for months on end and now won't run more than once,
sometimes not at all. I get an error on oQT.refresh
backgroundrefresh:=false

Any ideas on what went wrong? I replaced the text of the password
with ***** for my own piece of mind

dDate = Format(Month(Date - 1) & "/" & iStartDay & "/" & Year(Date -
1), "mm/dd/yyyy")

sDate = Format(dDate, "yyyymmdd")

Workbooks.Add

For r = iStartDay To Day(Date - 1)

sSName = Format(dDate, "mm-dd-yyyy")
sWBName = "Call by Call " & sSName & ".XLS"

If FileExists(sPath & sWBName) = False Then

sSql = ""
sConn =
"ODBC;DSN=Aus1S002;UID=cbecker;PWD=rekceb;SRVR=Aus1s002;DB=blue"
sSql = "SELECT " & sCW & sDate & ".Timestamp, "
sSql = sSql & sCW & sDate & ".CallEvent, "
sSql = sSql & sCW & sDate & ".CallEventName, "
sSql = sSql & sCW & sDate & ".CallID, "
sSql = sSql & sCW & sDate & ".TelsetLoginID, "
sSql = sSql & sCW & sDate & ".AssociatedData, "
sSql = sSql & sCW & sDate & ".Destination, "
sSql = sSql & sCW & sDate & ".EventData, "
sSql = sSql & sCW & sDate & ".Source, "
sSql = sSql & sCW & sDate & ".Time " & vbCrLf
sSql = sSql & "FROM blue.dbo.eCallByCallStat" & sDate & "
" & sCW & sDate & vbCrLf
sSql = sSql & " ORDER BY " & sCW & sDate & ".Timestamp"


Set oQT = ActiveSheet.QueryTables.Add(Connection:=sConn,
Destination:=Range("A1"), Sql:=sSql)

oQT.Refresh BackgroundQuery:=False

Do While oQT.Refreshing = True
Loop

Columns.AutoFit

For Each WSh In ActiveWorkbook.Worksheets
For Each oQT In WSh.QueryTables
oQT.Delete
Next oQT
Next WSh

ActiveSheet.Name = sSName

ActiveWorkbook.SaveAs Filename:=sPath & sWBName

ActiveWorkbook.Close

Workbooks.Add

End If

sDate = sDate + 1
dDate = DateAdd("d", 1, dDate)

Next r

 

[Craig B]

So I coded up this nice macro to extract call by call data from a
Symposium, read Sybase, Database. I use a few variables to take the
place of dates and common words to save typing. I use a for...next loop
to zip through a few days of the month or the entire month which ever,
it creates the SQL on the fly, builds the query table, interrogates
the data base, deletes the query table then gives the sheet and
workbook a unique name saves it, then adds a new workbook and starts
over.

This worked fine for months on end and now won't run more than once,
sometimes not at all. I get an error on oQT.refresh
backgroundrefresh:=false

Any ideas on what went wrong? I replaced the text of the password
with ***** for my own piece of mind

dDate = Format(Month(Date - 1) & "/" & iStartDay & "/" & Year(Date -
1), "mm/dd/yyyy")

sDate = Format(dDate, "yyyymmdd")

Workbooks.Add

For r = iStartDay To Day(Date - 1)

sSName = Format(dDate, "mm-dd-yyyy")
sWBName = "Call by Call " & sSName & ".XLS"

If FileExists(sPath & sWBName) = False Then

sSql = ""
sConn =
"ODBC;DSN=Aus1S002;UID=cbecker;PWD=rekceb;SRVR=Aus1s002;DB=blue"
sSql = "SELECT " & sCW & sDate & ".Timestamp, "
sSql = sSql & sCW & sDate & ".CallEvent, "
sSql = sSql & sCW & sDate & ".CallEventName, "
sSql = sSql & sCW & sDate & ".CallID, "
sSql = sSql & sCW & sDate & ".TelsetLoginID, "
sSql = sSql & sCW & sDate & ".AssociatedData, "
sSql = sSql & sCW & sDate & ".Destination, "
sSql = sSql & sCW & sDate & ".EventData, "
sSql = sSql & sCW & sDate & ".Source, "
sSql = sSql & sCW & sDate & ".Time " & vbCrLf
sSql = sSql & "FROM blue.dbo.eCallByCallStat" & sDate & "
" & sCW & sDate & vbCrLf
sSql = sSql & " ORDER BY " & sCW & sDate & ".Timestamp"

Set oQT = ActiveSheet.QueryTables.Add(Connection:=sConn,
Destination:=Range("A1"), Sql:=sSql)

oQT.Refresh BackgroundQuery:=False

Do While oQT.Refreshing = True
Loop

Columns.AutoFit

For Each WSh In ActiveWorkbook.Worksheets
For Each oQT In WSh.QueryTables
oQT.Delete
Next oQT
Next WSh

ActiveSheet.Name = sSName

ActiveWorkbook.SaveAs Filename:=sPath & sWBName

ActiveWorkbook.Close

Workbooks.Add

End If

sDate = sDate + 1
dDate = DateAdd("d", 1, dDate)

Next r

Bummer, nothing on this from anyone?