change and ODBC Connection

I have a 514 spreadsheets set up pointing to one database via an ODBC
and now I need to point them the in a different direction, same query
in the background , any suggestions?

Hi,

You can refresh your query by code.

Dim strWhat As String
Dim StrFrom As String
Dim strWhere As String
Dim strOrder As String
Dim strSql As String
Dim varSql As Variant
Dim wk as Worksheet
strWhat = "SELECT *"
StrFrom = "FROM yourdata"
strWhere = "WHERE your condition "
strOrder = "ORDER BY your column"
strSql = strWhat & " " & StrFrom & " " & strWhere & " " & strOrder
varSql = StringToArray(strSql)
for each wk in worksheets
wk.activate
With Range("A2").QueryTable
.Connection = _
"OLEDB;Provider=MSDAORA.1;Password=MyPW;User ID=MyID;Data
Source=MyDataSource"
.CommandType = xlCmdSql
.CommandText = varSql
.Refresh BackgroundQuery:=False
End With
next wk
End Sub


Function StringToArray(Query As String) As Variant

Const StrLen = 127 ' Set the maximum string length for
' each element in the array to return
' to 127 characters.
Dim NumElems As Integer
Dim Temp() As String
Dim i
' Divide the length of the string Query by StrLen and
' add 1 to determine how many elements the String array
' Temp should contain, and redimension the Temp array to
' contain this number of elements.

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

' Build the Temp array by sequentially extracting 127
' segments of the Query string into each element of the
' Temp array.

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

' Set the function StringToArray to the Temp array so it
' can be returned to the calling procedure.

StringToArray = Temp

End Function
RegaRDS
jy

<[email protected]> wrote in message
news:[email protected]...
>I have a 514 spreadsheets set up pointing to one database via an ODBC
> and now I need to point them the in a different direction, same query
> in the background , any suggestions?
>