get data from other excel files dynamically

[zundappeiro]

Hi,

First of all let me say that i'm new in this forum and quite newbie in using vba macros.

I would like to have some advice in how to do what i projected (or if it feasible at all). The problem is as follows:

The company where i'm working gets each week from its client forecasts for each product we produce and send them, for the following 52 weeks. It happens that these forecasts (basically tables with the quantities expected to be bought from us, by article and week) vary a lot for the same product and same week, depending on the week. This means that, for example, the forecast we got this week may have a forecast of the expected quantity for the product x in the week y completely different from the same product x and same week y of the forecast that we got last week, and so forth.

What i would like to do was an analysis of this forecasts based on the historic of the forecasts received, let's say the last 25. This way i would have a basis of comparison and be able to point out the products for which the forecasts would vary less and vice versa, this way improving the information from which the production department would plan the production.

Now the real problem for me is in creating a macro that allows me to do it dynamically. In theory i thought that the macro should have the capacity to go and collect the data (the forecasts) of the last 25 excel files received. But this should be dynamic as next week the last 25 files will then be different (the first of this week will stop taking part as this week's enters the historic).

So what i don't really know is if it is possible to do this, and if so, how it is the best way to do it.

I hope it's not a too long thread for you to analyse and comment (and understandable!) but i would really appreciate whatever idea/help i could get.

Thanks in advance!

[lucas]

the macro should have the capacity to go and collect the data (the forecasts) of the last 25 excel files received.

Welcome to the forum.

Before we can help you we will need more specifics.

Are all of the new files in one folder?

Where is the data to be collected from in the excel files and where will you want to put it in the workbook with the macro.

This is pretty big project but if the data is consistant, it can probably be done.

[zundappeiro]

- Whenever a new week starts, a new file is received from the client so the file from the previous week is placed in the "historic" folder, so all the files that matter for the analysis are in the same folder.

- "Where is the data to be collected from in the excel files " - i don't know if i got this one; the data is always in the same cells, from file to file, only changing the heading which obviously increases one week, each week. (here the printscreen from the forecast file -feupload.fe.up.pt/get/RWpj8WhLp4XInAq)

- "where will you want to put it in the workbook with the macro" - this is one of the subjects in which i was trying to get advise: is it necessary to collect the data from the several forecast files and place it somewhere in the workbook with the macro for a ulterior analysis or can this analysis be done by just 'reading' the data from the forecast files, without having to do the placing part?

Thanks a lot for the help

[lucas]

The analysis may be able to be done without copying the data but we will still have to open each workbook, one at a time.

What action would you want to take on an open workbook.


You can attach a file to your post here by hitting go advanced and then scroll down and look for the button that says manage attachments.

[zundappeiro]

well if the analysis without copying the data requires to open each workbook, one at a time, it is not very practical so maybe copy the data from the workbooks to the one with the macro is a better idea...

one of the ideas i firstly had was to do an analysis of the variance of the equivalent values of the forecasts (product, week) to get an idea of what products have more stable forecasts throughout the time.

in attachment goes a printscreen from the forecast file we get.

[lucas]

I'm sorry I haven't been able to return to this. I hope you haven't given up.

Attached is a zip with 3 files. It shows a way to open all the files in a folder and copy a range from each one.

Just unzip them all into the same directory and run one that is named runme.xls. You can change the path to a specific directory in the code if it looks like something you might be able to adapt to your need.

[zundappeiro]

thanks for the reply. i had already used some code very similar to what you kindly sent and i can now copy and paste an entire sheet (the one i want) from the workbooks within the folder i want and paste it to the workbook with the macro. now the deal is to restrict that process to the last 25 workbooks put on that folder. i have realised that there's this ".LastModified" option that i think would be just fine for what i want. in fact i would like it to copy and paste the data from the last 25 workbooks modified, in the folder.

The code i have so far is as follows:

Sub actualizar()

Dim destino as Workbook
Dim ficheiro as Workbook
Dim i as Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "D:\Tiago\SPI history\Histórico"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks

If .Execute() > 0 Then
Set destino = ThisWorkbook
For i = 1 to .FoundFiles.Count
Set ficheiro = Workbooks.Open.(.FoundFiles(i))
ficheiro.Worksheets(1).Copy after:= _
destino.Sheets(destino.Sheets.Count)
ActiveSheet.Name = ficheiro.Name
ficheiro.Close
Next i
End If
End With
Application.ScreenUpdating = False
End Sub


Now around the " If .Execute() > 0 Then " part i think this ".LastModified" application could be used, but i'm not sure how...