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