-1

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
MicLima
  • 1
  • 3
  • Did you try the file? Asking if it is still valid will take a lot more time than just grabbing the file and trying it on a few of your documents. If you're worried about damage, copy the files to a test directory. Also, are you sure you mean PPI, or do you perhaps mean DPI? – music2myear May 17 '17 at 19:16
  • Yes, I get compile and syntax errors on both posted macros. Under File>Options>Advanced>ImageSizeAndQuality>SetDefaultTargetOutputTo: I would like to be able to set this to 96ppi while it's going through the conversion process. – MicLima May 17 '17 at 19:50
  • 1
    PPI is Pixels Per Inch, which is a measure of screen scaling, which does not really apply to Excel files in a meaningful way. DPI is Dots Per Inch, which is a measure of print image density which can be bound to some file types. Also, if those answers don't answer your question, then edit your question to include the errors and your specific needs that differ to make it more clear that this isn't a duplicate. The edit button is very helpful that way. – music2myear May 17 '17 at 22:18

1 Answers1

0

Change the code

wkbConvert.SaveAs FSO.BuildPath(fFile.ParentFolder, Left(fFile.Name, Len(fFile.Name) - 4)) & ".xlsx", FileFormat:=51

By

wkbConvert.SaveAs Replace(ThisWorkbook.FullName, ".xls", ".xlsx"), FileFormat:=xlExcel12
Edu Garcia
  • 107
  • 4