September 3, 2011 at 4:54 am
Currently I'm working on some code to modify a spreadsheet before it get's imported into SSIS, using a SSIS script task, so VB.Net.
However I'm having real trouble figuring out the correct code for doing a copy, paste as values for a particular range. In other words: my spreadsheet has formulas in it and I want to convert this to values before it gets imported. I have no trouble figuring out what it should be like in VBA, I just can't get it to work in VB.Net.
Does any one have a clue, or even better a code snippet on how to do this?
I can find alll sorts of examples in VB.net for excel just no Copy- paste (as values) ones.
Henk
September 3, 2011 at 6:59 am
I'm not sure that this will help.
Public Sub copyRetangle(filename As String)
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oWsS As Excel.Worksheet
Dim oWsD As Excel.Worksheet
Try
'Dim oRng As Excel.Range
oXL = New Excel.Application
oXL.Visible = True
oWB = oXL.Workbooks.Open(filename)
oWsS = oWB.Sheets(1)
oWsD = oWB.Sheets(2)
Dim sha As Excel.Shape
oWsS.Shapes().Range(1).Item(1).Copy()
oWsD.Select()
oWsD.Paste()
sha = oWsD.Shapes().Range(1).Item(1)
'Adjust the shape
sha.Top = 1.2
sha.Left = 2.6
sha.Height = 15
sha.Width = 16
oWB.Save()
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
oWsS = Nothing
oWsD = Nothing
oWB.Close()
oXL.Quit()
End Try
End Sub
Mike Feng
Also check out this link.
http://www.thescarms.com/dotnet/ExcelObject.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 4, 2011 at 2:05 am
No it doesn't seem to contain any code that would help me to copy paste (Special) a range in excel.
Unfortunately.....
If anybody has some code that does exactly that please let me know.....
September 4, 2011 at 5:57 am
The following is a Copy, Paste Special. Note that the last post contains the correction.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 4, 2011 at 9:28 am
Hopefully the following will be of help to you.
http://www.codeguru.com/forum/showthread.php?t=352162
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 5, 2011 at 9:39 pm
Thanks Welsh Corgi, it worked.
What I find kind of disappointing though is the error messages that you get when you get it wrong. They don't provide any usefull info at all.
Henk
September 7, 2011 at 7:48 am
Are you trying to just get the calculated values imported into a table, or are you trying the actual formulas imported into a table? If you're just trying to get the calculated values, then why are you needing to copy/paste values only in the source Excel file before importing it? You should be able to import the Excel file as-is, with only the values importing into your destination table (assuming your table is good to go, value types match, any necessary data conversions/transforms have been done, etc).
September 7, 2011 at 9:28 am
I have to agree that the messages are cryptic.
Regards.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply