C
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
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