Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
| Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 19th, 2007, 10:30 AM
Registered User
Join Date: Apr 2007
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Extracting data from Spreadsheets in a folder

I am looking to write a VBA Macro to extract certain information held in particular cells from all spreadsheets in different folders.

Is there an easy way to move from one spreadsheet to the next in a folder and to write the code so that it runs through to the last spreadsheet, be there 20 or 200 in a folder?

  #2 (permalink)  
Old April 19th, 2007, 03:59 PM
Friend of Wrox
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

There are a few ways to do this.

Using VB’s Dir() function is popular. You specify a file spec on the first call, then provide no argument on subsequent calls. Each call will find the next file that fits the spec. When there are no more that match, calling Dir() returns "". If you call it without arguments after that, and error is raised.
    Dim fNam As String

    fNam = Dir("*.xls")

    Do Until fNam = ""
        ' Do your processing here

        fNam = Dir()
    In the "Do processing here" you would put a call to a routine that has the file name as an argument, and which opens the spreadsheet, and processes its values, then closes it.

Excel will let you open more than one spreadsheet at a time, so you can have this code in one spreadsheet, which then opens additional spreadsheets for processing. Those sheets would be accessed through the collection of sheets, Application.Sheets("<[green]Name you used to open it here>").
  #3 (permalink)  
Old April 20th, 2007, 02:57 AM
Registered User
Join Date: Apr 2007
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks Brian.

What is the code to use if I don't know the filename of each particular spreadsheet in the folder?

Can I do something like MySheets

For each MySheet or something as all the spreadsheet names are going to be individual, and I'm looking for a standardised Macro to run through and extract the data?

Thanks for your help!


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting data from excel spreadsheets Neil1234567 Access VBA 2 October 16th, 2007 07:28 AM
Extracting the full path of a folder vamshikrishnaD ASP.NET 1.0 and 1.1 Professional 3 August 2nd, 2007 09:16 PM
Extracting data from Word Document (RTF) PaulJH BOOK: Access 2003 VBA Programmer's Reference 0 September 14th, 2006 02:19 PM
Help extracting dyanamic data from html code method VB How-To 0 May 3rd, 2006 03:00 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.