Just spitballing, but it sounds like you're dealing with a classic automation challenge in Excelnavigating a messy server structure to fetch targeted data without loading everything at once. Power Query is great for bulk imports, but as you've seen, it can choke on large-scale folder scans (especially nested ones) because it tries to index or combine everything upfront. For on-demand pulls based on a single project name, VBA is often a better fit: it's more flexible for searching directories recursively, opening specific files, filtering sheets, and extracting just what you need without performance hits from pre-loading thousands of files.
I'll outline an "easy" VBA-based approach below (no advanced coding requiredjust copy-paste and tweak). This assumes:
Your server path is something like \\server123\mainfolder\ (replace with your actual UNC path).
Projects are in subfolders named after the project (e.g., a folder called "ABC" or containing "ABC" in the name).
Each project folder contains one main Excel workbook (e.g., "ProjectData.xlsx"you can adjust if it's named differently or if there are multiples).
You want to pull from a "specific worksheet" (I'll assume it's named something consistent like "DataSheet"; if not, we can filter to the first non-"void" sheet or a specific one).
Data to pull is from known ranges (e.g., cells A1:B10customize as needed).
Ignore any sheets with "void" (case-insensitive) in the name.
If these assumptions don't match (e.g., multiple files per project or variable sheet names), reply with details for refinements.
Step-by-Step Setup
1 Enable VBA in Your Workbook:
Open your main Excel workbook (where you'll enter the project name).
Press Alt + F11 to open the VBA Editor.
Insert a new module: Insert > Module.
2 Paste This VBA Code: This macro:
Takes the project name from a cell (e.g., A1 on your active sheet).
Recursively searches the server path and subfolders for a matching project folder.
Finds the Excel file inside it.
Opens the workbook (invisibly).
Loops through sheets, skips any with "void" in the name.
Pulls data from the target sheet (customize the range and destination).
Closes everything cleanly.
Option Explicit
Sub PullProjectData()
Dim projectName As String
Dim basePath As String
Dim foundFile As String
Dim wbTarget As Workbook
Dim ws As Worksheet
Dim targetSheet As Worksheet
Dim dataRange As Range
' Input: Project name from cell A1 (change if needed)
projectName = ThisWorkbook.ActiveSheet.Range("A1").Value
If projectName = "" Then
MsgBox "Enter a project name in A1."
Exit Sub
End If
' Server base path (change to your actual path, e.g., "\\server123\mainfolder\")
basePath = "\\server123\mainfolder\" ' Ensure trailing backslash
' Find the project file recursively
foundFile = FindProjectFile(basePath, projectName)
If foundFile = "" Then
MsgBox "No matching project file found for: " & projectName
Exit Sub
End If
' Open the found workbook (read-only, invisible)
Application.ScreenUpdating = False
Set wbTarget = Workbooks.Open(foundFile, ReadOnly:=True)
' Find the specific sheet (assuming named "DataSheet"; adjust or use loop below)
' If sheet name varies, uncomment the loop to find first non-void sheet
Set targetSheet = Nothing
For Each ws In wbTarget.Worksheets
If InStr(1, LCase(ws.Name), "void") = 0 Then ' Ignore sheets with "void"
Set targetSheet = ws ' Use first matching; or check for specific name: If ws.Name = "DataSheet" Then
Exit For
End If
Next ws
If targetSheet Is Nothing Then
wbTarget.Close False
MsgBox "No valid sheet found (ignoring 'void' sheets)."
Exit Sub
End If
' Pull specific data (e.g., range A1:B10 from target sheet to B1 in your workbook)
Set dataRange = targetSheet.Range("A1:B10") ' Customize source range
dataRange.Copy
ThisWorkbook.ActiveSheet.Range("B1").PasteSpecial Paste:=xlPasteValues ' Customize destination
' Cleanup
wbTarget.Close False
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data pulled successfully from: " & foundFile
End Sub
Function FindProjectFile(folderPath As String, projectName As String) As String
Dim fso As Object
Dim folder As Object
Dim subFolder As Object
Dim file As Object
Dim filePath As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(folderPath)
' Check files in current folder
For Each file In folder.Files
If LCase(file.Name) Like "*projectdata.xlsx" Then ' Customize file name pattern
FindProjectFile = file.Path
Exit Function
End If
Next file
' Recurse into subfolders if name matches project
For Each subFolder In folder.SubFolders
If InStr(1, LCase(subFolder.Name), LCase(projectName)) > 0 Then
filePath = FindProjectFile(subFolder.Path, projectName)
If filePath <> "" Then
FindProjectFile = filePath
Exit Function
End If
End If
Next subFolder
FindProjectFile = ""
End Function
This is adapted from recursive folder search techniques 0 3 and data extraction methods 1 2 33 .
3 Customize It:
Project Matching: The code searches for subfolder names containing the project name (case-insensitive). If projects are files instead of folders, tweak the FindProjectFile function to search file names directly.
File Name: Adjust *projectdata.xlsx to match your files (e.g., *.xlsx for any Excel file, or *ABC*.xlsx).
Sheet Selection: If the sheet isn't fixed, the loop finds the first non-"void" sheet. For a specific one, add If ws.Name = "YourSheetName" Then.
Data Pull: Change A1:B10 to your actual range. If pulling multiple areas or formulas, use .Copy with xlPasteFormulas.
Server Path: Use UNC format (\\server\share\) for network access. Test permissionsVBA needs read access.
Multiple Sheets/Files: If projects have multiple valid sheets, modify to loop and append data.
4 Run It:
Enter project name (e.g., "ABC") in A1.
Press Alt + F8, select PullProjectData, and run.
For auto-run on cell change: Right-click the sheet tab > View Code > Paste:Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
PullProjectData
End If
End Sub
Why Not Power Query?
You could parameterize Power Query for a specific project 10 12 19 24 , but it still scans the entire folder structure initially, which hurts with 1000s of projects/40 folders. To filter sheets, you'd expand content and filter on sheet names post-import 25 35 , but VBA avoids that overhead by searching only when needed.
Tips for Scale/Performance
With thousands of projects, the first search might take seconds-minutes depending on network speedcache results if repeated.
If folders are too deep, add a depth limit to the recursion.
Test on a small local folder first to avoid server locks.
For security: Enable "Trust access to the VBA project object model" in Excel Options > Trust Center if needed.
If this doesn't fit (e.g., you prefer Power Query or need to handle "void" differently), share more details like folder structure or exact sheet/data needs!
