Data drops from closed file

ianto2842

New Member
Joined
Apr 7, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
Hi, so every day I have to run a task where I open a helper spreadsheet, drop a load of data exports in from emails (reports linked in the email, not attached so I'm assuming there's no way to automate this element of it) and then go into a tracker spreadsheet (there is one per week, with tabs per day - I need the data for the previous day, whatever file that may be) and then copy the data from this spreadsheet into the helper to consolidate and give a report. I'm wondering if there is a way to run some VBA to pull this data in without having to open the specific spreadsheet? I initially tried this using Indirect( ) and a concatenate to build up dynamic cell references, but they wouldn't work on closed workbooks.

Ultimately it needs to use a date field to generate a file name in the format "Staffing wc dd-mm-yyyy" and copy data from the day of the week given from the date, and copy data from that tab using range DO8:DW157. EG if the date in A1 = 11/08/2021 it should copy data from the spreadsheet "Staffing wc 09-08-2021" and copy in range DOO8:DW157 from the tab "Wed" without opening it, pasting as values into A2. From my googling I believe it's possible but I'm struggling to do it.

I hope this makes sense, any suggestions gratefully received :) Thanks!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
I'm wondering if there is a way to run some VBA to pull this data in without having to open the specific spreadsheet?
I would suggest looking into using Power Query to import your data. For example, I have one file with the data that constantly gets updated. In my excel sheet, I have set up a query with Power Query to (at the press of a button) automatically, read that file, tidy up and organize the data to get it into the format I need, and then output the tidied data to a table in my excel sheet that I can do whatever I want with - view it or add it to a master table. Tell me if I'm wrong, but in your case, it sounds like each time you'll be loading data from a source file with a) a different file name, and b) different sheet names within the file. This makes things a little more complex, but it's definitely doable.

It sounds like your process might look something like this:
  1. Generate the file name for the data you want to open. This is the data source.
  2. Open that file
  3. Navigate to the sheet named with the correct date
  4. Grab the data in the sheet
  5. Place the results in your "main" worksheet. I'll call this the target.
Sound about right?
 

Forum statistics

Threads
1,143,637
Messages
5,719,962
Members
422,251
Latest member
bonebreaker100

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top