Hello everybody,
I have created a workbook that serves as both a database and to create cards that are printed. Each row / card has a unique ID number of type YY-MM-1234.
Since sometimes there are errors when writing the cards, I would like to be able to modify the desired row and update the workbook corresponding to the card.
For your information, The ID's line you want to modify is entered in a cell ("Ext" in the code) and then a sheet "CS" (in the code) is displayed with the line to be corrected. To finish the modified row is transferred to the "Card" workbook and then to the database.
The link to the record is in my database. I think it's a good idea to retrieve this link with a VlookUp function in order to open the desired workbook.
I can open the workbook I want here called "VLKUP1" (in the code), but I can't set it to be able to transfer my data. Also, I don't know how to replace the old database row with the corrected one in the database to avoid duplication.
Does anyone have an idea please?
Thank you in advance.
I have created a workbook that serves as both a database and to create cards that are printed. Each row / card has a unique ID number of type YY-MM-1234.
Since sometimes there are errors when writing the cards, I would like to be able to modify the desired row and update the workbook corresponding to the card.
For your information, The ID's line you want to modify is entered in a cell ("Ext" in the code) and then a sheet "CS" (in the code) is displayed with the line to be corrected. To finish the modified row is transferred to the "Card" workbook and then to the database.
The link to the record is in my database. I think it's a good idea to retrieve this link with a VlookUp function in order to open the desired workbook.
I can open the workbook I want here called "VLKUP1" (in the code), but I can't set it to be able to transfer my data. Also, I don't know how to replace the old database row with the corrected one in the database to avoid duplication.
Does anyone have an idea please?
Thank you in advance.
VBA Code:
Sub Collecte()
'Boucle sur tous les classeurs FNC ilot et transfère les données vers ce classeur.
'Les lignes collectées sont datées dans les fichiers sources afin d'éviter les doublons
Dim BDD As FileDialog 'déclare la variable BDD (Boîte de Dialogue Dossier)
Dim CA As String 'déclare la variable CA (Chemin d'Accès)
Dim CD As Workbook 'déclare la variable CD (Classeur Destination)
Dim OD As Worksheet 'déclare la variable OD (Onglet Destination)
Dim FS As String 'décalre la variable FS (Fichier Source)
Dim CS As Workbook 'déclare la variable CS (Classeur Source)
Dim OS As Worksheet 'déclare la variable OS (Onglet Source)
Dim DEST As Range 'déclare la variable DEST (celllue de DESTination)
Dim Ext, Ext2 As Variant
'Désactive la mise à jour de l'écran pour accélérer l'exécution
Application.ScreenUpdating = False
'chemin d'accès au dossier de stockage des classeurs de FNC qu'on souhaite mettre à jour
CA = "P:\04-Production Besancon\Qualité\02 - Fiches des Produits Non Conformes\ESSAIS" & "\"
' FNC Recherchée
Ext = ThisWorkbook.Worksheets("Accueil").Range("Concat_Num_FNC").Value
' ouvrir le classeur selon lien écrit dans Tableau FNC
VLKUP1 = WorksheetFunction.VLookup(Ext, Worksheets("Tableau FNC").Range("Tableau_FNC"), 59, False) 'lien classeur FNC pour pièce jointe
Workbooks.Open Filename:=VLKUP1
' Définir VLKUP1
Ext2 = VLKUP1.Value
Set CD = Workbooks(Ext2) 'définit la classeur destination CD
Set OD = CD.Sheets("Cartouche") 'définit l'onglet destination OD
Set CS = ActiveWorkbook 'définit la classeur source CS
Set OS = CS.Worksheets("Modification") 'définit l'onglet source OS (à adapter à ton cas, ici j'ai mis le premier onglet)
' Copier les données modifiées vers le classeur FNC à modifier
CS.OS.Range("A4:Q4").Copy: CD.OD.Range("A3:Q3").PasteSpecial Paste:=xlPasteValues
End Sub
