2

I have a bunch of (~100) Excel files, plus one "master" Excel file that is supposed to gather data from all of the other Excel files.

Based upon various websites, I have already found out how to reference cells and cell ranges from other workbooks in a formula. In general, the format must be something like:

path[workbook.xlsx]'worksheet'!cell

Now, it is already quite cumbersome to always put the absolute path there (all of my Excel files reside in the same directory, and I would prefer using relative paths in case I ever decide to move or copy them).

However, what really annoys me is that I cannot enter any formula with such a cross-workbook references without having to go through two (!) UI dialogs:

  • First, a warning message about enabling links is displayed. Granted, this can apparently be disabled somehow, even though just globally, not for my specific file.
  • Then, an Open File dialog is displayed where I have to select the referenced workbook again.

This makes copying from row to row extremely cumbersome.

Is there a way to prevent especially the prompt to pick the file again?

O. R. Mapper
  • 820
  • 4
  • 14
  • 29
  • 2
    Look into Power Query as a replacement for referencing cells. – bugdrown Jun 30 '23 at 00:09
  • @bugdrown: I briefly looked into it, but I quickly got the impression that it's not so much a reference to another file and more a way to (one-time) copy data from there. Also, the UI insisted on my picking existing cells from the other worksheet rather than specifying a cell range that would be completed at a later time. In the end, I wrote a small C# tool using the OpenXml SDK that reads out the relevant cells from the other Excel files and copies them to my master Excel file, as this seemed simpler than trying to get Excel to repeatedly take over the data. – O. R. Mapper Jun 30 '23 at 23:02
  • I should have asked how the data are formatted in the files. If tabular and all files are formatted the same way then Power Query is useful as data from new files can be pulled in with just a refresh. Even if non-tabular, files with a consistent structure can still be transformed and combined. Happy to hear you solved your own dilemma. – bugdrown Jul 01 '23 at 00:23

2 Answers2

1

The best solution would be to use Python to process the Excel files. It's way easier and you would have a lot of examples.

I personally used both Excel and Python, but found Python+Pandas the best way to go. See here for a quick start: https://www.dataquest.io/blog/excel-and-pandas/

aqf
  • 67
  • 3
0

You can silently open (and then close) each of your subordinate workbook files using GetObject called from code contained in your "master" workbook file.

Dim myWorkbook As Workbook

Set myWorkbook = GetObject("C:\user\....\myFile.xlsx")

'...Processing to get required values from myFile.xlsx ...

myWorkbook.Close

To avoid getting into a mess with multiple instances of myFile.xlsx open, I advise checking the Application.Workbooks collection to ensure that none of the currently open workbooks have the same filename ("myFile.xlsx" above) as the workbook file which is subject to the call to GetObject. Perform this check prior to the call to GetObject.

Wrap the code from GetObject to the Workbook.Close call in a loop to read a whole series of subordinate files.

The argument of GetObject can of course be in the form of concatenated string variables such as strPath & strFilename which should give you the flexibility you are after.

DMM
  • 916
  • 2
  • 7