How to copy paste data from multiple excel sheet in one workbook in sequence?

[Dave Smith]

Hi,

A big thanks in advance,

I want to create a database using VBA code where I need to copy certain data from multiple excel file in to one excel file.
The user will enter the path of the folder where the multiple excel files are kept, and the code should copy following thing's from multiple excel in to one excel file:
1. file name
2. customer name
3. location
4. product name
5. date

I want to do this process in sequence like 1st enter the file name which is opened in master file, then copy specific cell value from the opened excel file & activate the master file and paste it.
2nd close the opened file open second file and repeat the copy paste procedure.
3rd after copying all the data from all the files present in the folder it should save the master database excel file


I have written the code to get file name from the folder in master data base file as shown below but i am not able to figuring out how to open that file copy and paste in this in sequence

Sub getfolderdfilenames()


Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objfile As Scripting.File
Dim nextrow As Long

Set objFSO = CreateObject("Scripting.FilesystemObject")
Set objFolder = objFSO.GetFolder("C:\Users\SHMEHTA\Desktop\CFD Tool For Request")

nextrow = Database.Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each objfile In objFolder.Files

Database.Cells(nextrow, 2) = objfile.Name
nextrow = nextrow + 1

Next objfile
End Sub

Any Help is appreciated plz....

Regards,
Dave

 

[Dave Smith]

If the names are on the sheet, then you need a different macro, not like the one you put in the OP.
You should not read all the files in the folder, only the ones that interest you.

Try this:

Sub GetInfoFromFiles()
  Dim wb As Workbook, p As String, c As Range
  p = "C:\Users\SHMEHTA\Desktop\CFD Tool For Request\"
  Application.ScreenUpdating = False
  For Each c In Database.Range("B3", Database.Range("B" & Rows.Count).End(3))
    If Dir(p & c.Value) <> "" Then
      Set wb = Workbooks.Open(p & c.Value)
      Database.Cells(c.Row, 3).Resize(1, 4).Value = Array(wb.Sheets(1).[b1], wb.Sheets(1).[b2], wb.Sheets(1).[B6], wb.Sheets(1).[B7])
      wb.Close False
    End If
  Next
  ActiveWorkbook.Save
End Sub

Sorry DanteAmor for giving you little bit more trouble.... i am extremely sorry

I have tried this code but its not working for me like its not copying data from the other files...
The 1st code which you shared works so good that it does everything which i need to do by using macro only problem in that code is taking data from all the file present in the folder every time i run the macro instead of taking data only from the new file.
Can you pl help me in that pl..... i request you....

Regards,
Dave

 

[DanteAmor]

You must put the complete example.
What do you have in the "Database" sheet, in column "B". Before running the macro.
Now each of those files in column B, what do they have on the first sheet, you must put the data of each file here. Take for example 2 or 3 files. Use XL2BB tool minisheets.
And lastly, and very importantly, what is the result in the database sheet.

My examples:
Sheet Database berfore running the macro:

varios 19ene2022.xlsm
	A	B	C	D	E	F
1	Database
2	No.	File name	Date	Location	Customer	Product
3	1	Audit.xlsx
4	2	Copy of Template 2.xlsm
5	3	destination.xlsx
Hoja1

workbook audit:

Audit.xlsx
	A	B
1	Date	15/03/2022
2	location	cdmx
3	iteration	1
4
5
6	customer	alc001
7	product	topbox
Sheet1

workbook Copy of Template 2

Copy of Template 2.xlsx
	A	B
1	Date	22/07/2022
2	location	edomex
3	iteration	2
4
5
6	customer	nextel
7	product	modem-we
Sheet1

workbook destination:

destination.xlsx
	A	B
1	Date	02/01/2022
2	location	gdl
3	iteration	3
4
5
6	customer	morena
7	product	smarttv
Sheet1

Result after running the macro

varios 19ene2022.xlsm
	A	B	C	D	E	F
1	Database
2	No.	File name	Date	Location	Customer	Product
3	1	Audit.xlsx	15/03/2022	cdmx	alc001	topbox
4	2	Copy of Template 2.xlsx	22/07/2022	edomex	nextel	modem-we
5	3	destination.xlsx	02/01/2022	gdl	morena	smarttv
Hoja1

If you contribute with good examples, it will be easier to help you.

 

[Dave Smith]

You must put the complete example.
What do you have in the "Database" sheet, in column "B". Before running the macro.
Now each of those files in column B, what do they have on the first sheet, you must put the data of each file here. Take for example 2 or 3 files. Use XL2BB tool minisheets.
And lastly, and very importantly, what is the result in the database sheet.

My examples:
Sheet Database berfore running the macro:

varios 19ene2022.xlsm
	A	B	C	D	E	F
1	Database
2	No.	File name	Date	Location	Customer	Product
3	1	Audit.xlsx
4	2	Copy of Template 2.xlsm
5	3	destination.xlsx
Hoja1

workbook audit:

Audit.xlsx
	A	B
1	Date	15/03/2022
2	location	cdmx
3	iteration	1
4
5
6	customer	alc001
7	product	topbox
Sheet1

workbook Copy of Template 2

Copy of Template 2.xlsx
	A	B
1	Date	22/07/2022
2	location	edomex
3	iteration	2
4
5
6	customer	nextel
7	product	modem-we
Sheet1

workbook destination:

destination.xlsx
	A	B
1	Date	02/01/2022
2	location	gdl
3	iteration	3
4
5
6	customer	morena
7	product	smarttv
Sheet1

Result after running the macro

varios 19ene2022.xlsm
	A	B	C	D	E	F
1	Database
2	No.	File name	Date	Location	Customer	Product
3	1	Audit.xlsx	15/03/2022	cdmx	alc001	topbox
4	2	Copy of Template 2.xlsx	22/07/2022	edomex	nextel	modem-we
5	3	destination.xlsx	02/01/2022	gdl	morena	smarttv
Hoja1

If you contribute with good examples, it will be easier to help you.

 

[Dave Smith]

You must put the complete example.
What do you have in the "Database" sheet, in column "B". Before running the macro.
Now each of those files in column B, what do they have on the first sheet, you must put the data of each file here. Take for example 2 or 3 files. Use XL2BB tool minisheets.
And lastly, and very importantly, what is the result in the database sheet.

My examples:
Sheet Database berfore running the macro:

varios 19ene2022.xlsm
	A	B	C	D	E	F
1	Database
2	No.	File name	Date	Location	Customer	Product
3	1	Audit.xlsx
4	2	Copy of Template 2.xlsm
5	3	destination.xlsx
Hoja1

workbook audit:

Audit.xlsx
	A	B
1	Date	15/03/2022
2	location	cdmx
3	iteration	1
4
5
6	customer	alc001
7	product	topbox
Sheet1

workbook Copy of Template 2

Copy of Template 2.xlsx
	A	B
1	Date	22/07/2022
2	location	edomex
3	iteration	2
4
5
6	customer	nextel
7	product	modem-we
Sheet1

workbook destination:

destination.xlsx
	A	B
1	Date	02/01/2022
2	location	gdl
3	iteration	3
4
5
6	customer	morena
7	product	smarttv
Sheet1

Result after running the macro

varios 19ene2022.xlsm
	A	B	C	D	E	F
1	Database
2	No.	File name	Date	Location	Customer	Product
3	1	Audit.xlsx	15/03/2022	cdmx	alc001	topbox
4	2	Copy of Template 2.xlsx	22/07/2022	edomex	nextel	modem-we
5	3	destination.xlsx	02/01/2022	gdl	morena	smarttv
Hoja1

If you contribute with good examples, it will be easier to help you.

OK DanteAmor I got your question & sorry for not expalining how the files looks before and after running the macro

There will be a folder (named Database) where all the excel files will be kept and with in that there is a folder (Database file) where only master database file is kept. Now i will keep new files in folder named Database.
The macro should compare the file name located in the master data base file in "column B" with the files present in the Database folder, now if the file name "File_1.xlsx" is present in the master database then the macro should no copy data again from it rather then it should go to other file present in the folder to get file name and other info.

Eg: Now if I put the new file name "File_2.xlsx" in the database folder then the macro should get the data only from this file only instead of getting data again from the file_1

Before

master database.xlsm
	A	B	C	D	E	F
1	Database
2	Sr No.	File name	Date	Location	Customer name	Product name
3	1	File_1.xlsx	18/1/2022	GHF	HTU	Spanner(M16)
4
5
6
7
8
database

After running macro

master database.xlsm
	A	B	C	D	E	F
1	Database
2	Sr No.	File name	Date	Location	Customer name	Product name
3	1	File_1.xlsx	18/1/2022	GHF	HTU	Spanner(M16)
4	2	File_2.xlsx	16/1/2022	EVG	IBS	Washer(M45)
5
6
database

Also pl. have a look to the images Which I am attaching to get an idea of the folder, files name and location.

I hope i have tried to explain properly....

 

[DanteAmor]

It would be fabulous if your example is complete, XL2BB minisheet is missing from File_2.xlsx file. We practically have to guess where the data goes.
The information is obvious to you because you are familiar with your data.
Remember, you are asking us for help, you must provide complete information.
I don't understand where you get the value of column A "Sr No" is it a consecutive or you get it from the file?
-----

Also pl. have a look to the images Which I am attaching to get an idea of the folder, files name and location.

I don't understand what you mean by that.
The folder is written in the macro code, just as you put it in your OP:

Set objFolder = objFSO.GetFolder("C:\Users\SHMEHTA\Desktop\CFD Tool For Request")

-----
Try this:

Sub getfolderdfilenames_3()
  Dim objFSO As Scripting.FileSystemObject
  Dim objFolder As Scripting.Folder
  Dim objfile As Scripting.File
  Dim nextrow As Long
  Dim wb As Workbook
  Dim f As Range
  Dim sName As String
  
  Application.ScreenUpdating = False
  Set objFSO = CreateObject("Scripting.FilesystemObject")
  Set objFolder = objFSO.GetFolder("C:\Users\SHMEHTA\Desktop\CFD Tool For Request")
  
  nextrow = Database.Cells(Rows.Count, 1).End(xlUp).Row + 1
  For Each objfile In objFolder.Files
    If InStrRev(objfile, ".xls", , vbTextCompare) > 0 Then
      sName = Mid(objfile, InStrRev(objfile, "\") + 1)
      Set f = Database.Range("B:B").Find(sName, , xlValues, xlWhole, , , False)
      If f Is Nothing Then
        Set wb = Workbooks.Open(objfile)
        Database.Cells(nextrow, 1) = wb.Sheets(1).Range("B3")
        Database.Cells(nextrow, 2) = objfile.Name
        Database.Cells(nextrow, 3) = wb.Sheets(1).Range("B1")
        Database.Cells(nextrow, 4) = wb.Sheets(1).Range("B2")
        Database.Cells(nextrow, 5) = wb.Sheets(1).Range("B6")
        Database.Cells(nextrow, 6) = wb.Sheets(1).Range("B7")
        nextrow = nextrow + 1
        wb.Close False
      End If
    End If
  Next objfile
  ActiveWorkbook.Save
End Sub

 

[Dave Smith]

It would be fabulous if your example is complete, XL2BB minisheet is missing from File_2.xlsx file. We practically have to guess where the data goes.
The information is obvious to you because you are familiar with your data.
Remember, you are asking us for help, you must provide complete information.
I don't understand where you get the value of column A "Sr No" is it a consecutive or you get it from the file?
-----

I don't understand what you mean by that.
The folder is written in the macro code, just as you put it in your OP:

-----
Try this:

Sub getfolderdfilenames_3()
  Dim objFSO As Scripting.FileSystemObject
  Dim objFolder As Scripting.Folder
  Dim objfile As Scripting.File
  Dim nextrow As Long
  Dim wb As Workbook
  Dim f As Range
  Dim sName As String
 
  Application.ScreenUpdating = False
  Set objFSO = CreateObject("Scripting.FilesystemObject")
  Set objFolder = objFSO.GetFolder("C:\Users\SHMEHTA\Desktop\CFD Tool For Request")
 
  nextrow = Database.Cells(Rows.Count, 1).End(xlUp).Row + 1
  For Each objfile In objFolder.Files
    If InStrRev(objfile, ".xls", , vbTextCompare) > 0 Then
      sName = Mid(objfile, InStrRev(objfile, "\") + 1)
      Set f = Database.Range("B:B").Find(sName, , xlValues, xlWhole, , , False)
      If f Is Nothing Then
        Set wb = Workbooks.Open(objfile)
        Database.Cells(nextrow, 1) = wb.Sheets(1).Range("B3")
        Database.Cells(nextrow, 2) = objfile.Name
        Database.Cells(nextrow, 3) = wb.Sheets(1).Range("B1")
        Database.Cells(nextrow, 4) = wb.Sheets(1).Range("B2")
        Database.Cells(nextrow, 5) = wb.Sheets(1).Range("B6")
        Database.Cells(nextrow, 6) = wb.Sheets(1).Range("B7")
        nextrow = nextrow + 1
        wb.Close False
      End If
    End If
  Next objfile
  ActiveWorkbook.Save
End Sub

A big thank you DanteAmor For this fabulous code It completely worked for me
Once again thank you for helping me

 

[DanteAmor]

Im glad to help you, thanks for the feedback