{"id":380,"date":"2022-12-06T07:17:38","date_gmt":"2022-12-06T06:17:38","guid":{"rendered":"http:\/\/lesmrdtrucs.mataf35400.fr\/?page_id=380"},"modified":"2022-12-06T08:28:35","modified_gmt":"2022-12-06T07:28:35","slug":"excel","status":"publish","type":"page","link":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/excel\/","title":{"rendered":"Excel"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Une appli de VbFrance qui peut montrer pas mal de trucs:
https:\/\/codes-sources.commentcamarche.net\/source\/8501-generateur-de-calendriers-excel-sous-vb-6<\/a><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

R\u00e9f\u00e9rence \u00e0 un fichier Excel<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Ajouter la r\u00e9f\u00e9rence \u00e0 Excel:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Mettre dans les d\u00e9clarations du module (ou de la feuille en tant que Dim)
Global MonAppWorld As Excel.Application<\/em><\/span>
Global MonWbWorld As Excel.Workbook<\/em><\/span>
Global MesFeuillesXls(1 To 20) As String<\/em><\/span><\/p>

Ouvrir Excel:
Sub XlsSetup()<\/em><\/span>
\u00a0\u00a0 Dim MaXlsFeuille As Excel.Worksheet<\/em><\/span>
\u00a0\u00a0 \u00a0On Error Resume Next ‘ignore errors<\/em><\/span>
\u00a0\u00a0 \u00a0Set MonAppWorld = GetObject(, \u00ab\u00a0Excel.Application\u00a0\u00bb) ‘rechercher une copie d’Excel en cours<\/span><\/em><\/span>
\u00a0\u00a0 \u00a0If Err Then ‘Si Excel n’est pas en cours<\/span><\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Err.Clear<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Set MonAppWorld = CreateObject(\u00ab\u00a0Excel.Application\u00a0\u00bb) ‘le lancer<\/span><\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0If Err Then MsgBox \u00ab\u00a0Erreur XlsSetup, CreateObject ‘Excel.application’\u00a0 \u00a0\u00bb & Error$<\/em><\/span>
\u00a0\u00a0 \u00a0End If\u00a0\u00a0 \u00a0<\/em><\/span><\/p>

\u00a0\u00a0 \u00a0Err.Clear\u00a0\u00a0 ‘ Effacer l’objet Err en cas d’erreur.<\/span><\/em><\/span>
\u00a0\u00a0 \u00a0Set MonWbWorld = MonAppWorld.Workbooks.Open(MonFicXls)<\/em><\/span>
\u00a0\u00a0 \u00a0If Err Then MsgBox \u00a0\u00bb Erreur XlsSetup, Open \u00a0\u00bb & MonFicXls & \u00a0\u00bb\u00a0\u00a0 \u00a0\u00bb & Error$\u00a0\u00a0 \u00a0<\/em><\/span><\/p>

\u00a0\u00a0 \u00a0Err.Clear<\/em><\/span>
\u00a0\u00a0 \u00a0‘ Parcourt la collection de feuilles et ajoute<\/em><\/span>
\u00a0\u00a0 \u00a0‘ le nom de chaque feuille dans la liste d\u00e9roulante.<\/em><\/span>
\u00a0\u00a0 \u00a0For Each MaXlsFeuille In MonWbWorld.Sheets<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Ii = Ii + 1<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0MesFeuillesXls(Ii) = MaXlsFeuille.Name<\/em><\/span>
\u00a0\u00a0 \u00a0Next MaXlsFeuille<\/em><\/span>
\u00a0\u00a0 \u00a0On Error GoTo 0<\/em><\/span>
\u00a0\u00a0 \u00a0Set MaXlsFeuille = Nothing<\/em><\/span>
End Sub<\/em><\/span><\/p>

Faire le traitement:
Sub R\u00e9cup\u00e8reProdDuJour(ByVal MaDate As String, MaMachine As String)<\/em><\/span>
\u00a0\u00a0 \u00a0Dim MaXlsFeuille As Excel.Worksheet<\/em><\/span>
\u00a0\u00a0 \u00a0Dim MonXlsChamp As Excel.Range<\/em><\/span>
\u00a0\u00a0 \u00a0Dim MaLigne As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0‘Ligne de Excel dans laquelle on \u00e9crit<\/span><\/em><\/span>
\u00a0\u00a0 \u00a0Dim MaColonne As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0‘Colonne de Excel dans laquelle on \u00e9crit<\/span><\/em><\/span>
\u00a0\u00a0 \u00a0Dim NbJours As Integer, TmpMois As String<\/em><\/span><\/p>

\u00a0\u00a0 \u00a0MaDate = Left$(MaDate, 2)\u00a0\u00a0 \u00a0<\/em><\/span><\/p>

\u00a0\u00a0 \u00a0If MonAppWorld Is Nothing Then XlsSetup<\/em><\/span><\/p>

\u00a0\u00a0 \u00a0‘————————————————————————————————————-<\/span><\/em><\/span>
\u00a0\u00a0 \u00a0Err.clear: On Error Resume Next<\/em><\/span>
\u00a0\u00a0 \u00a0Set MaXlsFeuille = MonWbWorld.Sheets(MaMachine)<\/em><\/span>
\u00a0\u00a0 \u00a0If Err Then MsgBox \u00ab\u00a0Erreur dans SauveSurExcel, Set MaXlsFeuille\u00a0\u00a0 \u00a0\u00bb & Error$<\/em><\/span>
\u00a0\u00a0 \u00a0On Error GoTo 0\u00a0\u00a0 \u00a0<\/em><\/span><\/p>

\u00a0\u00a0 \u00a0Set MonXlsChamp = MaXlsFeuille.Rows(1)\u00a0\u00a0 \u00a0<\/em><\/span><\/p>

\u00a0\u00a0 \u00a0‘Recherche de la colonne Excel qui nous interesse (en fonction de la date \u00e0 lire)<\/em><\/span>
\u00a0\u00a0 \u00a0MaColonne = 0<\/em><\/span>
\u00a0\u00a0 \u00a0Dim MaCase<\/em><\/span>
\u00a0\u00a0 \u00a0For j = 3 To 33<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Tmp = MonXlsChamp.Cells(4, j)<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0If Right$(Format$(Tmp, \u00ab\u00a000\u00a0\u00bb), 2) = MaDate Then<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0MaColonne = j<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0For MaLigne = 5 To 50<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0If MonXlsChamp.Cells(MaLigne, 1) = \u00ab\u00a0\u00a0\u00bb Then Exit For<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0If Val(MonXlsChamp.Cells(MaLigne, MaColonne)) > 0 Then<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0macouleur = MonXlsChamp.Cells(MaLigne, MaColonne).Interior.Color<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0End If<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Next MaLigne<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Exit For<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0End If<\/em><\/span>
\u00a0\u00a0 \u00a0Next j\u00a0\u00a0 \u00a0<\/em><\/span><\/p>

\u00a0\u00a0 \u00a0‘————————————————————————————————————-<\/span><\/em><\/span>
\u00a0\u00a0 \u00a0‘ Nettoie.<\/em><\/span>
\u00a0\u00a0 \u00a0Set MaXlsFeuille = Nothing<\/em><\/span>
\u00a0\u00a0 \u00a0Set MonXlsChamp = Nothing<\/em><\/span>
End Sub<\/em><\/span><\/p>

Fermer Excel:
Sub XlsClose()<\/em><\/span>
\u00a0\u00a0 \u00a0‘ Ceci doit forcer un d\u00e9chargement de Microsoft Excel,<\/em><\/span>
\u00a0\u00a0 \u00a0‘ \u00e0 conditon qu’aucune autre application ou aucun autre utilisateur ne l’ait charg\u00e9.<\/em><\/span>
\u00a0\u00a0\u00a0 Err.Clear: On Error Resume Next<\/em><\/span>
\u00a0\u00a0 \u00a0MonWbWorld.Close True<\/em><\/span>
\u00a0\u00a0 \u00a0Set MonWbWorld = Nothing<\/em><\/span>
\u00a0\u00a0 \u00a0Set MonAppWorld = Nothing<\/em><\/span>
\u00a0\u00a0 \u00a0On Error GoTo 0<\/em><\/span>
End Sub<\/em> <\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Copie d'une portion de grille dans le presse-papier<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Private Sub Grid_KeyPress(KeyAscii As Integer)<\/em><\/span>
\u00a0\u00a0 \u00a0If KeyAscii = 3 Then ‘Touche <Ctrl C><\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Dim Sep As String, Tmp As String<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Clipboard.Clear\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0<\/em><\/span>

\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0‘————————-Header de la grille<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0For j = Grid.Col To Grid.ColSel<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Tmp = Tmp & Sep & Grid.TextMatrix(0, j)<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Sep = vbTab<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Next j<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Sep = vbCrLf\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0<\/em><\/span>

\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0‘————————-Partie de la grille qui est s\u00e9lectionn\u00e9e<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0For i = Grid.Row To Grid.RowSel<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0For j = Grid.Col To Grid.ColSel<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Tmp = Tmp & Sep & Grid.TextMatrix(i, j)<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Sep = vbTab<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Next j<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0If Tmp <> \u00ab\u00a0\u00a0\u00bb Then Tmp = Tmp & vbCrLf<\/em><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Sep = \u00ab\u00a0\u00a0\u00bb<\/em><\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Next i<\/em><\/span>
\u00a0\u00a0 \u00a0End If<\/em><\/span>
\u00a0\u00a0 \u00a0Clipboard.SetText Tmp<\/em><\/span>
End Sub<\/em> <\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Conversion d'une table Access en feuille Excel<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Cet exemple, pomp\u00e9 en 10 min d’apr\u00e8s le prog d’arrachement, est brut et sert \u00e0 cr\u00e9er un fichier Excel \u00ab\u00a0Etiq.xls\u00a0\u00bb comportant une feuille \u00ab\u00a0Etiquette\u00a0\u00bb contenant tous les enregistrements de la table \u00ab\u00a0Etiquette\u00a0\u00bb de la Bdd \u00ab\u00a0Etiq.mdb\u00a0\u00bb.

Attention: pour que \u00e7a fonctionne, il faut que la feuille \u00ab\u00a0Etiquette\u00a0\u00bb du fichier Excel n’existe pas.

Dans la zone d\u00e9claration d’une feuille (ou d’un module)

Dim Ws As Workspace<\/em><\/span>

Dans la proc\u00e9dure d’un contr\u00f4le (par exemple)

Private Sub Command1_Click()<\/em><\/span>
\u00a0\u00a0 \u00a0Dim Bdd As String<\/em><\/span>
\u00a0\u00a0 \u00a0Dim Xls As String<\/em><\/span>
\u00a0\u00a0 \u00a0Bdd = \u00ab\u00a0D:\\documents and Settings\\ebap188\\My Documents\\etiq.mdb\u00a0\u00bb<\/em><\/span>
\u00a0\u00a0 \u00a0Xls = Replace(Bdd, \u00ab\u00a0.mdb\u00a0\u00bb, \u00ab\u00a0.xls\u00a0\u00bb)\u00a0\u00a0 \u00a0<\/em><\/span>

\u00a0\u00a0 \u00a0Dim DataBdd As Database<\/em><\/span>
\u00a0\u00a0 \u00a0Dim DataXls As Database\u00a0\u00a0 \u00a0<\/em><\/span>

\u00a0\u00a0 \u00a0Set Ws = Workspaces(0)<\/em><\/span>
\u00a0\u00a0 \u00a0Set DataBdd = Ws.OpenDatabase(Bdd)<\/em><\/span>
\u00a0\u00a0 \u00a0Set DataXls = Ws.OpenDatabase(Xls, 0, 0, \u00ab\u00a0Excel 8.0;\u00a0\u00bb)\u00a0\u00a0 \u00a0<\/em><\/span>

\u00a0\u00a0\u00a0 ‘DataBdd.Execute \u00ab\u00a0SELECT * into [Excel 8.0;database=\u00a0\u00bb & Xls & \u00ab\u00a0].Etiquette FROM Etiquette WHERE [Specif 1] = ’11KW\/400V'\u00a0\u00bb<\/em><\/span>
\u00a0\u00a0 \u00a0 DataBdd.Execute \u00ab\u00a0SELECT * into [Excel 8.0;database=\u00a0\u00bb & Xls & \u00ab\u00a0].Etiquette FROM Etiquette\u00a0\u00bb<\/em><\/span>
End Sub<\/em><\/span>

Si l’erreur Isam appara\u00eet, c’est qu’il faut dans l’install Office cocher le truc qui va bien dans les donn\u00e9es (de m\u00e9moire. Faut fouiller pour plus de d\u00e9tail). <\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Quelques commandes en VBA<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t


(r\u00e9cup\u00e9r\u00e9es de: https:\/\/codes-sources.commentcamarche.net\/faq\/484-vba-excel-aide-aux-debutants-quelques-morceaux-de-codes<\/a> –>Voir les commentaires)<\/p>

Les diff\u00e9rentes m\u00e9thodes d\u00e9crites par la suite ne sont \u00e9videmment pas les seules possibles, ni m\u00eame les meilleures, mais elles fonctionnent.\u00a0\u00a0 \u00a0<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

S\u00e9lection d'une feuille<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 Sheets(\u00ab\u00a0NomDeLaFeuille\u00a0\u00bb).Select<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

S\u00e9lection d'une cellule<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 Range(\u00ab\u00a0A1\u00a0\u00bb).Select<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Ouverture d'un autre fichier Excel<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 ChDir \u00ab\u00a0Chemin du r\u00e9pertoire du fichier\u00a0\u00bb\u00a0\u00a0 \u00a0
\u00a0\u00a0\u00a0 Workbooks.Open Filename:= \u00a0\u00bb Chemin du r\u00e9pertoire du fichier\\fichier.xls \u00ab\u00a0
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Fermeture d'un fichier<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 ActiveWindow.Close ‘pour fermer le fichier en cours<\/span>
<\/em><\/span>Ou
\u00a0\u00a0\u00a0 Workbooks.Close Filename:= \u00a0\u00bb Chemin du r\u00e9pertoire du fichier\\fichier.xls \u00ab\u00a0

<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Parcourir les lignes d'un fichier<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 i = 1\u00a0\u00a0 \u00a0
\u00a0\u00a0 \u00a0Sheets(\u00ab\u00a0Base\u00a0\u00bb).Select\u00a0\u00a0 \u00a0
\u00a0\u00a0 \u00a0While Not Range(\u00ab\u00a0A\u00a0\u00bb & i).Value = \u00ab\u00a0\u00a0\u00bb\u00a0\u00a0 \u00a0
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 i = i + 1\u00a0\u00a0 \u00a0
\u00a0\u00a0 \u00a0Wend
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Parcourir les colonnes<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 While Cells(1, i) <> \u00ab\u00a0\u00a0\u00bb\u00a0\u00a0 \u00a0
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i = i + 1\u00a0\u00a0 \u00a0
\u00a0\u00a0\u00a0 Wend
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Trouver la lettre associ\u00e9e au chiffre pour la colonne<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 NumCol = Cells(1, i).Column\u00a0\u00a0\u00a0
\u00a0\u00a0\u00a0 lettre = IIf(NumCol > 26, Chr(64 + NumCol \\ 26) & Chr(64 + NumCol Mod 26), Chr(64 + NumCol))
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Tirer les formules<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 Range(\u00ab\u00a0A3:Z3\u00a0\u00bb).Select ‘s\u00e9lection de la ligne contenant la formule<\/span>\u00a0\u00a0 \u00a0
\u00a0\u00a0 \u00a0Selection.AutoFill Destination:=Range(\u00ab\u00a0A3:Z\u00a0\u00bb & i – 1), Type:=xlFillDefault\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0Range(\u00ab\u00a0A3:Z\u00a0\u00bb & i – 1).Select
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Adapter un graphique<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 ActiveSheet.ChartObjects(\u00ab\u00a0Graphique 3\u00a0\u00bb).Activate\u00a0\u00a0 \u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(3).Select\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(3).XValues = \u00ab\u00a0=’% Nom classeur?!R4C1:R\u00a0\u00bb & i & \u00ab\u00a0C1\u00a0\u00bb\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(1).XValues = \u00ab\u00a0=’% Nom classeur?!R4C1:R\u00a0\u00bb & i & \u00ab\u00a0C1\u00a0\u00bb\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(2).XValues = \u00ab\u00a0=’% Nom classeur?!R4C1:R\u00a0\u00bb & i & \u00ab\u00a0C1\u00a0\u00bb\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(3).Values = \u00ab\u00a0=’% Nom classeur?!R4C5:R\u00a0\u00bb & i & \u00ab\u00a0C5\u00a0\u00bb\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(2).Select\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(2).Values = \u00ab\u00a0=’% Nom classeur?!R4C7:R\u00a0\u00bb & i & \u00ab\u00a0C7\u00a0\u00bb\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(1).Select\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0ActiveChart.SeriesCollection(1).Values = \u00ab\u00a0=’% Nom classeur?!R4C3:R\u00a0\u00bb & i & \u00ab\u00a0C3\u00a0\u00bb
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Adapter la taille des cellules<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0 \u00a0 ‘redimensionnement des cellules<\/span>
\u00a0\u00a0 \u00a0Rows(\u00ab\u00a02:\u00a0\u00bb & i).Select\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0Selection.RowHeight = 25\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0Range(\u00ab\u00a0A1:H1\u00a0\u00bb).Select
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Changer les bordures<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 ‘on s’occupe des bordures du tableau
\u00a0\u00a0\u00a0 Range(\u00ab\u00a0B3:H\u00a0\u00bb & i).Select\u00a0\u00a0 \u00a0<\/span>
\u00a0\u00a0 \u00a0Selection.Borders(xlDiagonalDown).LineStyle = xlNone\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0Selection.Borders(xlDiagonalUp).LineStyle = xlNone\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0With Selection.Borders(xlEdgeLeft)\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .LineStyle = xlContinuous\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .Weight = xlMedium\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .ColorIndex = xlAutomatic\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0End With\u00a0\u00a0\u00a0 <\/span>

\u00a0\u00a0 \u00a0With Selection.Borders(xlEdgeTop)\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .LineStyle = xlContinuous\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .Weight = xlMedium\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .ColorIndex = xlAutomatic\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0End With\u00a0\u00a0\u00a0 <\/span>

\u00a0\u00a0 \u00a0With Selection.Borders(xlEdgeBottom)\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .LineStyle = xlContinuous\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .Weight = xlMedium\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .ColorIndex = xlAutomatic\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0End With\u00a0\u00a0\u00a0 <\/span>

\u00a0\u00a0 \u00a0With Selection.Borders(xlEdgeRight)\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .LineStyle = xlContinuous\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .Weight = xlMedium\u00a0\u00a0\u00a0 <\/span>
\u00a0 \u00a0\u00a0\u00a0\u00a0 .ColorIndex = xlAutomatic\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0End With\u00a0\u00a0\u00a0 <\/span>

\u00a0\u00a0 \u00a0With Selection.Borders(xlInsideVertical)\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .LineStyle = xlContinuous\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .Weight = xlMedium\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .ColorIndex = xlAutomatic\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0End With\u00a0\u00a0\u00a0 <\/span>

\u00a0\u00a0 \u00a0With Selection.Borders(xlInsideHorizontal)\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .LineStyle = xlContinuous\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .Weight = xlMedium\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 .ColorIndex = xlAutomatic\u00a0\u00a0\u00a0 <\/span>
\u00a0\u00a0 \u00a0End With<\/span><\/span>
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

R\u00e9cup\u00e9rer une valeur sur une autre feuille<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 nb = Range(\u00ab\u00a0B\u00a0\u00bb & i).Value\u00a0\u00a0 \u00a0
\u00a0\u00a0\u00a0 Sheets(\u00ab\u00a0nb rebut\u00a0\u00bb).Select\u00a0\u00a0 \u00a0
\u00a0\u00a0\u00a0 Range(\u00ab\u00a0C\u00a0\u00bb & j).Value = nb
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Ins\u00e9rer ou effacer une ligne<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 Rows(\u00ab\u00a04:4\u00a0\u00bb).Select\u00a0\u00a0 \u00a0
\u00a0\u00a0 \u00a0Selection.Delete Shift:=xlUp ‘ pour supprimer\u00a0\u00a0\u00a0<\/span>
\u00a0\u00a0 \u00a0Rows(\u00ab\u00a05:5\u00a0\u00bb).Select\u00a0\u00a0\u00a0
\u00a0\u00a0 \u00a0Selection.Insert Shift:=xlDown ‘ pour ins\u00e9rer<\/span>
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Afficher le nom de l'utilisateur tel qu'il apparaitra en tant qu'auteur (Ex: MRD)<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\u00a0\u00a0\u00a0 Function TrouveUtilisateur()
\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 TrouveUtilisateur = Application.UserName
\u00a0\u00a0\u00a0 End Function
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Afficher le nom de l'utilisateur de la session Windows (Ex: SESA8675)<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

    Public Function UserName()
        UserName = Environ$(\u00ab\u00a0UserName\u00a0\u00bb)
    End Function
<\/em><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

Une appli de VbFrance qui peut montrer pas mal de trucs:https:\/\/codes-sources.commentcamarche.net\/source\/8501-generateur-de-calendriers-excel-sous-vb-6 R\u00e9f\u00e9rence \u00e0 un fichier Excel Ajouter la r\u00e9f\u00e9rence \u00e0 Excel: Mettre dans les d\u00e9clarations du module (ou de la feuille en tant que Dim)Global MonAppWorld As Excel.ApplicationGlobal MonWbWorld As Excel.WorkbookGlobal MesFeuillesXls(1 To 20) As String Ouvrir Excel:Sub XlsSetup()\u00a0\u00a0 Dim MaXlsFeuille As Excel.Worksheet\u00a0\u00a0 \u00a0On Error […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"ocean_post_layout":"","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"","ocean_second_sidebar":"","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"","ocean_custom_header_template":"","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"","ocean_menu_typo_font_family":"","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"","footnotes":""},"class_list":["post-380","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/pages\/380","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/comments?post=380"}],"version-history":[{"count":13,"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/pages\/380\/revisions"}],"predecessor-version":[{"id":421,"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/pages\/380\/revisions\/421"}],"wp:attachment":[{"href":"https:\/\/lesmrdtrucs.mataf35400.fr\/index.php\/wp-json\/wp\/v2\/media?parent=380"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}