Open workbooks - Filepath, filename

[mdocton]

Hi,

I am a VBA novice but enjoy building useful macros, so please forgive my inexperience. I have researched the various threads but am struggling, so would appreciate a pointer in the right direction.

I have folders with number of workbooks in each folder. I also have a value ("x", a number).

I would like to run a macro that searches a particular column of each workbook in aspecified folder for "x". If it finds it, I need to copy that row, record it, then move on, until all workbooks have been checked.

So, I am working on the following lines;
1. Create new workbook (for recording results found)
2. Select folder
3. Open the first workbook found in the folder (*.xls, *.xlsx)

4. Select first sheet

5. Check column E for "x"
6. If found, copy row; Go to new workbook and paste it. Resume.
7. Go to next sheet. Step 5 again.

8. Workbook 1 finished; Select 2nd workbook. Step 4

9. All workbooks done - end.

I've tried to fathom searching closed workbooks, but it seems very difficult, so I'm happy to open each workbook in turn.
I'm ok with most of it but my difficuly is specifying the workbook to open. I can generate the filepaths/filenames (which are recorded in A1 & B1 in the new workbook), but I would appreciate some help with the next step;

The Filepaths/filenames are recorded in new workbook ok;
How do I take the Filepath/filenames from that worksheet and use use that information to open that workbook? I would then be able to carry out the search & record procedure before closing it and resuming.

Many thanks indeed

Mark

 

[John_w]

I'm ok with most of it but my difficuly is specifying the workbook to open. I can generate the filepaths/filenames (which are recorded in A1 & B1 in the new workbook), but I would appreciate some help with the next step;

The Filepaths/filenames are recorded in new workbook ok;
How do I take the Filepath/filenames from that worksheet and use use that information to open that workbook? I would then be able to carry out the search & record procedure before closing it and resuming.

For anything like this I always use the Macro Recorder to get me started with the VBA code. Doing a manual File - Open to a specific workbook gives:

Workbooks.Open Filename:="C:\Temp\Excel\Book2.xls"

Amending this to use the folder path and filename from cells A1 and B1 gives, at its simplest:

Workbooks.Open Filename:=Range("A1").Value & Range("B1").Value

This assumes that the folder path ends with a backslash or slash - if not include & "\" or & "/" in the statement. Also, neither Range specifies a Sheet reference, so this implicitly uses the active sheet if the code is placed in a module, or the sheet associated with a sheet module if placed in that sheet's module. Note that the opened workbook becomes the active workbook.

 

[mdocton]

Thank you, John,

That's simple and perfect - I couldn't quite get there, but am ok now!