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 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
| Function AddAllExcelCelonisFileWithClick()
If Table_Exist("T001_CELONIS_Cumul") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Cumul"
End If
RefreshDatabaseWindow
sql1 = "CREATE TABLE T001_CELONIS_Cumul (Key_PO_Item CHAR(50) ,[Created on] CHAR(50),[Purchasing Document] CHAR(50)"
sql1 = sql1 & ", [Item] double , [Material] CHAR(50),[Short Text]CHAR(50)"
sql1 = sql1 & ", [Material Type] CHAR(50), [Material_Description] CHAR(50),[Hazardous material number] CHAR(50)"
sql1 = sql1 & ", Vendor CHAR(50),[Name1] CHAR(50),[Postal Code] CHAR(50)"
sql1 = sql1 & ", City CHAR(50),Country CHAR(50)"
sql1 = sql1 & ", [Storage Location] CHAR(50),[Vendor Material No] CHAR(50)"
sql1 = sql1 & ", [Default storage location for external procurement] CHAR(50),Plant CHAR(50)"
sql1 = sql1 & ", IT_System CHAR(50),[SAP source system] CHAR(50)"
sql1 = sql1 & ", Warehouse_Location CHAR(50));"
CurrentDb.Execute (sql1)
Debug.Print sql1
RefreshDatabaseWindow
If Table_Exist("T001_CELONIS_Raw") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End If
RefreshDatabaseWindow
DoCmd.TransferText acImportDelim, "ExtractCSVCelonis", "T001_CELONIS_Raw", _
"C:\Users\...\02_Projects\PreCheckOnDock\OPEN_ORDERS\CELONIS\1_Celonis_2014-01-01_2016-12-31.csv", True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q001_T001_CELONIS_Raw_to_T001_Cumul"
DoCmd.SetWarnings True
If Table_Exist("T001_CELONIS_Raw") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End If
DoCmd.TransferText acImportDelim, "ExtractCSVCelonis", "T001_CELONIS_Raw", _
"C:\Users\...\02_Projects\PreCheckOnDock\OPEN_ORDERS\CELONIS\2_Celonis_2017-01-01_2017-06-30.csv", True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q001_T001_CELONIS_Raw_to_T001_Cumul"
DoCmd.SetWarnings True
If Table_Exist("T001_CELONIS_Raw") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End If
DoCmd.TransferText acImportDelim, "ExtractCSVCelonis", "T001_CELONIS_Raw", _
"C:\Users\...\02_Projects\PreCheckOnDock\OPEN_ORDERS\CELONIS\3_Celonis_2017-07-01_2017-09-30.csv", True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q001_T001_CELONIS_Raw_to_T001_Cumul"
DoCmd.SetWarnings True
If Table_Exist("T001_CELONIS_Raw") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End If
DoCmd.TransferText acImportDelim, "ExtractCSVCelonis", "T001_CELONIS_Raw", _
"C:\Users\...\02_Projects\PreCheckOnDock\OPEN_ORDERS\CELONIS\4_Celonis_2017-10-01_2017-12-31.csv", True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q001_T001_CELONIS_Raw_to_T001_Cumul"
DoCmd.SetWarnings True
If Table_Exist("T001_CELONIS_Raw") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End If
DoCmd.TransferText acImportDelim, "ExtractCSVCelonis", "T001_CELONIS_Raw", _
"C:\Users\...\02_Projects\PreCheckOnDock\OPEN_ORDERS\CELONIS\5_Celonis_2018-01-01_2018-04-30.csv", True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q001_T001_CELONIS_Raw_to_T001_Cumul"
DoCmd.SetWarnings True
If Table_Exist("T001_CELONIS_Raw") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End If
DoCmd.TransferText acImportDelim, "ExtractCSVCelonis", "T001_CELONIS_Raw", _
"C:\Users\...\02_Projects\PreCheckOnDock\OPEN_ORDERS\CELONIS\6_Celonis_2018-05-01_2018-08-31.csv", True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q001_T001_CELONIS_Raw_to_T001_Cumul"
DoCmd.SetWarnings True
If Table_Exist("T001_CELONIS_Raw") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End If
DoCmd.TransferText acImportDelim, "ExtractCSVCelonis", "T001_CELONIS_Raw", _
"C:\Users\...\02_Projects\PreCheckOnDock\OPEN_ORDERS\CELONIS\7_Celonis_2018-09-01_2018-12-31.csv", True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q001_T001_CELONIS_Raw_to_T001_Cumul"
DoCmd.SetWarnings True
If Table_Exist("T001_CELONIS_Raw") Then
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End If
DoCmd.TransferText acImportDelim, "ExtractCSVCelonis", "T001_CELONIS_Raw", _
"C:\Users\...\02_Projects\PreCheckOnDock\OPEN_ORDERS\CELONIS\8_Celonis_2019-01-01_2022-12-31.csv", True
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q001_T001_CELONIS_Raw_to_T001_Cumul"
DoCmd.SetWarnings True
CurrentDb.Execute "DROP TABLE T001_CELONIS_Raw"
End Function |
Partager