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
| Option Compare Database
Public Enum eConstXls
xlPasteColumnWidths = 8
xlNone = -4142
End EnumPrivate
Sub Commande92_Click()
Dim vStatusBar As String
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "Mise en page des feuilles EXCEL ... veuillez patienter.")
Dim xlApp As Object '' Excel.Application
Dim xlSheet1 As Object ' Excel.Worksheet
Dim xlBook As Object ' Excel.Workbook
Dim sSheet As String, Rep1 As String, LaDate As String, MoisDate As String, StTarget As String, Sql1 As String
Dim thedb As DAO.Recordset
LaDate = Now()
MoisDate = Format(LaDate, "ddmm")
Rep1 = "F:\PELO\PELO 2018-2019\FichiersInscriptionParent\"
Sql1 = "SELECT DISTINCT tblEcole.Abvr, tblEcole.NomEcole FROM tblEcole;"
StTarget = Rep1 & "EcolePELO" & "_" & MoisDate & ".xlsm"
StTarget = "E:\Users\rdurupt.GENARISGROUP\Desktop\Classeur1.xls"
Set xlBook = GetObject(StTarget)
'filename is the string with the link to the file ("C:/....blahblah.xls")
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
End If
'Make sure excel is visible on the screen
xlApp.Visible = True
xlBook.Windows(1).Visible = True
'xl.ActiveWindow.Zoom = 75
sSheet = thedb(0)
'Define the sheet in the Workbook as XlSheet1
Set xlSheet1 = xlBook.Worksheets(1)
With xlSheet1
.Name = "Modele"
.Select ' "Modele"
.Rows("1:2").Select
.copy
.Name = sSheet
.Select sSheet
.Rows("1:1").Select
''''
' Bloc ici
''''
.Rows("1:1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False
.Range("B3").Select
.Application.CutCopyMode = False
.Selection.copy
.Range("B1:N1").Select
.ActiveSheet.Paste
.Range("A3:B266").Select
.Application.CutCopyMode = False
.App
End With
xlSheet1.aActiveSheet.Sheet (1)
' xlApp.ActiveSheet.Name = "Modele"
xlSheet1.Sheets("Modele").Select
xlSheet1.Application.ActiveWindow.SelectedSheets.Delete , True
xlBook.Save , True
xlBook.Close
xlApp.Quit
Err_MCommande92_Click:
vStatusBar = SysCmd(acSysCmdClearStatus)
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Commande92_Click
Exit_Commande92_Click:
vStatusBar = SysCmd(acSysCmdClearStatus)
End Sub |
Partager