Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

processing excel spreadsheet with multiple worksheets

Posted on 2005-05-06
4
Medium Priority
?
788 Views
Last Modified: 2013-12-26
I have an excel spreadsheet that has two worksheets on it.  Ultimately, my goal is to selectively pull values from both worksheets and combine them into the contents of a datawindow so I can save it to a database table and do some further processing.  The data from worksheet one is combined with a given row on worksheet two by matching up two columns from worksheet 1 to worksheet 2.  

I was planning on using excel oleobject to do a saveas to text so that I can utilize a datawindow but am unsure as to how to deal with the multiple worksheets.  

Any thoughts on this? Maybe a saveas isn't the best approach.  Possibly loop through the contents of each worksheet somehow?  

Thanks in advance for your assistance.
0
Comment
Question by:babybird
  • 2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
diasroshan earned 2000 total points
ID: 13949640
hi,
try the following and see if it helps... the idea is to take all the data into 2 separate datawindows from 2 worksheets and do the necessary validations and manipulations that u need to apply...

Here's an example using clipboard.. Ofcourse you need to have Data window columns in the same order..

OLEObject  excel

Integer    li_RetValue, li_rtn
Boolean    lb_sheet_rtn
Long       ll_cnt
String     ls_ClipBoard

excel = create OLEObject

li_rtn = excel.ConnectToNewObject("excel.application")
IF li_rtn <> 0 THEN
    MessageBox('Excel error','can not run Excel Program')
    DESTROY excel
    RETURN 0
END IF

excel.WorkBooks.Open( "c:\mysheet.xls" )
excel.Application.Visible = false
excel.windowstate = 2 // 1 : Normal, 2 : Minimize, 3 : Maximize

lb_sheet_rtn = excel.worksheets(1).Activate

ls_ClipBoard =  Clipboard() //Store current clipboard Data

excel.Worksheets(1).Range("A1:E5000").Copy  //  copy to clipboard - Take enough Range. .. Or use the UsedRage property.  If data has header row, use correct statring range.. (B1:... )
ll_cnt = dw_1.ImportClipboard()

Clipboard(ls_ClipBoard) //Restore clipboard

IF ll_cnt <= 1 THEN
    Messagebox("Inf", "Could not find .")
END IF

lb_sheet_rtn = excel.worksheets(2).Activate

ls_ClipBoard =  Clipboard() //Store current clipboard Data

excel.Worksheets(2).Range("A1:E5000").Copy  
ll_cnt = dw_2.ImportClipboard()

Clipboard(ls_ClipBoard) //Restore clipboard

IF ll_cnt <= 1 THEN
    Messagebox("Inf", "Could not find .")
END IF

excel.Application.Quit
excel.DisConnectObject()
DESTROY excel


Cheers,
Rosh
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13965577
hi,

did it help...??? or,

do u need more assistance...

Cheers,
Rosh
0
 

Author Comment

by:babybird
ID: 13977592
I'm not sure yet.  I have been dealing with some production issues so I haven't been able to address this yet.  I hope to do so by the end of the week.  Thanks for asking.
0
 

Author Comment

by:babybird
ID: 14096379
Just wanted to let you know that the above process did eventually work and is an option however I was having performance issues.  I don't believe the performance issues were related to the above process as much as they were to the process of bringing processing the data collected.  As a result, I ended up importing the data into datawindows and saving to tables so I could do the processing in a stored proc instead.  This improved the performance considerably.  But I wanted to thank you for your input and I appreciate the time you took to respond.

Thanks
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
Suggested Courses
Course of the Month21 days, 6 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question