I am trying to convert a lot of old .xls files to .xlsx format AND changing ppi settings at the same time. Is this possible with some kind of script or macro? Changing the ppi, in a workbook full of pictures, from 220ppi to 96ppi reduces the size.
I found this page, but am wondering if it is still valid for Office 2013/2016 components and how to update it with possible ppi changes.
The below gives me a compile error stating "User-defined type not defined" where it says Dim FSO As Scripting.FileSystemObject
Option Explicit
' Convert all xls files in selected folder to xlsx
Sub convertXLStoXLSX()
Dim FSO As Scripting.FileSystemObject
Dim strConversionPath As String
Dim fFile As File
Dim fFolder As Folder
Dim wkbConvert As Workbook
' Open dialog and select folder
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
strConversionPath = .SelectedItems(1)
End With
Set FSO = New Scripting.FileSystemObject
' Check if the folder exists
If FSO.FolderExists(strConversionPath) Then
Set fFolder = FSO.GetFolder(strConversionPath)
' Loop through files, find the .xls files
For Each fFile In fFolder.Files
If Right(fFile.Name, 4) = ".xls" Or Right(fFile.Name, 4) = ".XLS" Then
Application.DisplayAlerts = False
Set wkbConvert = Workbooks.Open(fFile.Path)
' Save as XML workbook - if file contains macros change FileFormat:=52
wkbConvert.SaveAs FSO.BuildPath(fFile.ParentFolder, Left(fFile.Name, Len(fFile.Name) - 4)) & ".xlsx", FileFormat:=51
wkbConvert.Close SaveChanges:=False
' Delete original file
fFile.Delete Force:=True
Application.DisplayAlerts = True
End If
Next fFile
End If
End Sub
