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 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159
| Option Explicit
Public Sub preparation()
Dim Fichier As Variant
Dim Wb As Workbook
Dim lastcell, Annee, Mois As Integer
Dim MyRange, chemin As String
'Ouvre explorateur afin de sélectioner le fichier à modifier
Fichier = Application.GetOpenFilename("Classeurs Excel (*.xls), *.xls")
If Fichier = False Then Exit Sub
Set Wb = Workbooks.Open(Fichier)
'Copie et collage des colonnes vers le fichier cible
Application.ScreenUpdating = False
lastcell = Range("N1").End(xlDown).Row
Sheets("Security Valuation").Range(Cells(2, "N"), Cells(lastcell, "O")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("A2").Select
ActiveSheet.Paste
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "K"), Cells(lastcell, "K")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("C2").Select
ActiveSheet.Paste
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "X"), Cells(lastcell, "Y")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("D2").Select
ActiveSheet.Paste
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "M"), Cells(lastcell, "M")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("F2").Select
ActiveSheet.Paste
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "U"), Cells(lastcell, "U")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("G2").Select
ActiveSheet.Paste
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "T"), Cells(lastcell, "T")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("H2").Select
ActiveSheet.Paste
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "E"), Cells(lastcell, "E")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("I2").Select
ActiveSheet.Paste
ActiveSheet.Range(Cells(2, "J"), Cells(lastcell, "J")).Value = "EASY"
ActiveSheet.Range(Cells(2, "K"), Cells(lastcell, "K")).Value = "IS"
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "S"), Cells(lastcell, "S")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("L2").Select
ActiveSheet.Paste
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "J"), Cells(lastcell, "J")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("M2").Select
ActiveSheet.Paste
Windows("Security Valuation.xls").Activate
ActiveSheet.Range(Cells(2, "X"), Cells(lastcell, "X")).Select
Selection.Copy
Windows("Macro.xls").Activate
Sheets("Holdings").Select
Range("N2").Select
ActiveSheet.Paste
Wb.Close
Application.ScreenUpdating = True
Annee = Year(Range("I2"))
Mois = Month(Range("I2"))
ActiveWorkbook.SaveAs Filename:="C:\Security Valuation_" & Annee & "_" & Mois + 1 & ".xls"
End Sub
Public Sub exportation()
Dim Db As DAO.Database
Dim TWB As Object
Dim strSQL As String
Dim varA, varB, varC, varD, varE, varF, varG, varH, varI, varJ, varK, varL, varM, varN As String
Dim i, lastcell As Integer
lastcell = Range("N1").End(xlDown).Row
'Exportation du tableau vers Access
Set TWB = ThisWorkbook.Sheets("Holdings")
Set Db = DAO.OpenDatabase("C:\Table.mdb", False, False)
For i = 2 To lastcell
varA = TWB.Range("A" & i): varB = TWB.Range("B" & i)
varC = TWB.Range("C" & i): varD = TWB.Range("D" & i)
varE = TWB.Range("E" & i): varF = TWB.Range("F" & i)
varG = TWB.Range("G" & i): varH = TWB.Range("H" & i)
varI = TWB.Range("I" & i): varJ = TWB.Range("J" & i)
varK = TWB.Range("K" & i): varL = TWB.Range("L" & i)
varM = TWB.Range("M" & i): varN = TWB.Range("N" & i)
strSQL = "INSERT INTO [EASY_Temp_Import] VALUES('" & varA & "','" & varB & "','" & varC & _
"','" & varD & "','" & varE & "','" & varF & "','" & varG & "','" & varH & "','" & varI & _
"','" & varJ & "','" & varK & "','" & varL & "','" & varM & "','" & varN & "')"
Db.Execute strSQL
Next
Db.Close
'Vide le tableau et ferme le fichier
ActiveSheet.Range(Cells(2, 1), Cells(lastcell, 14)).ClearContents
MsgBox "La table Acess Easy_Temp_Import a été mise à jour!"
ActiveWorkbook.Close SaveChanges:=False
End Sub |
Partager