VBA Code To Copy Weights from all workbooks to one main workbook

[Mohammednt0]

Good Day,


Could you please help me to create a VBA Code that will Copy all the weights from many excel files and sheets to the main workbook file Named “Daily Cars Transfers”


Inside “Daily Transfers” Folder: There are 12 Folders named from 1 to 12 folder, each folder represents a month, these 12 folders contain the daily excel files and each file has multiple sheets, I need to copy the total weight from each excel file and paste it in the correct location inside the Daily Cars Transfers Main Workbook.

A. The code will process all 12 folders, so it will start from folder “1” (which represent the first month.)

B. Then, it will process all excel files inside folder “1”

C. Each excel file has multiple sheets, the code will Go to all sheets and identify: Material type, location, date, and Net Weight, to copy them to “Daily Cars Transfers“ report

To identify Material and location the code will go to row 2 then cells “B:P” as u can see in the image below, in cells “B:P” The first word is the material which is “corn”, and the second word is the location which is “Riyadh”.

the code will copy the net weight total, as u can see in the image below.
the code will use the first date to copy the total net weight to the correct cell , as u can see in the image below.



D. After getting Material type, location, date, and Net Weight, the code should paste Net Weight in the correct location inside the Daily Cars Transfers file,

• So according to the image above the date is (01-06-2023), Month 6 day 1, so the code will open Daily Cars Transfers file, and it will go to sheet 6 (which means month 6) , then day 1

• the material is corn so the code will go to corn ,
• location is Riyadh so the code will go to Riyadh,
• the day is one, so the code will go to day one, then paste total net weight (337860) in cell B6



• then it will go to the next sheet and do the exact same process




• Date is (01-06-2023), Month 6 day 1, the code will go to Daily Cars Transfers file, sheet 6 , day 1
• the material is corn so the code will go to corn ,
• location is Kharj so the code will go to Kharj,
• the day is one, so the code will go to day one, then paste total net weight in cell C6



and so on. When the code is done with all sheets, it should go for the next files in folder 6, then folder 7 till the end of the folders.

Thanks in advance.

[snb]

Do not split similar data into separate sheets, files and folders.
Integrate all data (years. months, dates) into 1 table. (2^20 entries).

Never use merged cells or pictures in a data worksheet.
Use listobject, autofilter, pivottables, slicers to analyze the data.

[Mohammednt0]

i agree with you, but its not up to me, unfortunately i cant change the way they work, its so time consuming.

[DocAElstein]

Hello,
I don’t think there is really anything too difficult here, and I also expect most of the coding required should be within your abilities to do yourself.
You have given a very good clear explanation of what you want.
So the solution requires either you or someone else to carefully work through your explanation and then do what you want with coding. The coding is not difficult. But it may take a while to do.
I think generally a help forum is intended to help you, rather than do a lot of work for you.

Never the less, I will give you a start.
Let’s see if we can do this bit:
Inside “Daily Transfers” Folder: There are 12 Folders named from 1 to 12 folder, each folder represents a month, these 12 folders contain the daily excel files and each file has multiple sheets, I need to …….
A. The code will process all 12 folders, so it will start from folder “1” (which represent the first month.)
B. Then, it will process all excel files inside folder “1”
In fact that may be better written…
Inside “Daily Transfers” Folder: There is a folder named 2023. In that folder are 12 Folders named from 01 to 12, each folder represents a month, these 12 folders contain the daily excel files, 01.xlsx , 02.xlsx …. etc. and each file has multiple sheets, I need to ……
A. The code will process all 12 folders, so it will start from folder “1” (which represent the first month.)
B. Then, it will process all excel files inside folder “1”

Perhaps if I show you how to loop through and process each file, in each folder, then you can figure out how to copy / transfer data etc.


Two common ways to process files in folders, like you want to loop through and process each file, in each folder, is one of these
_ Using the "Scripting.FileSystemObject" object
_ using the Dir function

The Dir function is simpler, but difficult to use when wanting to loop through lots of folders within folders. The Dir function is good for looping through files in a single folder, or for looping through files in a known number of folders. That is what you want to do, so let’s look at that for you ( I note your example data only has 4 months Folders, 03 04 05 06, not 12 folders)


Here is a macro ( in next post) to start you off with. I have 'commented it extensively . It basically

_ loops through all your excel files .
__ At excel file, it
____gives one example of how you can get some information from the Excel file into the main workbook, without opening the excel file. This way of getting data might be a bit too restrictive for all you want to do, - I am just giving it as an example because if you can manage to do everything without opening a file, then it will be quicker
____It then opens and closes each Excel file.

So the further work you need to do is to either
replace this bit
' Do all your transferring of data, etc....
, with the coding to do the various data transfer .

Or, Alternatively you could remove the section that opens and closes the file, and see if you can do all you want to do by getting all the data out of the Excel file without opening it, in a similar way to the one example I gave. But this my be a bit too advanced for you.

I think at the point that you have the main workbook and an excel file open , you should be able to write the coding to do the various transferring of data that you want.


In order for the macro to work you must have the file, Daily Cars Transfers.xls , in the same folder as your example Data Folder which you gave,
https://i.postimg.cc/cCyDSDW4/Inventory ... s-2023.jpg






Alan





_.___________________________________________________

Daily Cars Transfers.xls https://app.box.com/s/tu69rx3wlnjfgu0d9hx4cmdkr7fwxzto

Macro in next post

[DocAElstein]

MACRO FOR LAST POST

Code: Select all

Option Explicit
Sub ProcessExcelFilesInAFolder()  '  http://www.eileenslounge.com/viewtopic.php?f=30&t=39841   https://app.box.com/s/tu69rx3wlnjfgu0d9hx4cmdkr7fwxzto
' The Year Folder path.  Since we know where it is in relation to this workbook,  Daily Cars Transfers.xls  ,  we can simplify getting the full path as follows
Dim MeFolderPth As String
 Let MeFolderPth = ThisWorkbook.Path & "\Data\Daily Transfers\2023\"

' Loop for each Month Folder   ( I note your example data only has 4 months Folders, 03 04 05 06, not 12 folders)
Dim Cnt As Long
    For Cnt = 3 To 6 Step 1  ' ( I note your example data only has 4 months Folders, 03 04 05 06, not 12 folders)
    Dim MnthFlderPth As String
     Let MnthFlderPth = MeFolderPth & Format(Cnt, "00") ' I can't just use Cnt as that would give me  3 4 5 6  ,  the  Format( )    thing lets me get the format as i want it
    
    'I need now to somehow loop through the Day Excel Files. Months won't aleays have the same number of days, which complicates things, but fortunately i can use the  Dir  function in a way that allows me to go through all the files in a folder
    Dim XlFle As String
     Let XlFle = Dir(MnthFlderPth & "\*.xlsx", vbNormal) '  this gets the first  excel  .xlsx file it finds at that matches the rest of the string -- The  *  is a "wild card" , which means in this case, we look for any file name as long as it has the extension of  .xlsx  and is at the path specified
     MsgBox prompt:="First found file is " & XlFle & "  at path  " & MnthFlderPth
     'Debug.Print "First found file is " & XlFle & "  at path  " & MnthFlderPth
    ' The next  Do Loop While  type coding is a very common way that we use the   Dir  function
    
        Do While XlFle <> ""  '_-============================================================================
        ' as long as i find another file at the end of the loop here ' --###   , then I will keep doing the next lot of stuff
        MsgBox prompt:="I am now processing file    " & XlFle & "  at path  " & MnthFlderPth
        'Debug.Print "I am now processing file    " & XlFle & "  at path  " & MnthFlderPth
    
        '  Now do what you want to do, example, I can get some info from a closed workbook,
         Let ThisWorkbook.Worksheets.Item(1).Range("A1").Value = "='" & MnthFlderPth & "\[" & XlFle & "]SOYA'!$J$6"
         Let ThisWorkbook.Worksheets.Item(1).Range("A1").Value = ThisWorkbook.Worksheets.Item(1).Range("A1").Value  ' Change the referrence path to the actual value
        MsgBox prompt:="I just took the value from cell  J6  in  worksheet  SOYA  in the excel workbook and put it in  the first cell in the main file. You can see that it has the value of  " & ThisWorkbook.Worksheets.Item(1).Range("A1").Value
        'Debug.Print "I just took the value from cell  J6  in  worksheet  SOYA  in the excel workbook and put it in  the first cell in the main file. You can see that it has the value of  " & ThisWorkbook.Worksheets.Item(1).Range("A1").Value
        '  Now do what you want to do, example open file, do some data transferring, close file
         Workbooks.Open Filename:=MnthFlderPth & "\" & XlFle
        
        ' Do all your transferring of data, etc here
        
        ' Close and save the file
         Workbooks("" & XlFle & "").Close SaveChanges:=False
    
    
         Let XlFle = Dir   ' --##    THIS IS VERY IMORTANT, but it is easy to forget.   If I use  Dir  but don't give any other infomation, then it will try to find the next file using the same search criteria used before which in our case was    MnthFlderPth & "\*.xlsx"     Another thing very convenient for our purposes is that   Dir   will not start again after finding all the files. Instead it will return  ""  once it has been used to find all the files. So that allows us to keep the loop going only While it does not return  ""      
        Loop ' While XlFle <> ""   '_-=======================================================================
    
    Next Cnt
End Sub

_._______________________________________________

Share ‘Daily Cars Transfers.xls’
https://app.box.com/s/tu69rx3wlnjfgu0d9hx4cmdkr7fwxzto

[Mohammednt0]

Dear Mr. Alan
I Apologies for my late reply. thanks for giving me some of your time.
again thank you for the code, i really appreciate it, im gonna modify it and use it.
i understand that the way i asked you guys was inappropriate you are here to help us, not to do the work for us,
sorry i didn't noticed till its too late.

thanks.

[DocAElstein]

Sure, If you try and get stuck or have a problem then I expect someone will help

You have got a good detailed explanation and that is usually half the problem solved, at least if you understand the English language. VBA is very basic, and half of the coding speaks for itself, it does what it says

Alan