Copy paste from multiple Excel and Csv files to another excel file VBA

[youbitto]

Hello
I have Multiple CSV, XLS and XLSX files that I want to import into one sheets with the option to select the files

For example I have the Source files :

1. FMS.xls with columns (A , B, D, F, G, N) and the sheet is called "tableFacture"
2. HT.csv with columns (A, C, D , E, L, S) with "," separator and the sheet is called "Liste_des_factures hta"

For destination File "Port.xlsm" I want the source files to be pasted in a specific order (the sheet is called "Port")

for FMS.xls : (A to A), (B to B), (D to C), (F to G), (G to D), (N to E)
for HT.csv : (A to A), (C to B), (D to C) , (E to G), (L to D), (S to E)

The Column F in Port.xlsm I want it to be filled with "FMS" for the data imported from FMS.xls and "HT" for the data imported from HT.csv


I tried doing this on my own but nothing worked since I lack the knowledge and still starting VBA

FMS.xls

FMS.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	N° Memoire	Code FMS	Code Client	Intitule Client	Type	Categorie client	Date facturation	Date presentation	Date réglement	Date Acompte	Statut	Statut (Paiement)	Statut presentation	Montant TTC	Actions
2	932109000023	9300479	9300479	SKTM	Morale	Secteur Economique	12/10/2021				NON_REGLE	C_NON_REGLE<br/>Non reglé (CTC)	affectee non présenté	796.6500
3	932109000030	9300368	9300368	KASMA F L N	Morale	Administration	12/10/2021				NON_REGLE	C_NON_REGLE<br/>Non reglé (CTC)	affectee non présenté	82364.2300
4	932109000043	9300231	9300231	AIR ALGERIE ADRAR	Morale	Administration	12/10/2021				NON_REGLE	C_NON_REGLE<br/>Non reglé (CTC)	affectee non présenté	25548.7900
5	932111000027	9300664	9300664	LE DIRECT.ACTION SOCIALE ADRAR	Morale	Administration	12/12/2021				NON_REGLE	C_NON_REGLE<br/>Non reglé (CTC)	affectee non présenté	18114.4300
6	932106000051	9300559	9300559	D E P ADRAR	Morale	Administration	19/07/2021				NON_REGLE	C_NON_REGLE<br/>Non reglé (CTC)	affectee non présenté	137537.2700
tableFacture

HT.csv

HT.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	N° facture	N° Contrat	Code Client	Intitule Client	Categorie	Traite	Affectation	Statut presentation	Statut	Statut paiement	Etat	Date facturation	Date presentation	Date encaissement en attente de validation	Date retablissement	Date réglement	Date Acompte	Date annulation	Montant TTC	Actions
2	932202A00073	9314653E1998	9391354	MENUISERIE GRL MOULAY OMAR MED	Secteur Economique	Non	Non	Remis	non réglé<br/>	C_NON_REGLE<br/>Non reglé (CTC)	Fraîche	02/03/2022							3637.0800	org.primefaces.component.commandbutton.CommandButton@aef61a0
3	932111A01183	9350227E2015	9392471	PICINE 25 M3 ZT KOUNTA	Administration	Non	Non	affectee non présenté	non réglé<br/>	C_NON_REGLE<br/>Non reglé (CTC)	Fraîche	02/12/2021							29074.3700	org.primefaces.component.commandbutton.CommandButton@aef61a0
4	932408A01305	9360350E2014	9391770	APC TAMANTIT	Administration	Non	Non	non présenté	non réglé<br/>	C_NON_REGLE<br/>Non reglé (CTC)	Fraîche	02/09/2024							46435.4400	org.primefaces.component.commandbutton.CommandButton@aef61a0
5	932408A00481	9315068E2020	9320A000060	Forage agricole N° 04 O/Ghozala Timmi Adrar	Secteur Economique	Non	Non	non présenté	non réglé<br/>	C_NON_REGLE<br/>Non reglé (CTC)	Fraîche	02/09/2024							148594.8000	org.primefaces.component.commandbutton.CommandButton@aef61a0
Liste_des_factures hta

Port.xlsx

Port.xlsm
	A	B	C	D	E	F	G
12
13	N° Memoire	Code Client	Intitule Client	Date facturation	Montant TTC	ener	Categorie
14
15
16
17
Port

 

[John_w]

Try this macro.

For destination File "Port.xlsm"

The line Set destWb = Workbooks.Open(ThisWorkbook.Path & "\Port.xlsm") opens "Port.xlsm" (in the same folder as the macro workbook) if it isn't already open.

Public Sub Import_Files()

    Dim selectedFiles As Variant
    Dim sourceWb As Workbook, destWb As Workbook
    Dim destSheet As Worksheet
    Dim file As Variant
    Dim nextRow As Long, numRows As Long
   
    selectedFiles = Application.GetOpenFilename(MultiSelect:=True, FileFilter:="Excel and CSV Files,*.xlsx;*.xls;*.csv", Title:="Select files to import")
    If VarType(selectedFiles) = vbBoolean Then Exit Sub  'Cancel clicked
   
    Application.ScreenUpdating = False
    
    Set destWb = Workbooks.Open(ThisWorkbook.Path & "\Port.xlsm")
    Set destSheet = destWb.Worksheets("Port")
    
    With destSheet
        nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    End With

    For Each file In selectedFiles
    
        Set sourceWb = Workbooks.Open(file, ReadOnly:=True)
        
        Select Case Mid(file, InStrRev(file, "."))
        
            Case ".xlsx", ".xls"
            
                'for FMS.xls : (A to A), (B to B), (D to C), (F to G), (G to D), (N to E)
                'Column F filled with "FMS"
               
                With sourceWb.Worksheets("tableFacture")
                    numRows = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
                    .Range("A2").Resize(numRows).Copy destSheet.Cells(nextRow, "A")
                    .Range("B2").Resize(numRows).Copy destSheet.Cells(nextRow, "B")
                    .Range("D2").Resize(numRows).Copy destSheet.Cells(nextRow, "C")
                    .Range("F2").Resize(numRows).Copy destSheet.Cells(nextRow, "G")
                    .Range("G2").Resize(numRows).Copy destSheet.Cells(nextRow, "D")
                    .Range("N2").Resize(numRows).Copy destSheet.Cells(nextRow, "E")
                    destSheet.Cells(nextRow, "F").Resize(numRows).Value = "FMS"
                    nextRow = nextRow + numRows
                End With
                
            Case ".csv"
            
                'for HT.csv : (A to A), (C to B), (D to C), (E to G), (L to D), (S to E)
                'Column F filled with "HT"
           
                With sourceWb.Worksheets(1)
                    numRows = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
                    .Range("A2").Resize(numRows).Copy destSheet.Cells(nextRow, "A")
                    .Range("C2").Resize(numRows).Copy destSheet.Cells(nextRow, "B")
                    .Range("D2").Resize(numRows).Copy destSheet.Cells(nextRow, "C")
                    .Range("E2").Resize(numRows).Copy destSheet.Cells(nextRow, "G")
                    .Range("L2").Resize(numRows).Copy destSheet.Cells(nextRow, "D")
                    .Range("S2").Resize(numRows).Copy destSheet.Cells(nextRow, "E")
                    destSheet.Cells(nextRow, "F").Resize(numRows).Value = "HT"
                    nextRow = nextRow + numRows
                End With
       
        End Select
        
        sourceWb.Close False
            
    Next

    Application.ScreenUpdating = True
    
End Sub

 

[youbitto]

Try this macro.



The line Set destWb = Workbooks.Open(ThisWorkbook.Path & "\Port.xlsm") opens "Port.xlsm" (in the same folder as the macro workbook) if it isn't already open.

Public Sub Import_Files()

    Dim selectedFiles As Variant
    Dim sourceWb As Workbook, destWb As Workbook
    Dim destSheet As Worksheet
    Dim file As Variant
    Dim nextRow As Long, numRows As Long
  
    selectedFiles = Application.GetOpenFilename(MultiSelect:=True, FileFilter:="Excel and CSV Files,*.xlsx;*.xls;*.csv", Title:="Select files to import")
    If VarType(selectedFiles) = vbBoolean Then Exit Sub  'Cancel clicked
  
    Application.ScreenUpdating = False
   
    Set destWb = Workbooks.Open(ThisWorkbook.Path & "\Port.xlsm")
    Set destSheet = destWb.Worksheets("Port")
   
    With destSheet
        nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    End With

    For Each file In selectedFiles
   
        Set sourceWb = Workbooks.Open(file, ReadOnly:=True)
       
        Select Case Mid(file, InStrRev(file, "."))
       
            Case ".xlsx", ".xls"
           
                'for FMS.xls : (A to A), (B to B), (D to C), (F to G), (G to D), (N to E)
                'Column F filled with "FMS"
              
                With sourceWb.Worksheets("tableFacture")
                    numRows = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
                    .Range("A2").Resize(numRows).Copy destSheet.Cells(nextRow, "A")
                    .Range("B2").Resize(numRows).Copy destSheet.Cells(nextRow, "B")
                    .Range("D2").Resize(numRows).Copy destSheet.Cells(nextRow, "C")
                    .Range("F2").Resize(numRows).Copy destSheet.Cells(nextRow, "G")
                    .Range("G2").Resize(numRows).Copy destSheet.Cells(nextRow, "D")
                    .Range("N2").Resize(numRows).Copy destSheet.Cells(nextRow, "E")
                    destSheet.Cells(nextRow, "F").Resize(numRows).Value = "FMS"
                    nextRow = nextRow + numRows
                End With
               
            Case ".csv"
           
                'for HT.csv : (A to A), (C to B), (D to C), (E to G), (L to D), (S to E)
                'Column F filled with "HT"
          
                With sourceWb.Worksheets(1)
                    numRows = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
                    .Range("A2").Resize(numRows).Copy destSheet.Cells(nextRow, "A")
                    .Range("C2").Resize(numRows).Copy destSheet.Cells(nextRow, "B")
                    .Range("D2").Resize(numRows).Copy destSheet.Cells(nextRow, "C")
                    .Range("E2").Resize(numRows).Copy destSheet.Cells(nextRow, "G")
                    .Range("L2").Resize(numRows).Copy destSheet.Cells(nextRow, "D")
                    .Range("S2").Resize(numRows).Copy destSheet.Cells(nextRow, "E")
                    destSheet.Cells(nextRow, "F").Resize(numRows).Value = "HT"
                    nextRow = nextRow + numRows
                End With
      
        End Select
       
        sourceWb.Close False
           
    Next

    Application.ScreenUpdating = True
   
End Sub

Wow thank you very much, it worked perfectly I will try to understand what you did, I get it worked with a multiple codes and opening each file separately , a very beginner method but yours is perfect with what I had in mind