Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Truncate, Insert, or Update SQL command in EXCEL Macro
I want to do other types of SQL commands in an EXCEL macro besides standard
"select" queries. In particular, I'd like to do a "truncate table" , and also an "Insert" and an "Update". I recorded a macro to do a "select", thinking I would then just edit the "select" statement to do what I want, but it does not appear to do anything when I execute it. Here's a sample of the code that was recorded. The query is passed to an ORACLE database through an ODBC connection. Step 2 will be to pass some parameters (data) to SQL in the insert and update commands. Any suggestions and examples would be most sincerely appreciated. Thanks! Sub Exec_Query() ' ' With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=lss;UID=userid;PWD=password;DBQ=LSS; DBA= W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST= T;BTD=F;BAM=IfAllSuccessful;NUM=N" _ ), Array("LS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000; TLO=O;")), Destination:= _ Range("A1")) ..CommandText = Array( _ "SELECT var1, var2" & Chr(13) & "" & Chr(10) & "FROM master_tbl" & Chr(13) & "" & Chr(10) & "WHERE (var1 = var2)") ..Name = "Query" ..FieldNames = True ..RowNumbers = False ..FillAdjacentFormulas = False ..PreserveFormatting = True ..RefreshOnFileOpen = False ..BackgroundQuery = True ..RefreshStyle = xlInsertDeleteCells ..SavePassword = True ..SaveData = True ..AdjustColumnWidth = True ..RefreshPeriod = 0 ..PreserveColumnInfo = True ..Refresh BackgroundQuery:=False End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Truncate, Insert, or Update SQL command in EXCEL Macro
"LD" wrote:
I want to do other types of SQL commands in an EXCEL macro besides standard "select" queries. In particular, I'd like to do a "truncate table" , and also an "Insert" and an "Update". I recorded a macro to do a "select", thinking I would then just edit the "select" statement to do what I want, but it does not appear to do anything when I execute it. Here's a sample of the code that was recorded. The query is passed to an ORACLE database through an ODBC connection. Step 2 will be to pass some parameters (data) to SQL in the insert and update commands. Any suggestions and examples would be most sincerely appreciated. Thanks! Sub Exec_Query() ' ' With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=lss;UID=userid;PWD=password;DBQ=LSS; DBA= W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST= T;BTD=F;BAM=IfAllSuccessful;NUM=N" _ ), Array("LS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000; TLO=O;")), Destination:= _ Range("A1")) .CommandText = Array( _ "SELECT var1, var2" & Chr(13) & "" & Chr(10) & "FROM master_tbl" & Chr(13) & "" & Chr(10) & "WHERE (var1 = var2)") .Name = "Query" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub One way to do that is using ADO, Connection and Command objects. Pass the Command object strings containing SQL queries and then use Execute Sub ADOTest() Dim Cnn As ADODB.Connection Dim Cmd As ADODB.Command Set Cnn = New ADODB.Connection Cnn.ConnectionString = _ "Data Source='MyDataSource';" Cnn.ConnectionTimeout = 30 Cnn.Open Set Cmd = New ADODB.Command Cmd.ActiveConnection = Cnn strSQLCreate = "CREATE TABLE MyTable " & _ "(Field1 int , " & _ "Field2 char(20) NULL, " & _ "Field3 datetime NULL)" Cmd.CommandText = strSQLCreate Cmd.Execute strSQLInsert = "INSERT INTO MyTable " & _ "VALUES (987, 'some value', '11/25/2006')" Cmd.CommandText = strSQLInsert Cmd.Execute strSQLUpdate = "UPDATE MyTable " & _ "SET MyTable.Field2 = 'another value' " & _ "WHERE Field1 = 987" Cmd.CommandText = strSQLUpdate Cmd.Execute strSQLTruncate = "TRUNCATE TABLE MyTable" Cmd.CommandText = strSQLTruncate Cmd.Execute strSQLDrop = "DROP TABLE MyTable" Cmd.CommandText = strSQLDrop Cmd.Execute Cnn.Close Set Cnn = Nothing End Sub I use SQL Server, but I think you can easily change the queries to work with your database. You also need to change the connectio string. Hope this helps some. -- urkec |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Truncate, Insert, or Update SQL command in EXCEL Macro
Seems I may not have all the correct "object libraries" selected to use ADO,
but cannot determine which to select when I go to Tools/References. The error message I get for the very first dimension statement is "Compile Error: User-defined type not defined". Any suggestions? "urkec" wrote: "LD" wrote: I want to do other types of SQL commands in an EXCEL macro besides standard "select" queries. In particular, I'd like to do a "truncate table" , and also an "Insert" and an "Update". I recorded a macro to do a "select", thinking I would then just edit the "select" statement to do what I want, but it does not appear to do anything when I execute it. Here's a sample of the code that was recorded. The query is passed to an ORACLE database through an ODBC connection. Step 2 will be to pass some parameters (data) to SQL in the insert and update commands. Any suggestions and examples would be most sincerely appreciated. Thanks! Sub Exec_Query() ' ' With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=lss;UID=userid;PWD=password;DBQ=LSS; DBA= W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST= T;BTD=F;BAM=IfAllSuccessful;NUM=N" _ ), Array("LS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000; TLO=O;")), Destination:= _ Range("A1")) .CommandText = Array( _ "SELECT var1, var2" & Chr(13) & "" & Chr(10) & "FROM master_tbl" & Chr(13) & "" & Chr(10) & "WHERE (var1 = var2)") .Name = "Query" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub One way to do that is using ADO, Connection and Command objects. Pass the Command object strings containing SQL queries and then use Execute Sub ADOTest() Dim Cnn As ADODB.Connection Dim Cmd As ADODB.Command Set Cnn = New ADODB.Connection Cnn.ConnectionString = _ "Data Source='MyDataSource';" Cnn.ConnectionTimeout = 30 Cnn.Open Set Cmd = New ADODB.Command Cmd.ActiveConnection = Cnn strSQLCreate = "CREATE TABLE MyTable " & _ "(Field1 int , " & _ "Field2 char(20) NULL, " & _ "Field3 datetime NULL)" Cmd.CommandText = strSQLCreate Cmd.Execute strSQLInsert = "INSERT INTO MyTable " & _ "VALUES (987, 'some value', '11/25/2006')" Cmd.CommandText = strSQLInsert Cmd.Execute strSQLUpdate = "UPDATE MyTable " & _ "SET MyTable.Field2 = 'another value' " & _ "WHERE Field1 = 987" Cmd.CommandText = strSQLUpdate Cmd.Execute strSQLTruncate = "TRUNCATE TABLE MyTable" Cmd.CommandText = strSQLTruncate Cmd.Execute strSQLDrop = "DROP TABLE MyTable" Cmd.CommandText = strSQLDrop Cmd.Execute Cnn.Close Set Cnn = Nothing End Sub I use SQL Server, but I think you can easily change the queries to work with your database. You also need to change the connectio string. Hope this helps some. -- urkec |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i write a macro to truncate alpha-numeric data? | Excel Discussion (Misc queries) | |||
How do I set up macro to truncate column/field? | Excel Programming | |||
How can I insert a run macro command in the IF function? | Excel Programming | |||
Can I insert roundup command into different formulas in excel? | Excel Worksheet Functions | |||
How to execute an SQL insert/update from Excel for Mac | Excel Programming |