Hi, I am trying to run the following code:

Dim Enddate As String
Dim sqlarray As Variant
Dim sqltext As String


Private Sub CommandButton1_Click()
AR = txtAR.Text
Startdate = txtStart.Text
Enddate = txtEnd.Text

sqltext = "SELECT oe1.loc, oe1.NAME, sum(inv.ttl_invc_amt), sr.SR_AREA,
oe.INTEGRATION_ID FROM siebel.s_invoice inv INNER JOIN siebel.S_SRV_REQ sr ON
inv.SR_ID = sr.row_id INNER JOIN siebel.s_org_ext oe ON sr.X_BILL_TO_ID_YORK
= oe.row_id INNER JOIN siebel.s_org_ext oe1 ON sr.CST_OU_ID = oe1.row_id
INNER JOIN SIEBEL.S_ADDR_ORG adr ON oe1.PR_ADDR_ID = adr.row_id INNER JOIN
SIEBEL.S_CONTACT c ON sr.CST_CON_ID = c.ROW_ID WHERE oe.Integration_id =
'%ARNumber%' and inv.invc_dt = '%startdate%' and inv.invc_dt <= '%enddate%'
and inv.X_LAWSON_INVOICE_NUMBER_YORK is not null GROUP BY oe1.loc, oe1.NAME,
sr.SR_AREA, oe.INTEGRATION_ID"
sqltext = Replace(sqltext, "%ARNumber%", AR)
sqltext = Replace(sqltext, "%StartDate%", Format$(Startdate, "dd-mmm-yy"))
sqltext = Replace(sqltext, "%EndDate%", Format$(Enddate, "dd-mmm-yy"))

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;DBA=W;APA=T; EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F; FRL=F;BAM=" _
), Array("IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC =10;TLO=0;")),
Destination _
:=Range("A1"))
.CommandText = sqltext
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I keep getting a run time error when the .Refresh BackgroundQuery:=False
statement runs.

Any help will be apprciated.

Thanks

Dwaine,

All DAO errors will always err out at the .Refresh line. The problem is your
issue
could be in the SQL, the connect string, or anywhere else. I would suggest
you put in
an "On Error GoTo" before your QueryTables.Add line and have it skip to a
routine that
traps the error. The error message may give you more of a clue as to where
the issue
stems from. Here is an example (but untested) error routine code:

Dim errErrs As ODBCErrors
Dim errErr As ODBCError
....
....
On Error GoTo XERR
....QueryTables.Add...
....
....
....
Exit Sub
XERR:
Set errErrs = Application.ODBCErrors
If errErrs.Count 0 Then
For Each errErr In errErrs
strMsg = strMsg & "#" & errErr.SqlState & " = " & errErr.ErrorString
Next errErr
MsgBox strMsg, vbCritical, "ODBC ERROR"
End If
If Err.Number < 0 Then
MsgBox Err.Number & vbCr & Err.Source & vbCr & vbCr & Err.Description,
vbCritical
Err.Clear
End If
End Sub

HTH somehow.


"Dwaine Horton" wrote:

Hi, I am trying to run the following code:

Dim Enddate As String
Dim sqlarray As Variant
Dim sqltext As String


Private Sub CommandButton1_Click()
AR = txtAR.Text
Startdate = txtStart.Text
Enddate = txtEnd.Text

sqltext = "SELECT oe1.loc, oe1.NAME, sum(inv.ttl_invc_amt), sr.SR_AREA,
oe.INTEGRATION_ID FROM siebel.s_invoice inv INNER JOIN siebel.S_SRV_REQ sr ON
inv.SR_ID = sr.row_id INNER JOIN siebel.s_org_ext oe ON sr.X_BILL_TO_ID_YORK
= oe.row_id INNER JOIN siebel.s_org_ext oe1 ON sr.CST_OU_ID = oe1.row_id
INNER JOIN SIEBEL.S_ADDR_ORG adr ON oe1.PR_ADDR_ID = adr.row_id INNER JOIN
SIEBEL.S_CONTACT c ON sr.CST_CON_ID = c.ROW_ID WHERE oe.Integration_id =
'%ARNumber%' and inv.invc_dt = '%startdate%' and inv.invc_dt <= '%enddate%'
and inv.X_LAWSON_INVOICE_NUMBER_YORK is not null GROUP BY oe1.loc, oe1.NAME,
sr.SR_AREA, oe.INTEGRATION_ID"
sqltext = Replace(sqltext, "%ARNumber%", AR)
sqltext = Replace(sqltext, "%StartDate%", Format$(Startdate, "dd-mmm-yy"))
sqltext = Replace(sqltext, "%EndDate%", Format$(Enddate, "dd-mmm-yy"))

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;DBA=W;APA=T; EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F; FRL=F;BAM=" _
), Array("IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC =10;TLO=0;")),
Destination _
:=Range("A1"))
.CommandText = sqltext
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I keep getting a run time error when the .Refresh BackgroundQuery:=False
statement runs.

Any help will be apprciated.

Thanks

I found my problem. In the replace statement my values didn't match excatly
to what I had in the sql statement. It is case sensitive.

"Dwaine Horton" wrote:

Hi, I am trying to run the following code:

Dim Enddate As String
Dim sqlarray As Variant
Dim sqltext As String


Private Sub CommandButton1_Click()
AR = txtAR.Text
Startdate = txtStart.Text
Enddate = txtEnd.Text

sqltext = "SELECT oe1.loc, oe1.NAME, sum(inv.ttl_invc_amt), sr.SR_AREA,
oe.INTEGRATION_ID FROM siebel.s_invoice inv INNER JOIN siebel.S_SRV_REQ sr ON
inv.SR_ID = sr.row_id INNER JOIN siebel.s_org_ext oe ON sr.X_BILL_TO_ID_YORK
= oe.row_id INNER JOIN siebel.s_org_ext oe1 ON sr.CST_OU_ID = oe1.row_id
INNER JOIN SIEBEL.S_ADDR_ORG adr ON oe1.PR_ADDR_ID = adr.row_id INNER JOIN
SIEBEL.S_CONTACT c ON sr.CST_CON_ID = c.ROW_ID WHERE oe.Integration_id =
'%ARNumber%' and inv.invc_dt = '%startdate%' and inv.invc_dt <= '%enddate%'
and inv.X_LAWSON_INVOICE_NUMBER_YORK is not null GROUP BY oe1.loc, oe1.NAME,
sr.SR_AREA, oe.INTEGRATION_ID"
sqltext = Replace(sqltext, "%ARNumber%", AR)
sqltext = Replace(sqltext, "%StartDate%", Format$(Startdate, "dd-mmm-yy"))
sqltext = Replace(sqltext, "%EndDate%", Format$(Enddate, "dd-mmm-yy"))

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;DBA=W;APA=T; EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F; FRL=F;BAM=" _
), Array("IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC =10;TLO=0;")),
Destination _
:=Range("A1"))
.CommandText = sqltext
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I keep getting a run time error when the .Refresh BackgroundQuery:=False
statement runs.

Any help will be apprciated.

Thanks