Author |
Topic |
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-30 : 13:03:23
|
I am exporting to Excel in vb.net using Visual Studios for Applicaiton. I think I am on to somehting here but I am rather baffled. I have run the follwoing code in debug and it runs just fine there are no errors. however it does not update the cell in the spreadsheet. any advise as to what I might have done worng.Dim AccessConnection As New System.Data.OleDb.OleDbConnection Dim _conn As String Dim SchemaTable As DataTable Dim _filename As String = "C:\Documents and Settings\dhumphries\Desktop\test.xls" _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;" Dim _connection As OleDbConnection = New OleDbConnection(_conn) Dim _command As OleDbCommand = New OleDbCommand _command.Connection = _connection Try _command.CommandText = "Update [city$c4:c4] set f1 ='DAN'" _connection.Open() _command.ExecuteNonQuery() 'MessageBox.Show("The Data Imported Successfully!") Catch e1 As Exception 'MessageBox.Show("Error!.Your Excel Spread Sheet has some problems.") End Try Dts.TaskResult = Dts.Results.Success End Sub |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-30 : 13:12:45
|
to export single, isnt it enough to use OLEDB source task with your query and excel destination task pointing to your excel file inside a data flow task? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-30 : 13:13:31
|
or even use OPENROWSET rather than SSIS if its for an adhoc need. |
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-30 : 14:07:25
|
This will need to be a runable job in the end which is why I would like it to be is ssis. I can not use an excel destination becuase I need to be able to update a specific cell in an exsiting file. for example write the total from the query to cell h7. the excell destination I do not beleive allows you to specify a cell value like that. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-30 : 14:10:12
|
then i think its better to use openrowset and call the query from your sql job. |
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-30 : 15:36:13
|
OK I see what you are saying but the Ad Hoc Distributed Queries is not turned on. I have tried to get this turned on but becuase this is another deparments server I am unlikely to get that permission. So is it possible to do this using a VSA script. It seems like that would work but I am not exactly sure what the code would look like. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-31 : 03:27:12
|
http://www.eggheadcafe.com/software/aspnet/30605392/vbscript--excel-read-fil.aspx |
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2009-01-05 : 09:14:44
|
For anyone who is interested here is how I finally resolved this issue.The first step in the control flow is an SQL Task. I run my Select sum statement and store the sum of the value in a variable.I then use a script component and use the dts varialbe setup in the previous step to update my excel spread sheet. The Script uses the System.Data.OleDb.OleDbConnection method to update the Excel sheet. If you are using a similiar mersion of Visual studios for SQL server as I am you may have tried to use the ExcelObj library only to find you have no reference for it. in fact you have no COM Tab at all. so I am using this method. The biggest thing you want to know about this method is that the script will be reading your excel file as if it were reading an SQL Table. so that means you need to have a header row and you need to have some sort of unique identifier in each row. I just made two hidden values. The first hidden value is the header I called i and a. pretty generic but simply id and amount. the second hidden value is the i column itself. It is what allows me to call the write cell in the where statement. Attached is the code and hopefully you may find this helpful if you are new at SSIS As I am.' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic' The ScriptMain class is the entry point of the Script Task.Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.Data.OleDbImports System.Data.SqlClientPublic Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() ' ' Add your code here Try Dim MyConnection As System.Data.OleDb.OleDbConnection Dim myCommand As New System.Data.OleDb.OleDbCommand Dim sql As String Dim SVNG8Amt As String = CType(Dts.Variables("User::SVNG8Amt").Value, String) If SVNG8Amt = "" Then SVNG8Amt = "0" End If MyConnection = New System.Data.OleDb.OleDbConnection _ ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _ "'C:\Documents and Settings\dhumphries\Desktop\test.xls';Extended Properties=Excel 8.0") MyConnection.Open() myCommand.Connection = MyConnection sql = "Update [CWLP$] set a = " & SVNG8Amt & " where i='5h'" myCommand.CommandText = sql myCommand.ExecuteNonQuery() MyConnection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try MsgBox("Updated ") Dts.TaskResult = Dts.Results.Success End SubEnd Class |
|
|
Rashmi_P
Starting Member
1 Post |
Posted - 2012-07-27 : 17:33:11
|
Hi,I tried the solution mentioned by you but it didn't work for me. I am getting error Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.OleDb.OleDbException: External table is not in the expected format. Did you ever come across this error while working on the example code?Please reply.Rashmi.quote: Originally posted by dhumphries For anyone who is interested here is how I finally resolved this issue.The first step in the control flow is an SQL Task. I run my Select sum statement and store the sum of the value in a variable.I then use a script component and use the dts varialbe setup in the previous step to update my excel spread sheet. The Script uses the System.Data.OleDb.OleDbConnection method to update the Excel sheet. If you are using a similiar mersion of Visual studios for SQL server as I am you may have tried to use the ExcelObj library only to find you have no reference for it. in fact you have no COM Tab at all. so I am using this method. The biggest thing you want to know about this method is that the script will be reading your excel file as if it were reading an SQL Table. so that means you need to have a header row and you need to have some sort of unique identifier in each row. I just made two hidden values. The first hidden value is the header I called i and a. pretty generic but simply id and amount. the second hidden value is the i column itself. It is what allows me to call the write cell in the where statement. Attached is the code and hopefully you may find this helpful if you are new at SSIS As I am.' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic' The ScriptMain class is the entry point of the Script Task.Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.Data.OleDbImports System.Data.SqlClientPublic Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() ' ' Add your code here Try Dim MyConnection As System.Data.OleDb.OleDbConnection Dim myCommand As New System.Data.OleDb.OleDbCommand Dim sql As String Dim SVNG8Amt As String = CType(Dts.Variables("User::SVNG8Amt").Value, String) If SVNG8Amt = "" Then SVNG8Amt = "0" End If MyConnection = New System.Data.OleDb.OleDbConnection _ ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _ "'C:\Documents and Settings\dhumphries\Desktop\test.xls';Extended Properties=Excel 8.0") MyConnection.Open() myCommand.Connection = MyConnection sql = "Update [CWLP$] set a = " & SVNG8Amt & " where i='5h'" myCommand.CommandText = sql myCommand.ExecuteNonQuery() MyConnection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try MsgBox("Updated ") Dts.TaskResult = Dts.Results.Success End SubEnd Class
|
|
|
|