VBA to Open Xlsx/Xlsm/Xls excel files and Convert to another format

[Hollando]

Hello all,

I need help with this build .

Basically I want to be able to create a path within the code to search this folder for excel files with Xlsx/Xlsm/Xls, open, save as, same name, but new extension. In this case I am converting older files to Xlsb in order to save space.

There are three main environments I will need to do this in

1. A Users Desktop
2. A Users Documents
3. Sharedrive Folder

As always any suggestions would be greatly appreciated. Thank you in advance.

Regards,

Hollando

 

[JLGWhiz]

The path is the critical part of this endeavor. The path takes you to the directory and then the rest is basic. Where you have more than one path, you could list the patn names in a column and then loop down the column to work them one at a time. Assume they are in column A of sheet 1.

For each c In Sheets(1).Range("A2:A4")
    fPath = c,Value
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    fName = Dir(fPath & "*.xl*)
    Do While fName <> ""
         Set wb = Worksbooks.Open(fName)
         SaveAs wb.Name & ".xlsb"  
         wb.Close False
         fName = Dir
    Loop
Next

 

[Hollando]

The path is the critical part of this endeavor. The path takes you to the directory and then the rest is basic. Where you have more than one path, you could list the patn names in a column and then loop down the column to work them one at a time. Assume they are in column A of sheet 1.

For each c In Sheets(1).Range("A2:A4")
    fPath = c,Value
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    fName = Dir(fPath & "*.xl*)
    Do While fName <> ""
         Set wb = Worksbooks.Open(fName)
         SaveAs wb.Name & ".xlsb"  
         wb.Close False
         fName = Dir
    Loop
Next

Thanks for the quick response JLG.

I'm a little perplexed as to how this works.

What is the FPath = c, Value saying?

To use one path as an example could we use the desktop?

 

[JLGWhiz]

Basically I want to be able to create a path within the code

The lower case 'c' is a variable representing a cell object, in the case, From A2 thru A4 where it was assumed the file paths would have been listed for the various sources to find the files you want to open. The statemehnt fPath = c.Value is to assign the value of the cell in Column A to a variable with a name that identifies what it represents. c.Value could just as easily been used everywhere that fPath appears, but it might make it confusing to read the code if debugging is required. What the snippet that I posted does Is:
1. Creates a For...Next loop to walk down the designated cells in column A.
2. Assigns the current cell in Col A to the fPath variable
3. Checks for valid attenuation symbol in path and inserts one if needed.
4. Uses Dir function to assugn a file name to the fName variable
5. Starts Do Loop to
...a. Open a workbook with the path and file name previously identified (Notice that I forgot to inclued path here)
...b. Save the file with new extension
...c. Close the workbook that was just saved.
...d. get new file name for fName variable
6. Loop until all files in directory have been worked.
7. Loop to the next directory in column A.
Here is the corrected snippet:

For each c In Sheets(1).Range("A2:A4")
    fPath = c,Value
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    fName = Dir(fPath & "*.xl*)
    Do While fName <> ""
         Set wb = Worksbooks.Open(fPath & fName)
         SaveAs wb.Name & ".xlsb"  
         wb.Close False
         fName = Dir
    Loop
Next

 

[Hollando]

The lower case 'c' is a variable representing a cell object, in the case, From A2 thru A4 where it was assumed the file paths would have been listed for the various sources to find the files you want to open. The statemehnt fPath = c.Value is to assign the value of the cell in Column A to a variable with a name that identifies what it represents. c.Value could just as easily been used everywhere that fPath appears, but it might make it confusing to read the code if debugging is required. What the snippet that I posted does Is:
1. Creates a For...Next loop to walk down the designated cells in column A.
2. Assigns the current cell in Col A to the fPath variable
3. Checks for valid attenuation symbol in path and inserts one if needed.
4. Uses Dir function to assugn a file name to the fName variable
5. Starts Do Loop to
...a. Open a workbook with the path and file name previously identified (Notice that I forgot to inclued path here)
...b. Save the file with new extension
...c. Close the workbook that was just saved.
...d. get new file name for fName variable
6. Loop until all files in directory have been worked.
7. Loop to the next directory in column A.
Here is the corrected snippet:

For each c In Sheets(1).Range("A2:A4")
    fPath = c,Value
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    fName = Dir(fPath & "*.xl*)
    Do While fName <> ""
         Set wb = Worksbooks.Open(fPath & fName)
         SaveAs wb.Name & ".xlsb"  
         wb.Close False
         fName = Dir
    Loop
Next

Thanks again for the reply JLG. I tried simply entering a path is cell A2:

C:\Users\hollando\Documents\Test

Also, when I placed the code within the VBA Project there were two areas identified as a Syntax Error.

Sub Test()
For Each c In Sheets(1).Range("A2:A4")
    [COLOR=#ff0000]fPath = c,Value[/COLOR]
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    [COLOR=#ff0000]fName = Dir(fPath & "*.xl*)[/COLOR]
    Do While fName <> ""
         Set wb = Worksbooks.Open(fPath & fName)
         SaveAs wb.Name & ".xlsb"
         wb.Close False
         fName = Dir
    Loop
Next
End Sub

Was there something I needed to change with thes two key areas?

 

[Andrew Poulsom]

In the first one replace the comma with a dot. In the second one insert a double quote before the closing parenthesis.

 

[Hollando]

In the first one replace the comma with a dot. In the second one insert a double quote before the closing parenthesis.

Thanks Andrew!

One extra issue now.

I get a Compile error: 'Sub or Function not defined' this appears to stem from the

SaveAs wb.Name & ".xlsb"

 

[Andrew Poulsom]

That should be:

wb.SaveAs wb.Name & ".xlsb"

 

[Hollando]

That should be:

wb.SaveAs wb.Name & ".xlsb"

Excellent. We're getting close.

Run-time error '1004':

This extension can not be used with the selected file type. Change the file extension in the file name text box or select a different file type by change the Save as type.

 

[Andrew Poulsom]

Try:

wb.SaveAs wb.Name & ".xlsb", FileFormat:=50