Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Go Back  Xtreme Visual Basic Talk > > > Copying Data from Excel Range To Array or some other source


Reply
 
Thread Tools Display Modes
  #1  
Old 04-28-2010, 01:11 PM
Jahnavi Jahnavi is offline
Newcomer
 
Join Date: Apr 2010
Posts: 3
Default Copying Data from Excel Range To Array or some other source


HI All,

I am a newbie to Vb.net. I am using .NEt 2.0 and Vb 2005.

I am trying to open an exisiting excel sheet and then I want to copy a range of cells in to an array in one shot instead of looping thru cell by cell.
And then I want to do some calculations and then move them to database tables.

I am stumbled at the part of transfering data from excel to an array.

Hope to get an answer soon.

Let me know if you need some more information from me.

Thanks a lot in advance
Reply With Quote
  #2  
Old 04-28-2010, 02:51 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Jahnavi,

Welcome to the forum.

For general information on automating Excel via VB.NET, see the Automating Office Programs with VB.NET tutorial.

For copying the values of a range into an array and then iterating the values within the array, instead of looking through the cells of the range, see Efficiently Iterating the Values of a Range.

Hope this gets you started, let us know if you get stuck...

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 04-29-2010, 08:08 PM
Jahnavi Jahnavi is offline
Newcomer
 
Join Date: Apr 2010
Posts: 3
Default Still Stuck

Hi Mike,

Thanks for the reply..
looks like I will stuck in this program for ever

Ok all i am trying to do is to open an excel workbook and copy some data into a range and then clsoe the work book.

And then open an another work book and then paste the data at a particular range however it is not working ...

I have changed the code accordingly and I want that one to paste it at "F9" instead it pastes at some other place in the same page...

Please help me with an answer

Thanks in advacne

code is given below
Dim xlApp As Excel.Application
xlApp = New Excel.ApplicationClass
xlApp.ScreenUpdating = False
Dim xlOldWorkBook, xlNewWorkBook As Excel.Workbook
Dim xlOldWorkSheet, xlNewWorkSheet As Excel.Worksheet
Dim cRng, pastecell As Excel.Range
'Open the Mix file and get the raw Data and close it
xlOldWorkBook = xlApp.Workbooks.Open("C:\Mname.xls")
xlOldWorkSheet = xlOldWorkBook.Worksheets("sheet1")
cRng = xlOldWorkSheet.Range("A2:T25")
cRng.Copy()
xlOldWorkBook.Close(False)
releaseObject(xlOldWorkSheet)
releaseObject(xlOldWorkBook)
'Open the Conversion Excel File and paste data and do calcualtions
xlNewWorkBook = xlApp.Workbooks.Open("C:Conversion Program.xls")
xlNewWorkSheet = xlNewWorkBook.Worksheets("OldProgramData")
xlNewWorkSheet.Range("B3").Value = drow.MixFileName
pastecell = xlNewWorkSheet.Range("F9")
pastecell.Worksheet.Paste()
'xlNewWorkSheet.Range("F9").Select()
'xlNewWorkSheet.Paste()
xlNewWorkBook.Save()


'And the releaseobject sub is here
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Reply With Quote
  #4  
Old 05-01-2010, 09:24 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

I can't say for sure. It looks like you are correctly setting the PasteCell to the range "F9".

You might want to simplify your program further to only execute this copy-paste operation, getting rid of all other calls. (For example, like setting the value in cell "B3" and anything else other than this copy-paste operation.) Once you get this aspect working, you can add the other aspects back.

You also might want to have a read of the Automating Office Programs with VB.NET tutorial. It will help you with creating a new Excel.Application instance as well as how to properly release it. It's a lot to digest, so you might have to read through it more than once. (I've had to read through it many times -- and I wrote it!)

I hope this helps Jahnavi,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
 
Copying Data from Excel Range To Array or some other source
Copying Data from Excel Range To Array or some other source
 
-->