Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Select data from table and write to separate workbooks

Discussion in 'Information Technology' started by pete2586, Jul 28, 2006.

  1. pete2586

    pete2586
    Expand Collapse
    Guest

    Hi,

    I have a table containing the information I want to export into Excel. I
    would like to separate the data depending on the value in one of the columns
    and write each chunk of separated data to a different workbook. To make it
    slightly more complicated, within each workbook I need to write the data to
    different worksheets depending upon the value in a second column. I have very
    little idea how to start going about this so any help would be most welcome.
     
  2. Loading...


  3. Larry Daugherty

    Larry Daugherty
    Expand Collapse
    Guest

    The more experience you already have with VBA the better. The main
    concept you'll have to learn is Automation, in which the various
    Office applications can be made to play together. You'll be doing VBA
    programming in both Access and Excel.

    I recommend that you first lay out your design using pencil and paper.
    Think it through in terms of the data you have at the outset and what
    you want as a final result, working out the intermediate transforms on
    paper.

    I recommend keeping all of the final code in an Access module when
    you're done, rather than trying to keep some code in each application.

    Once you have the design details pretty well worked out it's time to
    work out which parts will be directed at Access objects and which at
    Excel objects. While all of the code will end up in an Access module,
    I recommend that you initially do the Excel code in an Excel workbook
    and only bring it over to Access when it works flawlessly in the Excel
    environment. When you first bring your Excel code over to Access I
    recommend using early binding so that you'll have the benefit of
    intellisense for any additional Excel code you may have to write. You
    can change back to late binding as one of the final steps if you'd
    like. I don't know your level of expertise in Excel but I almost
    always record a macro to get a code template and then research and
    generalize the code.

    Automation is a great concept and it works as advertized. It can get
    confusing as you get into the thick of it but press on and you'll
    succeed.

    HTH
    --
    -Larry-
    --

    "pete2586" <pete2586@discussions.microsoft.com> wrote in message
    news:8A4E6CCC-7FD4-4449-8318-3915CF3D28F3@microsoft.com...
    > Hi,
    >
    > I have a table containing the information I want to export into

    Excel. I
    > would like to separate the data depending on the value in one of the

    columns
    > and write each chunk of separated data to a different workbook. To

    make it
    > slightly more complicated, within each workbook I need to write the

    data to
    > different worksheets depending upon the value in a second column. I

    have very
    > little idea how to start going about this so any help would be most

    welcome.
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Larry Daugherty wrote:
    > > I have a table containing the information I want to export into
    > > Excel. I
    > > would like to separate the data depending on the value in one of the
    > > columns
    > > and write each chunk of separated data to a different workbook. To
    > > make it
    > > slightly more complicated, within each workbook I need to write the
    > > data to
    > > different worksheets depending upon the value in a second column.

    >
    > The more experience you already have with VBA the better. The main
    > concept you'll have to learn is Automation <<snip>>


    As this is Access, perhaps using SQL rather than VBA would be
    appropriate:

    -- Excel range address:
    SELECT col1
    INTO [Excel 8.0;DATABASE=C:\Workbook1.xls;].[Sheet1$A2:A50]
    FROM MyTable
    WHERE Col2 = 0;

    -- Excel worksheet's UsedRange:
    SELECT col1
    INTO [Excel 8.0;DATABASE=C:\Workbook2.xls;].[Sheet2$]
    FROM MyTable
    WHERE Col2 = 1;

    -- Excel workbook's Defined Name:
    SELECT col1
    INTO [Excel 8.0;DATABASE=C:\Workbook3.xls;].Range1
    FROM MyTable
    WHERE Col2 = 99;

    -- Excel worksheet's Defined Name:
    SELECT col1
    INTO [Excel 8.0;DATABASE=C:\Workbook3.xls;].[Sheet3$Range2]
    FROM MyTable
    WHERE Col2 = 55;

    Jamie.

    --
     

Share This Page