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? |
change and ODBC Connection
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 wrote in message ups.com... 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? |
All times are GMT +1. The time now is 11:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com