The workbook referenced will be closed. The name of the workbook would be the current date.
Example: H:\Drive_Path\Folder\[12.7.23.xlsx]Sheet1!$F$6
The next day, the referenced workbook would be 12.8.23.xlsm
A5 would contain a formula converting TODAY() to a string displaying 12.7.23.xlsx
I am struggling with making the example formula use A5 as the name of the closed workbook to reference from. Any help would be highly appreciated.
Deleted by author. Wrong answer
Last edited by Pepe Le Mokko; 12-08-2023 at 04:39 AM.
There is no practical way to do this with an Excel formula. INDIRECT only works when the source file is open. There are formula workarounds, but these are TOTALLY impractical if you're looking at more than a few files.
Your only option is VBA. If that is OK, I'll move your thread to the VBA sub-forum.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
That would be great, thank you!
If you are able to store the closed workbook on OneDrive, you could use its http address to get the required value.
Failing that, the following code will open a workbook and you can use the cell's value as required. There are options to hide the opened workbook but I didn't include these in the code. You'd also need to close the workbook when you're finished working on your current one (this could probably be coded into the Workbook_Close event procedure).
Please Login or Register to view this content.
Excel 365 user. To unblock a downloaded macro-enabled workbook, go to your "Downloads" folder > right click on the workbook name > click 'Properties' > check the 'Unblock' checkbox. You can now open the workbook.
My impression has always been that VBA is no better (if not worse) at accessing information in a closed workbook than native Excel formulas. Most VBA based strategies that I see for accessing information in a "closed" workbook are like deadlyduck's -- to use VBA to open the workbook so that the information can be accessed. Is that an acceptable strategy, or are you required to perform this task without opening the source file? I can think of several strategies for solving the problem if you're allowed to open the file -- access information -- close the file. If you are not allowed to the open the file, then solutions become more difficult (and most of the solutions I think of at that point are mostly about how to merge each day's file into a single, larger "database" file, because I find it is usually easier to access information from a single well designed database spreadsheet than to have the information spread out over multiple "daily" spreadsheet files.)
Originally Posted by shg
I'm attempting to display a data range, same range every day, on a dashboard type workbook. The range to display is from closed workbooks. Unopened behind the scenes. There is always a workbook of the current day's date "12.10.23.xlsx". I would like to always be able view data from that closed workbook, ranges "F5:H11" and "K5:M24". Everything is constant, only the name of the closed workbook changes to match current date.
I have no further suggestions for a problem like this, if the file must remained closed throughout the process of retrieving data from the file, and you are not allowed to hardcode the filename in an Excel formula.
From my experience VBA (or, better yet, Power Query) can access data in closed text files and bring it into Excel. I don't know Power Query, but Power Query might be able to extract information from closed Excel files, but someone more knowledgeable in Power Query would need to verify if that is possible and how that might happen. I will see if I can interest one of our Power Query experts in commenting on the possibility.
Maybe a routine to build the formulae would work.
Please Login or Register to view this content.
this can be done with Power Query if you are willing to go down that route. Please advise if you are willing to go that way and I will write a short tutorial. It would also be helpful if you uploaded an anonymized copy of your spreadsheet (8-12 records only).
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Will take some time to work up an example file. What I have done with varying levels of success:
I can get excel to open a file with today's date as "12.11.23.xlsx" and copy a specific range, then pasting it into a specific location on the main workbook.
I've had limited success with pasting that info to the main workbook the macro is executed from. Closing the 12.11.23.xlsx is a work in progress. I'm trying to power through learning to assign each of the two workbooks as a variable so the correct one is closed (without saving or prompting to save).
Maybe use ADO. See: https://stackoverflow.com/questions/...sed-excel-file
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
If you already did this yesterday, won't you have the data from yesterday already pasted in to your dashboard sheet? And, for the day before that, and the day before that, and so on.
So, if you have kept doing this for a number of days, then you only have today's data to be concerned with.
Hope this helps.
Pete
The data will be overwritten so only the current day's info will be shown. I felt close when I had a script open the correct workbook in the background, copying the info and pasting it to the correct place on the dashboard, however the background workbook remained open. the dashboard was being closed, then prompts to save the dated workbook in background and finally left with a blank excel screen; excel with no workbook loaded.
Each dated workbook has a list of names, comments and times relevant to that day only. I'm just trying to conveniently list this data on a dashboard type workbook.
Basic Open, Copy Date, Close ...
Please Login or Register to view this content.
What I have tried:
BD26 = Workbook namePlease Login or Register to view this content.
Mayb this then (untested).
Please Login or Register to view this content.
TMS, that worked really good!! Thank you
You're welcome. Thanks for the rep.
And, if you use Application.ScreenUpdating = False/True, you won't see the file being accessed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks