Re: exporting data to excel

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: MikeD (nobody_at_nowhere.edu)
Date: 05/13/04 

Date: Thu, 13 May 2004 18:38:04 -0400

"Mike" <anonymous@discussions.microsoft.com> wrote in message
news:B692036C-0C0F-42B1-A66F-D08786C5A796@microsoft.com...
> Hi,
> I have to export the data from my vb application to excel. I know how to
export data to an active*** after opening excel from vb. My question is
that if I have to export data to various sheets for eg first to sheet1, then
to sheet2 and so on in the same excel file. How to implement that.

You just need to reference the desired work***. There are several ways to
reference a given work***. One of these would be the Active*** property.
If you use that, you'd first have to activate the desired work*** (as Jeff
suggested). This is actually an additional step which is completely
unnecessary. Just use the Worksheets collection. With that collection, you
can reference any work*** by either its index (a number) or its name (a
string) if you know it. Here's some sample code that assigns a value to
cell A1 of the first 3 worksheets. It doesn't explicitly activate any of
them (the first is active by default). After you run the code, click each
work***'s tab to verify.

    Dim oXLApp As Excel.Application
    Dim oXLWB As Excel.Workbook

    Set oXLApp = New Excel.Application

    Set oXLWB = oXLApp.Workbooks.Add
    oXLWB.Worksheets(1).Cells(1, 1).Value = 1
    oXLWB.Worksheets(2).Cells(1, 1).Value = 2
    oXLWB.Worksheets(3).Cells(1, 1).Value = 3

    oXLApp.Visible = True

If you know the name of the work***, I'd recommend using that because the
name is going to be a lot more meaningful in code than a number, assuming
you're giving meaningful names to worksheets (it's just like using
meaningful names for variables). That code would be (using default names):

    oXLWB.Worksheets("Sheet1").Cells(1, 1).Value = 1
    oXLWB.Worksheets("Sheet2").Cells(1, 1).Value = 2
    oXLWB.Worksheets("Sheet3").Cells(1, 1).Value = 3

Note: I have Excel 2000. With that version, you must add a workbook after
creating a new instance of Excel.Application. Also, when adding a workbook,
3 worksheets are automatically created. This could be different with other
versions of Excel.

Mike

Quantcast