Importing data from closed workbook

[jleone]

Hi,
I work with in a warehouse, with inventory management, forecasting, etc. and right now I receive everyday a huge spreadsheet filled with a lot of information which is hard to read and track down.
I'm trying to create a master spreadsheet, that will be able to import only the selected data I want from the other workbork (which will be closed). The problem is, I need it to import the new data almost everyday, so it will be importing from a different file each day. Is there a way to create a macro/vba that will do this? I'm very new to macros and understand this is advanced so I'm not sure where to even begin.
Any help would be appreciated, thanks!

 

[GlennUK]

How are you importing the data?

 

[Ruddles]

Also:-

What is it about this file that tells you it's the new file? Is there something in the name? Or in the creation date?

Are you allowed to delete the file when you've imported it? Or rename it, perhaps?

 

[jleone]

that's the problem..i don't know how to import the data, i'd like to create a macro that will import the data. We've decided that each day the name of the file that has information that needs to be imported can be the same thing, such as controltool.xlm and then at the end of the day, once the macro is run, we can rename the file according to the date, so that the next day when we go to run the macro again, it'll use the new controltool.xlsm for that day, this way we don't ever have to change the macro around and instead can just change the name of the file after the macros finish running. Also, we can open up the other excel file to import it. I guess my new question is how do I write the coding or vba to import certain data from one excel file into another. For example, if i wanted to import row 3, columns 3-25 of controltool.xlm into row 2, columns 2-40, what would the general code look like?
thanks!

 

[GlennUK]

{snip}For example, if i wanted to import row 3, columns 3-25 of controltool.xlm into row 2, columns 2-40, ... {snip}

That's not a good example ... unless you really want to import a single row always ... is that the case? And can you explain how columns 3-25 map to columns 2-40 in the target area ( they are not the same size )?

 

[jleone]

I was trying to just make up an example off the top of my head. I don't know the exact sizes of what I want to import. Is there just a template of coding that I can use, and then just insert the specific rows or columns of cells that I'd like to input into it.

 

[GlennUK]

You can either do direct linking formulas to fetch data. But this will be slow if you link to many thousands of cells ( but I don't know even roughly how much data you are wanting to import ). Or you can use MS Query to fetch data from a closed book, as long as the data in the source book is organised like a table that MS Query can recognize ... with this approach you can specify criteria to limit which records to be imported, and specify which fields ( i.e. columns ) are to be included in the retrieved data. Do you think that either of these could be used in your case?

There may be other people on here who have more experience in using MS Query than me, although I'll gladly tell you what I know if you want to go down that route.