Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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?



Reply With Quote
  #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
Default

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.
Code:
    Dim fNam As String

    fNam = Dir("*.xls")

    Do Until fNam = ""
        ' Do your processing here

        fNam = Dir()
    Loop
    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>").
Reply With Quote
  #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
Default

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!

Iain

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:33 PM.


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