PDA

View Full Version : use VBA to covert .xls to .xlsx



north123
08-14-2011, 08:17 AM
I have around 150 excel files in old format ".xls", I want to convert these .xls files to .xlsx format with VBA. Anybody could you please help, is there a way to do this in VBA without opening the old files then saving them as .xlsx? Because in BVA using codes for opening and saving take quite a long time. It would be great if someone konws how to do this without actually opening the old files. Many thanks!

Krishna Kumar
08-14-2011, 09:20 AM
Hi,

Sub kTest()

Dim fName As String
Dim MyFolder As String

MyFolder = "C:\Test\" '<<== adjust to suit

If Right$(MyFolder, 1) <> "\" Then MyFolder = MyFolder & "\"

fName = Dir(MyFolder & "*.xls")

Do While Len(fName)
Name MyFolder & fName As MyFolder & Replace(fName, ".xls", ".xlsx", , , 1)
fName = Dir()
Loop

End Sub

Bob Phillips
08-14-2011, 11:01 AM
What happens if they have macros?

Krishna Kumar
08-14-2011, 11:26 PM
Since the OP wants it to be converted as xlsx, I guess the xls files have no macros.

Bob Phillips
08-15-2011, 01:16 AM
If he understands that nuance of 2007/2010. Worth asking.