Query Refresh Error

G

Gibu John George

Hi,

I am using Microsoft Query to guery an excel file and then get the queried
data into another excel sheet. But each time I try the same VBA code , with
the query conditions changed, for another excel it returns an error "Run-Time
error "1004" Application definition or Object definition error " when it
tries to execute ".Refresh BackgroundQuery:=False"

But when I record a new query macro specific to the new excel sheet it works
fine.

anybody with any resolutions/suggestion?

mail : (e-mail address removed)

VBA CODE:

Sheets("Sheet3").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\Program Files\Apache Software
Foundation\Tomcat 6.0\webapps\Web_Matrics\project_data\RP\ASTC\" &
current_week & "\RP_ATSC" _
), Array( _
"_" & current_week_date_NXP & "_" & current_time &
".xls;DefaultDir=C:\Program Files\Apache Software Foundation\Tomcat
6.0\webapps\Web_Matrics\project_data\RP\ASTC\" & current_week & ";" _
), Array("DriverId=790;MaxBufferSize=2048;PageTimeout=5;")),
Destination:= _
Range("A1"))
.CommandText = Array( _
"SELECT `DataSheet$`.`Request Type`, `DataSheet$`.`Sub System`,
`DataSheet$`.Status" & Chr(13) & "" & Chr(10) & "FROM `DataSheet$`
`DataSheet$`" & Chr(13) & "" & Chr(10) & "WHERE (`DataSheet$`.`Request
Type`='CR') AND (`DataSheet$`.`Resolved Week`= & current_week)" _
, _
"OR (`DataSheet$`.`Request Type`='CR') AND (`DataSheet$`.`Resolved
Wk`= & current_week)" _
)
.Name = "Query from Excel Files1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top