VBA to automatically pull data from other excel documents

OfficeAssociate99 · #1 05-26-2017, 11:06 AM

Hi,
I'm trying to automate the process of pulling data from a certain range (not all data) from around 6-7 other excel files into one master document. It would be great if I could filter the documents based on their title (i.e. they are all dated, so if I set it so that the program would search for the current date in all the titles, and pull very specific data sets into the master copy). All the documents are formatted using the same template.

Any help would be appreciated, as I am a n00b when it comes to programming.
Thanks.

BobBridges · #2 05-29-2017, 09:15 AM

I do this fairly often, and have developed some regular functions and classes to help. The general idea is that I use the FileSystemObject to run through all the files in a folder, and a Regular-Expression object to determine which filenames match the desired pattern. Does that tell you what you need to now, or do you need more information on one or both?

OfficeAssociate99 · #3 05-29-2017, 10:43 AM

Quote:

Originally Posted by BobBridges

I do this fairly often, and have developed some regular functions and classes to help. The general idea is that I use the FileSystemObject to run through all the files in a folder, and a Regular-Expression object to determine which filenames match the desired pattern. Does that tell you what you need to now, or do you need more information on one or both?

I have a little bit better of an idea. I'm more or less doing this from scratch, as I have limited working knowledge of VBA and coding related to that. Any additional details on either of items you mentioned would be great.

BobBridges · #4 05-29-2017, 09:46 PM

Most important, here's the link for the FileSystemObject and here's the one for the regular-expression object. However I can go a little further than that.

Here's some sample code where I use the FSO to look through the files in a folder and pick out the ones I want:

Code:

' Create the FileSystemObject and get the desired folder.
set ofs=CreateObject("Scripting.FileSystemObject")
if not ofs.folderexists(fnpIP) then abend "I can't find a folder named " & fnpIP & "!"
set ofo=ofs.getfolder(fnpIP)

' Look at each file in the folder and decide whether I want it.
for each ofi in ofo.files
  select case ofi.name
    case "Detroit.xlsm": bmch=True
    case "Chicago.xlsx": bmch=True
    case "Marywether.txt": bmch=True
    case else: bmch=False
    end select
  If bmch then
    ...process your file
    end if
  next ofi

This assumes that you want specific filenames. If you want a certain pattern of filenames, you can set up a RegExp "pattern" describing a filename that, for example, starts with one of three specified city names, then contains a date in ANSI format, and ends with "gener.xlsm". The run each filename past that RegExp pattern and use the ones that match and whose date is within the past 90 days. But regular expressions, while they're extremely flexible and powerful, are correspondingly complicated; we should talk about them in a separate thread.

Oh, by the way, the above code is not tested; I threw together the basic statements, but I don't promise there are no syntax errors. Should get you started though.

OfficeAssociate99 · #5 05-30-2017, 12:22 PM

Quote:

Originally Posted by BobBridges

Most important, here's the link for the FileSystemObject and here's the one for the regular-expression object. However I can go a little further than that.

Here's some sample code where I use the FSO to look through the files in a folder and pick out the ones I want:

Code:

' Create the FileSystemObject and get the desired folder.
set ofs=CreateObject("Scripting.FileSystemObject")
if not ofs.folderexists(fnpIP) then abend "I can't find a folder named " & fnpIP & "!"
set ofo=ofs.getfolder(fnpIP)
 
' Look at each file in the folder and decide whether I want it.
for each ofi in ofo.files
  select case ofi.name
    case "Detroit.xlsm": bmch=True
    case "Chicago.xlsx": bmch=True
    case "Marywether.txt": bmch=True
    case else: bmch=False
    end select
  If bmch then
    ...process your file
    end if
  next ofi

This assumes that you want specific filenames. If you want a certain pattern of filenames, you can set up a RegExp "pattern" describing a filename that, for example, starts with one of three specified city names, then contains a date in ANSI format, and ends with "gener.xlsm". The run each filename past that RegExp pattern and use the ones that match and whose date is within the past 90 days. But regular expressions, while they're extremely flexible and powerful, are correspondingly complicated; we should talk about them in a separate thread.

Oh, by the way, the above code is not tested; I threw together the basic statements, but I don't promise there are no syntax errors. Should get you started though.

Thanks, you've been very helpful. I'll try working through it.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using Excel data to automatically make Word documents	Vincent	Mail Merge	9	01-04-2021 03:05 PM
Pull data from Excel to Powerpoint (not dynamically)	Terri11130	PowerPoint	1	08-25-2015 12:17 PM
How to get project to automatically pull in task dates?	Moe Szylak	Project	2	04-26-2012 12:37 PM
Excel VBA: Pull data from web	tinfanide	Excel Programming	0	12-09-2011 02:11 AM
Old Excel Document folder automatically created in My Documents	slickcondo	Office	6	07-13-2011 02:31 AM