InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comMon, 06 Oct 2008 11:14:06 GMT20http://www.teradata.com/teradataforum/Topic11367-10-1.aspxOption ExplicitOption Base 1Sub RunSQLComments() Dim iName As Integer Dim r As Range Dim sCom As String, sCom2 As String Dim i As Integer, sVar As String, varValue As Variant, bInVar As Boolean Application.Cursor = xlWait Application.StatusBar = "In Progress..." 'For iName = 1 To ActiveWorkbook.names.count 'Set r = ActiveWorkbook.names(iName).RefersToRange Set r = Selection.Range("A1") sCom = "" On Error Resume Next sCom = r.Comment.Text On Error GoTo 0 If InStr(sCom, ":") > 0 Then sCom = right(sCom, Len(sCom) - InStr(sCom, ":")) End If If (InStr(UCase(sCom), "SEL ") > 0) Or (InStr(UCase(sCom), "SELECT") > 0) Or (InStr(UCase(sCom), "CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), "INSERT INTO") > 0) Or (InStr(UCase(sCom), ";CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), ";INSERT INTO") > 0) Or (InStr(UCase(sCom), "EXEC") > 0) Then 'It's a SQL command 'Look for variables and substitute in... sVar = "" sCom2 = "" bInVar = False For i = 1 To Len(sCom) If Not bInVar = True Then If Mid(sCom, i, 1) = "[" Then bInVar = True sVar = "" Else sCom2 = sCom2 & Mid(sCom, i, 1) End If Else If Mid(sCom, i, 1) <> "]" Then sVar = sVar & Mid(sCom, i, 1) Else varValue = "NULL" On Error Resume Next varValue = Range(sVar) On Error GoTo 0 sCom2 = sCom2 & CStr(varValue) bInVar = False End If End If Next Call runSQL1(sCom2, r) End If 'Next 'Format the 1st row with the sub 'Tidy headings'Range(ActiveCell, ActiveCell.End(xlToRight)).SelectCall TidyQueryHeadingsRange("A1").SelectApplication.Cursor = xlDefaultApplication.StatusBar = FalseEnd SubSub runSQL1(sCommand As String, destCell As Range) Dim userID As String userID = "user" Dim userPass As String userPass = "password" Const connDSN As String = "database" Const connDB As String = "" Dim bQueryThere As Boolean, sTemp As Variant bQueryThere = True On Error Resume Next sTemp = destCell.QueryTable.Name If Err > 0 Then bQueryThere = False On Error GoTo 0 On Error Resume Next If Not bQueryThere Then With ActiveSheet.QueryTables.Add( _ Connection:="ODBC;DSN=" & connDSN & ";UID=" & userID & ";PWD=" & userPass & ";DATABASE=" & connDB & ";", _ Destination:=destCell) .Sql = longStringToArray(sCommand) .FieldNames = True .RefreshStyle = xlOverwriteCells .RowNumbers = False .FillAdjacentFormulas = True .RefreshOnFileOpen = False .HasAutoFormat = False .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With Else With destCell.QueryTable .Connection = "ODBC;DSN=" & connDSN & ";UID=" & userID & ";PWD=" & userPass & ";DATABASE=" & connDB & ";" .Sql = longStringToArray(sCommand) .Refresh False End With End If If Err > 0 Then MsgBox "Data Access Error: " & vbCrLf & Err.Description, vbOKOnly End If On Error GoTo 0End SubFunction longStringToArray(sLong As String) As Variant 'Chop a string into an array of bits so can be passed in as SQL argument Dim temp() As String Dim i As Integer, iLine As Integer, iCol As Integer i = 0 iLine = 0 iCol = 256 Do While i < Len(sLong) i = i + 1 iCol = iCol + 1 If iCol > 255 Then iLine = iLine + 1 ReDim Preserve temp(iLine) iCol = 1 End If temp(iLine) = temp(iLine) & Mid(sLong, i, 1) Loop longStringToArray = tempEnd FunctionMon, 05 May 2008 18:05:24 GMTinsanity82007http://www.teradata.com/teradataforum/Topic11367-10-1.aspxHi guys,I have the below to run queries from comments in excel. This works for all of the queries I run except for those with volatile tables.I tried adding "(InStr(UCase(sCom), "CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), "INSERT INTO") > 0) Or (InStr(UCase(sCom), ";CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), ";INSERT INTO") > 0)" to get it to work, but it didn't.I added and tested "(InStr(UCase(sCom), "EXEC") > 0)" successfully for macros.I then tried creating a macro but that came up with an error as well, which I've already read up about saying that Teradata can't create macros with volatile tables included, which I can't understand why.I did try writing the query so that it has no volatile tables, but I can't define a limited enough source of data for it not to run out of spool space.Is there any excel add in out there that I can use to get data from our data warehouse with queries that contain volatile tables? My VB knowledge is very limited at best and I can't modify this.Mon, 05 May 2008 18:04:54 GMTinsanity82007