0

With my macro im using about 300 files. I want to do the summary of all these files in one file. I have the array formula to sum some staff. I just write a formula without opening each of the file. Anyway, let's assume that the formula is:

='sciezka & [nazwa pliku.xlsx]Interest table'!$E$1

The problem is that about 280 files have this sheet called "interest table" and everything is fine. However for the rest of the files I have to each time select the sheet because there is a dialog box / command bar "select sheet". enter image description here

Is there a way, with some vba formula, to force excel to choose the first sheet available or not to choose any and in the cell I will get the error?

enter image description here

I tried application.displayalerts = false but it is not working It would be great for me if the window wont show at all.

Thanks in advance for any comment

Emilia Rajchel
  • 85
  • 6
  • You can only use a worksheet in a formula if it is open. Therefore the files need to be open to use them. Even with VBA the files need to be opened. Excel cannot retrieve data from closed files. Excel can only read the values if it opens the files. If you write the formula with VBA it will still show the same popup dialog as Excel needs to know the name of the sheet. – Pᴇʜ Jul 14 '21 at 08:25
  • What you can do is write a VBA code that opens all the files one after another and collects the data for you. But you will have to write that code, as we cannot do that for you. Nevertheless if you get stuck or errors you can show your code and ask questions to it here on SO. – Pᴇʜ Jul 14 '21 at 08:31
  • Unfortunately I cannot open these workbooks, they are large, they are on external servers. It would took much more time. And Im sorry I have to disagree with you about getting data form closed workbook. It is possible. I enter other formulas where this sheet exists and I get proper data without opening any of the files. – Emilia Rajchel Jul 14 '21 at 08:58
  • That is not correct. Excel still opens that file in the background (which you might not see). Files need to be opened to read them. Even if you close your workbook with the formulas that link to other workbooks Excel opens all of them to check the data. If the file is not available it will work with the old data. But in case of the first time there is no old data so using that worksheet the first time it has to be opened. – Pᴇʜ Jul 14 '21 at 09:10
  • Hmm I did not know that. Do you know maybe where can I find a list of all "opened in the background" excel files? – Emilia Rajchel Jul 14 '21 at 10:54
  • 1
    Yes check [this](https://support.microsoft.com/en-us/office/break-a-link-to-an-external-reference-in-excel-f1ca8b08-4f24-4af6-92e5-f4fdb1442748). In the ribbon tab Data > Edit Links and you get all linked files listed. If you open your file those are the files that get opened too for refreshing the data (that could have changed while your file was closed). These files are not kept open but just read for refreshing and closed. – Pᴇʜ Jul 14 '21 at 11:15

0 Answers0