VB.Net - Able to select an Excel spreadsheet

Question

0.00/5 (No votes)

See more:

Hi,

I am having a problem with the following.

I am trying to

1. create 2 excel worksheets....able to do this
2. Add a heading (which are the same) on both worksheets e.g. Name, Address
3. Select a worksheet and populate with an array
4. When the records populated are greater than 20,000 then select worksheet 2 to populate the rest

I can do this no bother when just the one worksheet but seem to have got myself all confused when trying the above.

Posted 8-Apr-13 1:25am

Central_IT

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kenneth Haugland · Accepted Answer · 2013-04-08T01:37:00

Solution 1

Im assuming that you have some sort of code as this:

VB

Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range

        ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet

You can set the first sheet with the command:

VB

oSheet = oWB.Sheet(1)

and the second by:

VB

oSheet = oWB.Sheet(2)

Posted 8-Apr-13 1:37am

Kenneth Haugland

Comments

Central_IT 8-Apr-13 7:50am

Thanks for getting back with a quick reply.
Your solution works perfect. Just a wee question,
How can you in a loop display the headings and then the data?

Kenneth Haugland 8-Apr-13 7:53am

Just loop through the cells and read the header information?
Please see this:
http://support.microsoft.com/kb/301982

Central_IT 8-Apr-13 8:42am

Thanks yet again for the required information. There is enough info there to solve my problem.

Central_IT 8-Apr-13 8:43am

Should have been 5 stars but not allowing me over 4