Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OLEDBConnection Properties
How does one specify OLEDBConnection properties and then use it to create a
QueryTable? The idea is to create a Query Table based on data retrieved via an OLE DB driver and to update a pivot table in another sheet based on the created QT (the latter I can do with no problems). The following does not seem to work, since there is no way that I can find to set a reference to the actual connection. Thanx, Sergey Dim cn As OLEDBConnection Dim qt As QueryTable Dim sqlstring As String Worksheets("Sheet2").Activate For Each qt In ActiveSheet.QueryTables qt.Delete Next Dim dtFrom As String Dim dtTo As String dtFrom = <set parm value dtTo = <set parm value sqlstring = <some SQL statement using the above parms <how do I set the reference to cn??? With cn .AlwaysUseConnectionFile = False .BackgroundQuery = False .CommandText = sqlstring .CommandType = xlCmdSql .Connection = "OLEDB;Provider=IBMDA400;" & _ "DSN=DSNNAME;" & _ "UID=USER;PWD=PASSWORD;" .EnableRefresh = False .MaintainConnection = True .RefreshOnFileOpen = False .RefreshPeriod = 0 .RetrieveInOfficeUILang = False .RobustConnect = xlNever '.SavePassword = True .ServerCredentialsMethod = CredentialsMethodIntegrated End With With ActiveSheet.QueryTables.Add( _ Connection:=cn, _ Destination:=Range("A1")) .Name = "Query_Name" .AdjustColumnWidth = True .BackgroundQuery = False .EnableEditing = False .EnableRefresh = True .FieldNames = True .PreserveColumnInfo = False .PreserveFormatting = True .RefreshPeriod = 0 .RefreshStyle = xlInsertDeleteCells .SaveData = False .Refresh End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
OLEDBConnection Properties
Nevermind. I went with just refreshing the existing querytable and
connection, not the ideal situation, but oh well. "krysolov" wrote: How does one specify OLEDBConnection properties and then use it to create a QueryTable? The idea is to create a Query Table based on data retrieved via an OLE DB driver and to update a pivot table in another sheet based on the created QT (the latter I can do with no problems). The following does not seem to work, since there is no way that I can find to set a reference to the actual connection. Thanx, Sergey Dim cn As OLEDBConnection Dim qt As QueryTable Dim sqlstring As String Worksheets("Sheet2").Activate For Each qt In ActiveSheet.QueryTables qt.Delete Next Dim dtFrom As String Dim dtTo As String dtFrom = <set parm value dtTo = <set parm value sqlstring = <some SQL statement using the above parms <how do I set the reference to cn??? With cn .AlwaysUseConnectionFile = False .BackgroundQuery = False .CommandText = sqlstring .CommandType = xlCmdSql .Connection = "OLEDB;Provider=IBMDA400;" & _ "DSN=DSNNAME;" & _ "UID=USER;PWD=PASSWORD;" .EnableRefresh = False .MaintainConnection = True .RefreshOnFileOpen = False .RefreshPeriod = 0 .RetrieveInOfficeUILang = False .RobustConnect = xlNever '.SavePassword = True .ServerCredentialsMethod = CredentialsMethodIntegrated End With With ActiveSheet.QueryTables.Add( _ Connection:=cn, _ Destination:=Range("A1")) .Name = "Query_Name" .AdjustColumnWidth = True .BackgroundQuery = False .EnableEditing = False .EnableRefresh = True .FieldNames = True .PreserveColumnInfo = False .PreserveFormatting = True .RefreshPeriod = 0 .RefreshStyle = xlInsertDeleteCells .SaveData = False .Refresh End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OleDBConnection.Open() blocks with 100% CPU | Excel Programming | |||
Problem opening Excel 2007 file from OleDbConnection | Excel Programming | |||
Properties transferring from excel cells to word file properties | Excel Programming | |||
Properties of a jpg | Excel Programming |