#1
|
|||
|
|||
VBA to automatically pull data from other excel documents
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. |
#2
|
||||
|
||||
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?
|
#3
|
|||
|
|||
Quote:
|
#4
|
||||
|
||||
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 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. Last edited by BobBridges; 05-29-2017 at 09:47 PM. Reason: PS |
#5
|
|||
|
|||
Quote:
|
|
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 |