VBA to automatically pull data from other excel documents

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.

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