K
Kevin
Hi
the code below works until either a new name is added to the list or
a name is removed can anyone tell me why and how to fix the problem
Sub Weekly_values()
ThisWorkbook.Worksheets("SC INFO").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\KCARTE10\Desktop\working
on\team_sfr.mdb;DefaultDir=C:\Documents and Setti" _
), Array( _
"ngs\KCARTE10\Desktop\working on;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("C33:bd42"))
.CommandText = Array( _
"SELECT team_members_weekly_values.Names,
team_members_weekly_values.`1`, team_members_weekly_values.`2`,
team_members_weekly_values.`3`, team_members_weekly_values.`4`,
team_members_weekly_values.`5`," _
, _
" team_members_weekly_values.`6`,
team_members_weekly_values.`7`, team_members_weekly_values.`8`,
team_members_weekly_values.`9`, team_members_weekly_values.`10`,
team_members_weekly_values.`11`, team_" _
, _
"members_weekly_values.`12`, team_members_weekly_values.`13`,
team_members_weekly_values.`14`, team_members_weekly_values.`15`,
team_members_weekly_values.`16`, team_members_weekly_values.`17`,
team_" _
, _
"members_weekly_values.`18`, team_members_weekly_values.`19`,
team_members_weekly_values.`20`, team_members_weekly_values.`21`,
team_members_weekly_values.`22`, team_members_weekly_values.`23`,
team_" _
, _
"members_weekly_values.`24`, team_members_weekly_values.`25`,
team_members_weekly_values.`26`, team_members_weekly_values.`27`,
team_members_weekly_values.`28`, team_members_weekly_values.`29`,
team_" _
, _
"members_weekly_values.`30`, team_members_weekly_values.`31`,
team_members_weekly_values.`32`, team_members_weekly_values.`33`,
team_members_weekly_values.`34`, team_members_weekly_values.`35`,
team_" _
, _
"members_weekly_values.`36`, team_members_weekly_values.`37`,
team_members_weekly_values.`38`, team_members_weekly_values.`39`,
team_members_weekly_values.`40`, team_members_weekly_values.`41`,
team_" _
, _
"members_weekly_values.`42`, team_members_weekly_values.`43`,
team_members_weekly_values.`44`, team_members_weekly_values.`45`,
team_members_weekly_values.`46`, team_members_weekly_values.`47`,
team_" _
, _
"members_weekly_values.`48`, team_members_weekly_values.`49`,
team_members_weekly_values.`50`, team_members_weekly_values.`51`,
team_members_weekly_values.`52`, team_members_weekly_values.`Total`" &
Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Setting" _
, _
"s\KCARTE10\Desktop\working
on\team_sfr`.team_members_weekly_values team_members_weekly_values")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
thanks
kevin
the code below works until either a new name is added to the list or
a name is removed can anyone tell me why and how to fix the problem
Sub Weekly_values()
ThisWorkbook.Worksheets("SC INFO").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\KCARTE10\Desktop\working
on\team_sfr.mdb;DefaultDir=C:\Documents and Setti" _
), Array( _
"ngs\KCARTE10\Desktop\working on;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("C33:bd42"))
.CommandText = Array( _
"SELECT team_members_weekly_values.Names,
team_members_weekly_values.`1`, team_members_weekly_values.`2`,
team_members_weekly_values.`3`, team_members_weekly_values.`4`,
team_members_weekly_values.`5`," _
, _
" team_members_weekly_values.`6`,
team_members_weekly_values.`7`, team_members_weekly_values.`8`,
team_members_weekly_values.`9`, team_members_weekly_values.`10`,
team_members_weekly_values.`11`, team_" _
, _
"members_weekly_values.`12`, team_members_weekly_values.`13`,
team_members_weekly_values.`14`, team_members_weekly_values.`15`,
team_members_weekly_values.`16`, team_members_weekly_values.`17`,
team_" _
, _
"members_weekly_values.`18`, team_members_weekly_values.`19`,
team_members_weekly_values.`20`, team_members_weekly_values.`21`,
team_members_weekly_values.`22`, team_members_weekly_values.`23`,
team_" _
, _
"members_weekly_values.`24`, team_members_weekly_values.`25`,
team_members_weekly_values.`26`, team_members_weekly_values.`27`,
team_members_weekly_values.`28`, team_members_weekly_values.`29`,
team_" _
, _
"members_weekly_values.`30`, team_members_weekly_values.`31`,
team_members_weekly_values.`32`, team_members_weekly_values.`33`,
team_members_weekly_values.`34`, team_members_weekly_values.`35`,
team_" _
, _
"members_weekly_values.`36`, team_members_weekly_values.`37`,
team_members_weekly_values.`38`, team_members_weekly_values.`39`,
team_members_weekly_values.`40`, team_members_weekly_values.`41`,
team_" _
, _
"members_weekly_values.`42`, team_members_weekly_values.`43`,
team_members_weekly_values.`44`, team_members_weekly_values.`45`,
team_members_weekly_values.`46`, team_members_weekly_values.`47`,
team_" _
, _
"members_weekly_values.`48`, team_members_weekly_values.`49`,
team_members_weekly_values.`50`, team_members_weekly_values.`51`,
team_members_weekly_values.`52`, team_members_weekly_values.`Total`" &
Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Setting" _
, _
"s\KCARTE10\Desktop\working
on\team_sfr`.team_members_weekly_values team_members_weekly_values")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
thanks
kevin