| Home |
| Search |
| Today's Posts |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
External Data Failure VBA Code
On Jun 6, 4:20 pm, Craig B wrote:
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=Aus 1s002;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? |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mouse scroll failure when looking at code | Excel Worksheet Functions | |||
| code failure | Excel Programming | |||
| VBA Code Reference to Solver Failure ! | Excel Programming | |||
| Chart versus Work Sheet Code Failure | Excel Programming | |||
| Autorefreshing excell external data while opening from VBA code | Excel Programming | |||
Threaded Mode
