DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Excel 2000, Copy function

  1. #1
    Join Date
    Oct 2008
    Posts
    3

    Thumbs up Excel 2000, Copy function

    Is there any way to copy only the populated cells in a worksheet via VB code? I am creating a dasa base that pulls data from worksheets that will be constantly updated with new info. I would like to be able to extract the data from the constantly updated worksheet by using VB code to copy only the populated cells.

    Thanks

    Steven

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Welcome To DevX

    The simple answer is Yes.

    However, before the "how" answer can be given, we would need to know a few things.

    Will the populated cells ALWAYS be the same? In other words, something like A1:F26?
    I assume the excel spreadsheet name will never change?
    Will the contents of each cell go into its own db table field or will the data in different cells be combined to go into one field?
    How often does this get populated?
    What happens at the end of the day when you go home and your program stops running?

    (BTW: What database are you using?)

  3. #3
    Join Date
    Oct 2008
    Posts
    3

    Vb

    I am creating database using excel 2000. I work in the oil business, the continualy updated worksheets, we'll call it "Rig Info" from now on, each contain data specific to one rig. The concept is to have one main worksheet where you can select the rigs you want to view, click a button, and the output gives you the data from each of these rigs in one worksheet ("Report Sheet"). The Rig Info sheets can be updated at any time. The Rig Info formats will stay the same, but when it is updated with new info, the info will populate the next available row. The Rig Info data extracted today might have cells A1:F10 populated, but tomorrow the Rig Info sheet might have 2 new data entries leaving A1:F12 populated. I am looking for a code that will select and copy only the populated rows...I hope this explanation helps

    Thanks,
    Steven

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Based on what you posted, this application begs, screams out and pleads for the use of a real database as opposed to Excel.

    Have you worked with anything like Access before?

  5. #5
    Join Date
    Oct 2008
    Posts
    3
    No, I have never used access...just visual basics.

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Ok, then we will stay with Excel (but I would suggest looking at a database tutorial)

    Back to your example...
    Quote Originally Posted by Steveng1017
    The concept is to have one main worksheet where you can select the rigs you want to view
    So far, so good
    Quote Originally Posted by Steveng1017
    click a button, and the output gives you the data from each of these rigs in one worksheet ("Report Sheet").
    The first worksheet has a list of Rigs, and you choose one. Got it.

    Once that you have chosen one, where is the data coming from that will produce the output?
    Quote Originally Posted by Steveng1017
    The Rig Info sheets can be updated at any time.
    This is a bit confusing. Previously you said I would pick the rig that I wanted to view, then click a button to see the output. If this is the case, how are the sheets updated at any time?

    The big question here is: what is producing the data that I will be copying to a worksheet? Where is it coming from?

    This might be a moot question if, regardless of where it is coming from, it all mysteriously winds up on a single spreadsheet, and all I have to do is go to that spreadsheet and copy what I want to another spreadsheet.

  7. #7
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Quote Originally Posted by Steveng1017
    Is there any way to copy only the populated cells in a worksheet via VB code?
    Yes there is an object for each worksheet called UsedRange. With it you can determin the number of used rows and columns.
    ActiveWorksheet.UsedRange.Rows.Count
    ActiveWorksheet.UsedRange.Columns.Count

Similar Threads

  1. please help me
    By idea2 in forum .NET
    Replies: 7
    Last Post: 02-22-2007, 08:40 PM
  2. Replies: 0
    Last Post: 08-15-2005, 04:08 PM
  3. Access 97 to 2000 Conversion Error
    By Michelle in forum VB Classic
    Replies: 8
    Last Post: 04-19-2002, 05:01 PM
  4. Getting a GUI to function
    By Eric in forum Java
    Replies: 1
    Last Post: 11-27-2001, 07:53 AM
  5. Trying to print a PDF File from VB
    By Kunal Sharma in forum VB Classic
    Replies: 2
    Last Post: 04-25-2000, 03:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center