1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
|
Function EXPORT_TABLE()
'Declaration des variables
Dim RepertoireBase As String, NomFichier As String
Dim R001, R002, R003, QueryDef
Dim FSO As Object, folder As Object
'Fonction UnZippe Fichiers
Call DEZIPPE
'******************************TRAITEMENT FDC**********************************************
'Purge Table Principale
R001 = "DELETE * FROM IMPORTATION_RECAP"
DoCmd.RunSQL R001
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each folder In FSO.GetFolder(Application.CurrentProject.Path & "\").SubFolders
NomFichier = Dir(folder.Path & "\SIMPLES\" & "pressmaker_FDC*.xls", vbDirectory)
Do While NomFichier <> ""
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, "IMPORTATION_RECAP", folder.Path & "\SIMPLES\" & NomFichier, True '
Debug.Print NomFichier
NomFichier = Dir
Loop
Next
'Création Table pour Insertion Persos Poste
R002 = "SELECT DESIGNATION, NUMPARUTION INTO DESIGNATION FROM IMPORTATION_RECAP GROUP BY DESIGNATION, NUMPARUTION"
DoCmd.RunSQL R002
'Insertion Persos Poste
R003 = "INSERT INTO IMPORTATION_RECAP ( DESIGNATION, NUMPARUTION, ADR2, ADR3, ADR4, ADR5, ADR6, LG1 ) SELECT DESIGNATION.DESIGNATION, DESIGNATION.NUMPARUTION, ""M PERSO"" AS ADR2, ""RUE DE PERSOVILLE"" AS ADR3, ""PERSO"" AS ADR4, ""PERSO"" AS ADR5, ""99999 PERSOVILLE"" AS ADR6, ""9999999"" AS LG1 FROM DESIGNATION ORDER BY DESIGNATION.DESIGNATION"
DoCmd.RunSQL R003
'Exportation du Fichier Final
Set R004 = CurrentDb.CreateQueryDef("R004_EXPORT_TABLE", "Select * From IMPORTATION_RECAP ORDER BY NUMPARUTION, LG1 ")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "R004_EXPORT_TABLE", Application.CurrentProject.Path & "\EXPORT_TABLE_FDC.xls"
DoCmd.DeleteObject acTable, "DESIGNATION"
DoCmd.DeleteObject acQuery, "R004_EXPORT_TABLE"
'******************************TRAITEMENT FDP**********************************************
R001 = "DELETE * FROM IMPORTATION_RECAP"
DoCmd.RunSQL R001
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each folder In FSO.GetFolder(Application.CurrentProject.Path & "\").SubFolders
NomFichier = Dir(folder.Path & "\SIMPLES\" & "pressmaker_FDP*.xls", vbDirectory)
Do While NomFichier <> ""
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, "IMPORTATION_RECAP", folder.Path & "\SIMPLES\" & NomFichier, True '
Debug.Print NomFichier
NomFichier = Dir
Loop
Next
'Création Table pour Insertion Persos Poste
R002 = "SELECT DESIGNATION, NUMPARUTION INTO DESIGNATION FROM IMPORTATION_RECAP GROUP BY DESIGNATION, NUMPARUTION"
DoCmd.RunSQL R002
'Insertion Persos Poste
R003 = "INSERT INTO IMPORTATION_RECAP ( DESIGNATION, NUMPARUTION, ADR2, ADR3, ADR4, ADR5, ADR6, LG1 ) SELECT DESIGNATION.DESIGNATION, DESIGNATION.NUMPARUTION, ""M PERSO"" AS ADR2, ""RUE DE PERSOVILLE"" AS ADR3, ""PERSO"" AS ADR4, ""PERSO"" AS ADR5, ""99999 PERSOVILLE"" AS ADR6, ""9999999"" AS LG1 FROM DESIGNATION ORDER BY DESIGNATION.DESIGNATION"
DoCmd.RunSQL R003
'Exportation du Fichier Final
Set R004 = CurrentDb.CreateQueryDef("R004_EXPORT_TABLE", "Select * From IMPORTATION_RECAP ORDER BY NUMPARUTION, LG1 ")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "R004_EXPORT_TABLE", Application.CurrentProject.Path & "\EXPORT_TABLE_FDP.xls"
DoCmd.DeleteObject acTable, "DESIGNATION"
DoCmd.DeleteObject acQuery, "R004_EXPORT_TABLE"
'Fermeture BDD
DoCmd.Quit
End Function |
Partager