D
Dale Marques
I am trying to run a program that accesses one of our
database systems and creates a query table. I keep
getting the following error message "Run-Time Error 1004:
The operation cannot be done because the data is
refreshing in the background." If anyone has any
suggestions that would be great. My code is as follows:
Sub LFQuery()
' LFQuery Macro
' Macro recorded 6/21/2004 by Randy Kreider and Dale D.
Marques
' Declaring and Setting Variables
var1 = CStr(Worksheets("Customer").Range("F2").Value)
var2 = CStr(Worksheets("Customer").Range("F4").Value)
Worksheets("Customer").Unprotect
Range("A7:L18000").ClearContents
' Import Query Table
With ActiveSheet.QueryTables().Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for
Oracle};UID=;PWD=;SERVER=cdapd;", _
Destination:=Worksheets("Customer").Range("A7"))
.Sql = " SELECT CDM_LOCATIONS.OP_CENTER,
CDM_ESSR.METER_READ_DT, CDM_BILL_ACCOUNTS.BILL_ACCT_NUM,
CDM_BILL_ACCOUNTS.CUSTOMER_NAME,
CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION,
CDM_ESSR.REV_YR_MO, SUM(CDM_ESSR.KWH), SUM
(CDM_ESSR.MAXIMUM_DEMAND), SUM(CDM_ESSR.BILLED_DEMAND), SUM
(CDM_ESSR.COUNT_AS_BILL), SUM(CDM_ESSR.BILLING_DAYS)" & _
" FROM CDADM.CDM_LOCATIONS CDM_LOCATIONS,
CDADM.CDM_ESSR CDM_ESSR, CDADM.CDM_BILL_ACCOUNTS
CDM_BILL_ACCOUNTS, CDADM.CDM_SERVICE_SUPPLIERS
CDM_SERVICE_SUPPLIERS, CDADM.CDM_TARIFF_SCHEDULES
CDM_TARIFF_SCHEDULES " & _
" WHERE (CDM_LOCATIONS.LOCATION_GK_PK =
CDM_ESSR.LOCATION_FK) AND
(CDM_BILL_ACCOUNTS.BILL_ACCT_GK_PK =
CDM_ESSR.BILL_ACCT_FK ) AND (
CDM_TARIFF_SCHEDULES.TARIFF_SCHED_GK_PK =
CDM_ESSR.TARIFF_SCHED_FK) AND (CDM_ESSR.METER_READ_DT
BETWEEN TO_DATE('" & var1 & "','YYYYMMDD') AND TO_DATE('"
& var2 & "','YYYYMMDD')) AND
(CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION IN
(( 'LP6 ')) ) AND
(CDM_SERVICE_SUPPLIERS.SERVICE_SUPPLIER_GK_PK =
CDM_BILL_ACCOUNTS.SERVICE_SUPPLIER_FK)" & _
" GROUP BY CDM_LOCATIONS.OP_CENTER,
CDM_ESSR.METER_READ_DT, CDM_BILL_ACCOUNTS.BILL_ACCT_NUM,
CDM_BILL_ACCOUNTS.CUSTOMER_NAME,
CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION,
CDM_ESSR.REV_YR_MO"
' Query Table Formatting
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery = False
.SavePassword = True
.SaveData = True
End With
Worksheets("Customer").Protect DrawingObjects = True,
contents = True, Scenarios = True
End Sub
database systems and creates a query table. I keep
getting the following error message "Run-Time Error 1004:
The operation cannot be done because the data is
refreshing in the background." If anyone has any
suggestions that would be great. My code is as follows:
Sub LFQuery()
' LFQuery Macro
' Macro recorded 6/21/2004 by Randy Kreider and Dale D.
Marques
' Declaring and Setting Variables
var1 = CStr(Worksheets("Customer").Range("F2").Value)
var2 = CStr(Worksheets("Customer").Range("F4").Value)
Worksheets("Customer").Unprotect
Range("A7:L18000").ClearContents
' Import Query Table
With ActiveSheet.QueryTables().Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for
Oracle};UID=;PWD=;SERVER=cdapd;", _
Destination:=Worksheets("Customer").Range("A7"))
.Sql = " SELECT CDM_LOCATIONS.OP_CENTER,
CDM_ESSR.METER_READ_DT, CDM_BILL_ACCOUNTS.BILL_ACCT_NUM,
CDM_BILL_ACCOUNTS.CUSTOMER_NAME,
CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION,
CDM_ESSR.REV_YR_MO, SUM(CDM_ESSR.KWH), SUM
(CDM_ESSR.MAXIMUM_DEMAND), SUM(CDM_ESSR.BILLED_DEMAND), SUM
(CDM_ESSR.COUNT_AS_BILL), SUM(CDM_ESSR.BILLING_DAYS)" & _
" FROM CDADM.CDM_LOCATIONS CDM_LOCATIONS,
CDADM.CDM_ESSR CDM_ESSR, CDADM.CDM_BILL_ACCOUNTS
CDM_BILL_ACCOUNTS, CDADM.CDM_SERVICE_SUPPLIERS
CDM_SERVICE_SUPPLIERS, CDADM.CDM_TARIFF_SCHEDULES
CDM_TARIFF_SCHEDULES " & _
" WHERE (CDM_LOCATIONS.LOCATION_GK_PK =
CDM_ESSR.LOCATION_FK) AND
(CDM_BILL_ACCOUNTS.BILL_ACCT_GK_PK =
CDM_ESSR.BILL_ACCT_FK ) AND (
CDM_TARIFF_SCHEDULES.TARIFF_SCHED_GK_PK =
CDM_ESSR.TARIFF_SCHED_FK) AND (CDM_ESSR.METER_READ_DT
BETWEEN TO_DATE('" & var1 & "','YYYYMMDD') AND TO_DATE('"
& var2 & "','YYYYMMDD')) AND
(CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION IN
(( 'LP6 ')) ) AND
(CDM_SERVICE_SUPPLIERS.SERVICE_SUPPLIER_GK_PK =
CDM_BILL_ACCOUNTS.SERVICE_SUPPLIER_FK)" & _
" GROUP BY CDM_LOCATIONS.OP_CENTER,
CDM_ESSR.METER_READ_DT, CDM_BILL_ACCOUNTS.BILL_ACCT_NUM,
CDM_BILL_ACCOUNTS.CUSTOMER_NAME,
CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION,
CDM_ESSR.REV_YR_MO"
' Query Table Formatting
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery = False
.SavePassword = True
.SaveData = True
End With
Worksheets("Customer").Protect DrawingObjects = True,
contents = True, Scenarios = True
End Sub