To automize when source folder varies, receiver file name is fixed, file location changes

Hi!

I'm conducting a quantitative research for a study regarding some sales figures from an entity.

I would like to automatize some functions:

I have a master file where I want to have every week data copied to. This master file is always created into every week’s sales folder e.g. “sales week 1”. It is always copied from the previous month. And in that folder the master excel file has the very same name.

So e.g. in the folder
sales week 1:
master excel file
-cash week 1.csv
-credit week 1.csv
-debit week 1.csv

sales week 2:
master excel file
-cash week 2.csv
-credit week 2.csv
-debit week 2.csv

Etc.

Now comes the tricky part:
The sales figures come every week in separate excel files as shown above, and the name varies. To copy these files is taking a lot of time, which I could use for analysing. I copy then e.g. “cash week 1“ into the master excel file, into a sheet named “cash” so that the figures update to the specific week in question.

The master excel file has some pivot tables and graphs connected to them, so that’s why I’m copying the “raw-data” that I get every week into that specific file.

I have used cell-linking before a lot, but not with when source-file changes name every week. Not to mention that all the files are always put in a new folder.

Is it even possible to link/ automatize?

I will happily give some more information, and if I have been unclear I will happily explain in more detail.

Thank you in advance.

Kind regards,
Orthex

Hi Orhtex

Welcome to the forum.

You will not receive any replies from those who do not have the time / inclination / imagination to set up the file structure that you already have. (It's referred to as "re-inventing the wheel" )

If you submit a sample of what you have (all sensitive data removed) and a sample (mocked up, if necessary) of what you would like to achive, it will make it easier for contributors to see what is involved.

If might be a good idea if you were to update your details to show the version of Excel that you are using - it might make a difference to the solution.

Regards
Alastair

Hi Alastair!

Thanks for replying quickly and sorry for not responding quicker.

I have created some screenshots for a better understanding, hopefully

So I get the data like in picture 1. I receive it in an output folder. And they are saved only with the names of the workbooks and the reporting date as shown in picture 2. From here I copy the files to my drive, so that I have them as well, and to minimize the risk to have an administrator deleting them.

So I copy them to a folder I create with the corresponding date e.g. in this case July sales (pic 3). I then copy the master excel file from previous month (so that it keeps all formulas etc) as shown in pic 4 into the same folder.

For your understanding, the output files are as shown in pic 5. The files only vary by the rows, NOT the columns. So in other words columns are fixed and rows vary according to days in month etc.

1.jpg2.jpg3.jpg4.jpg5.jpg

So I would like to get these separate outputs, directly to the master excel file, so that the corresponding workbook will be entered as a sheet into the main excel file as shown in pic 6.

From these sheets (Cash, Cards,Credit,Invocing), the Analyze sheet will take information. Pic 7 shows just a simple graph taking the data from the Cash-sheet. Making the graphs automatically.

Now I'm no expert with excel, but I tried linking the files, but Excel does not have enough resources to manage it. And it would not work, due to that I always copý the outputs and master files from different folders, and saving them in the same folder?

I am very very greatfull, if anyone can help me in this case.

FYI. The output form is .csv, not xls/xlsx, is this a problem for e.g. VBA?

Thaaanks

Orthex



6.jpg7.jpg

Hi Orhtex

I have not been able to spend any time on the forum, but I am hoping to have some time very soon. I have not given up on you!

Regards
Alastair

Hi Orhtex

Please confirm that my understanding is correct, namely that you will put the data into a monthly directory and you want that information to be sucked into the monthly master file.

If you set up the chart in one month, that file can be used as the basis for next month.

If you confirm my understanding, I will work on it.

Regards
Alastair

Hi aydeegee,

Thank you for your input!

Yes you are correct: I will create a new monthly directory every month. I will copy the master file always from the previous month, into the new month. And as you mentioned, the solution I'm looking for is that the master file will copy a range from every monthly excel file I receive automatically.

Thanks a lot!!

Orhtex

Hi Orhtex

As you have not supplied any data files I have made up my own.

This works perfectly for my understanding of your issues - I hope my understanding is correct!

Be aware that the macro will grab all files in the given directory! If you have not used macros before you may have to adjust your security settings (File > Options > Trust Centre > Macro settings)

To run the macro click on the blue button on the Setup sheet. To see the macro, Hold down Ctrl and press F8 > Step into.

Let me know how you get on!

Regards
Alastair

Hey Alastair,

Wow! Thank you very much for your contribution!

I'm not so much familiar with Macros, but when I run it, I get an error in the Module 1 at the stage:
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

You understood me nearly perfect (you walked an extra mile), but all I need is the Macro to copy the range from the data1/2/3 (by columns, because the row amount varies every month, but not the columns). So no need that the macro will construct a summary automatically, I have pivot tables then taking info from these sheets.

I hope I'm not unclear. It's very clear in my head but not maybe in writing

Hi Orhtex

I'm not sure why you are getting the error - what message are you getting?

All that part of the macro is doing is getting the formatting right (as Excel has problems with dates that can be English or American)

I'm unclear about "no need that the macro will construct a summary automatically" as it does not!

I'm also unsure how "copy... by columns" works. The macro will bring across all columns and all rows (excluding the header) (irrespective of size), but no summary is done. Do you want to restrict the data in some way?

I have tweaked the Master file - but nothing that I believe will make any difference.

Regards
Alastair

Hi Alastair,

The error message is shown in the pictures below.

Capture.JPGCapture2.JPG

What I meant is that the information would be copied from the output folders (data1, data2... etc.) to the master file with header from range e.g. A:D (columns). And even greater if the macro will take all the information needless of the volume of the rows. But it should copy it to OWN sheets to the exact matching range e.g. A:D. Because in E&F there will be formulas. (Please observe that the range varies from A:D /A:E/A:F... etc.

Thank you once again Alastair for you input, I really appreciate it.

Kind regards, Orhtex

Hi Orhtex

I'm sorry, but I do not understand what you want.

If you still want me to assist, you must upload a sample of what you start with and what you want to achieve.

Regards
Alastair

Hi!

No problem, I cunstructed a sample file. Please have a look.

So the master file should take also the headers to the corresponding sheet in the master file. Because they differ from the output file.

Thanks a lot for your help, nice to know that there are helpful people around!!!

Data1.xlsxData2.xlsxData3.xlsxMaster.xlsm

Hi Orhtex

I now see what you are trying to achieve.

Because the data may be in columns A-E, the formula on the Master sheet will have to be moved to column F (even on those sheets where the data in only in A-D)

I hope that this is OK.

This will involve a change to the macro, but I will not have much time in the near future (we are being invaded by some small people ) but I will get onto it as soon as I can.

Regards
Alastair

Hi Orhtex

Strange ... I replied to this a couple of days ago, but my reply is not here?

If you did get the last solution, then here is the new improved version. Copy the Master file to a directory of your choosing and it will bring in all Excel file data.

Let me know how you get on.

Regards
Alastair