Obtaining named cell from X # of workbooks in the same file path


New Member
Let's see if I can articulate this and if someone can point me to the write thread. I wanted to see if I can do this on my own this weekend otherwise, I'll get our IT department involved (end of August).

The purpose of this question is to see if I can pull the same named cell value from X number of files in the same file path to create a check list of what's done or what's missing.

1. I have a folder with a variable number of CLOSED excel workbooks: 'C:\Users\mfinm3g\Desktop\Recon'. One month there could be 20 files, and another there could be 25 - due to frequency required.

2. They will all follow a similar naming convention
1950-8digits-variable name

3. Each file contains the same "named" cell - "Total" on the same sheet.

4. I have a checklist file, Contains all possible files. This checklist can have a variable number of rows. One year it may have 3. The following year 4. In it, it looks like this

Column E pulls the "named" cell - "total" from each closed workbook by creating a file path using A+B+C+cellB1+cellB2

it was able to pull 2 of the files since they exist, but the last was an error. This allows me to follow up.

how can I easily do this?! I'm struggling. I have pieces of the various codes. I just can't piece it together. And some codes open workbooks.

My thoughts as to how to break this problem down
1. use a loop
2. the loop will go through the checklist
3. it will pull the total without opening the workbook
Last edited:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...