Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQLSetConnectAttr error with Excel - converting to an older database?
On Feb 23, 2:04 pm, "S Davis" <theseanda..._at_gmail.com> wrote:
> Further update, should anyone need this. This will look at an old > (broken) query, grab the SQL from it, and assign new connection > information that you input. If you dont know your connection > information, it is as simple as creating a new query, and then running > Charles' MsgBox.Connection to display it. > > Sub AddQT() > Dim qt As QueryTable > MsgBox ActiveSheet.QueryTables(1).CommandText > MsgBox ActiveSheet.QueryTables(1).Connection > sqlstring = ActiveSheet.QueryTables(1).CommandText > connstring = _ > > "ODBC;DSN=nameofdsn;UID=userid;PWD=password;SERVER=dsnid.server.com;" > With ActiveSheet.QueryTables.Add(connstring, _ > Destination:=Range("I1"), Sql:=sqlstring) > .Refresh > End With > End Sub
Great that it work for you.
A bit OT. Example of creating a new querytable from a tab delimited
file.
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\OracleLog
\Report.txt", Destination:=.Range("A1"))
.Name = "Page" & Format(intSheetCount)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
'.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
'.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("A2").Select
ActiveWindow.FreezePanes = True
It is possible to use the same type of approach for an Oracle database query also.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Fri Feb 23 2007 - 13:17:47 CST