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
|
Function fichier_NS()
Dim xls As Excel.Application
On Error GoTo errHnd
Dim iStr_Repertoire As String
iStr_Repertoire = Nz(DLookup("[chemin]", "Param"), "")
If iStr_Repertoire = "" Then
MsgBox "Répertoire non défini."
GoTo fin
End If
Set xls = Nothing
Set xls = CreateObject("Excel.Application")
xls.Workbooks.Open Filename:=iStr_Repertoire & "fichier-BIS-For creation - No Soft.xls"
xls.Visible = True
xls.Rows("1:1").Select
xls.Selection.Delete Shift:=xlUp
xls.ActiveWorkbook.SaveAs Filename:=iStr_Repertoire & "IB - no soft.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
xls.Workbooks.Open Filename:=iStr_Repertoire & "fichier-For creation - No Soft.xls"
xls.Visible = True
xls.Windows.Arrange ArrangeStyle:=xlHorizontal
xls.Windows("IB - no soft.xls").Activate
xls.Range("A1").Select
xls.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
xls.Selection.Copy
xls.Windows("fichier-For creation - No Soft.xls").Activate
xls.Range("A1").Select
xls.Selection.End(xlDown).Select
xls.ActiveCell.Offset(1, 0).Activate
xls.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
xls.Windows("fichier-For creation - No Soft.xls").Activate
Call Flash_clip
xls.Windows("IB - no soft.xls").Activate
xls.ActiveWindow.Close savechanges:=False
xls.Windows("fichier-For creation - No Soft.xls").Activate
xls.ActiveWindow.WindowState = xlMaximized
xls.Columns("J:K").Select
xls.Selection.NumberFormat = "m/d/yyyy"
xls.Cells.Select
xls.Cells.EntireColumn.AutoFit
xls.Range("B4").Select
xls.ActiveWorkbook.SaveAs Filename:=iStr_Repertoire & "Fichiers fichier No Soft.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
GoTo fin
errHnd:
MsgBox "Erreur N° " & Err.Number & vbLf & Err.Description, , Err.Source
fin:
xls.Quit
Set xls = Nothing
End Function |
Partager