Bonjour Paritec et à tous
je vogue d'erreur en erreur, et là je viens de faire de plus amples recherches sur Internet et j'ai trouvé un anglo saxon qui dit que:
"In Excel 2011 we needed AppleScript to save as PDF, we not need that in Excel 2016 anymore.
But there is still work to do in 2016, the two examples below are working on this moment, will update the code if there are changes"
Sur excel 2011 il fallait un applescript, ce qui n'est plus le cas en Excel 2016. Mais que apparemment c'est pas 100% non plus la syntaxe de vba windows. En lisant cela c'était un peu l'épiphanie, car j'avais fait un export a PDF qui fonctionnait sur 2011 et effectivement ne fonctionne plus sur 2016 for Mac.
Cependant j'ai récupéré le code qu'il propose (voir ci-dessous) et je ne comprends pas comment je peux modifier le répertoire d'accès qu'il propose au répertoire du fichier Excel.
Sauriez vous m'aider?
Merci d'avance!!
Sub PublishEachWorkSheetToPDF()
'Ron de Bruin : 19-Nov-2016
'Test macro to publish each worksheet to pdf with ExportAsFixedFormat
'It will create a new folder for you with the files
Dim Fstr As String
Dim FolderName As String
Dim Folderstring As String
Dim sh As Worksheet
Dim FilePathName As String
Dim TestStr As String
Dim ASheet As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Remember the active worksheet
Set ASheet = ActiveSheet
'Name of the Root folder in the Office folder, and create the folder
FolderName = "PDFFolder"
Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
'Create folder in the Root folder with the name of the ActiveWorkbook
Fstr = Mid(ActiveWorkbook.Name, 1, InStrRev(ActiveWorkbook.Name, ".", , 1) - 1) & Format(Now, " dd-mmm-yyyy hh-mm-ss")
On Error Resume Next
TestStr = Dir(Folderstring & "/" & Fstr, vbDirectory)
On Error GoTo 0
If TestStr = vbNullString Then MkDir Folderstring & "/" & Fstr
'Loop through all worksheets
For Each sh In ActiveWorkbook.Worksheets
'If the sheet is visible then publish it to PDF
If sh.Visible = -1 Then
sh.Select
'My example sheet is landscape, I must attach this line
'for making the PDF also landscape, seems to default to
'xlPortait the first time you run the code
ActiveSheet.PageSetup.Orientation = xlLandscape
'File name is the sheet name and a date/time stamp
FileName = sh.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"
'Publish the Worksheet to pdf
FilePathName = Folderstring & "/" & Fstr & Application.PathSeparator & FileName
'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
'Not working if you change activeworkbook, always save the activesheet
'Also the parameters are not working like in Windows
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
FilePathName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False
End If
Next sh
ASheet.Select
MsgBox "You find the PDF files in this location : " & Folderstring & "/" & Fstr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub