Macro to convert Xls file format to Xlsx
Hi Friends!!
I am searching for the macro which can convert all the Xls file to Xlsx from a specified Folder. However i am unable to find the correct macro..
i have written a below code but don't know whats wrong.. Can someone help me on this please?
Please Login or Register to view this content.
You can't just change the name; well, maybe you can, but it won't change the file structure ... and, if it does work, Excel will probably complain that the file doesn't have the expected file structure.
You need to use SaveAs and use the new file name and choose the appropriate file type. After you have converted and checked the files, you can delete or (better) archive the original files.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks for that.. But i have Thousands of file thats why i needed a macro...It will consume much time if i do manually..
Are you sure that its not possible??
Thanks
You misunderstand. You can do exactly what you are doing but, instead of just trying to change the file extension, you need to use SaveAs to change the name AND the file structure/type (to .xlsx)
Regards, TMS
Hey meus
I think I have what you're looking for. I thought that was a good idea & have a use for it myself.
This code will change from one file format to another.
There's a fuller explanation in the attached workbook
I'd like to say it took me 5 minutes, but I'd be lying
Have fun
Please Login or Register to view this content.
I've improved the previous code a little bit by adding a DoEvents. My computer was not responding every now & then & the DoEvents has speeded things up.
I got the counter to count the right amount of files processed & if there's an error during one of the conversions, the message box tells you the name of the file that caused it.
TMS: Thanks for the rep & the comment
Please Login or Register to view this content.
Hey!! thanks Philb1
Added Rep
can you explain me little bit about the following?
thanksPlease Login or Register to view this content.
Hi meus.. Thanks for the rep.
The case function is used to find the file format number of the file extension you want to convert to. In your case as you're converting to xlsx, it will find 51. So the variable Num has a value of 51
Thenwill save the workbook to the destination folder with a xlsx file extensionPlease Login or Register to view this content.
You can save to any of the file formats. Have a play
Cheers
Phil
Hey Phil, one more thing...
It works perfectly great in my computer, excel 2013..
But in my friends computer, i tried with excel 2007.. the file is converted but with no extension...
What can be the problem?
Thanks,...
I'm using Excel 2010 & it works fine here too. As for 2007 I don't know & I don't know anyone who has it so I can try to replicate what's happening.
I've been excelling for about 3 years & I've never used an older version of Excel. Maybe someone who has 2007 can help.
Sorry I can't be of more help
Thank you Phil...
Really appretiate your help...
Is your 2007 machine set up to not show file extensions?
No... it shows up the file extensions...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks