Open Excel Wildcard and Save As XLSX

Hints, tips and tricks for newbies

Moderators: Dorian (MJT support), JRL

Post Reply
MacroRookie
Newbie
Posts: 1
Joined: Wed Aug 03, 2022 9:18 pm

Open Excel Wildcard and Save As XLSX

Post by MacroRookie » Wed Aug 03, 2022 9:22 pm

Hi friends, new user of Macro Scheduler here.

I am looking for a way to open excel files with a XLSB extension in a folder and save them as XLSX.

I tried using XLOpen with a wildcard * but no luck.

Any tips would be appreciated!
Cheers.

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1083
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Open Excel Wildcard and Save As XLSX

Post by Dorian (MJT support) » Thu Aug 04, 2022 9:43 am

We can get the list of files with GetFileList. Then we loop through them, ExtractFileName will extract the file name without extension so that when we save we can replace with XLSX. XLSave will allow you to change the format - To save to a different format (.e.g. to CSV or a different version of Excel) set the optional fileformat value to an xlFileFormat enumerator.

You may need or want to change the timing with some Waits, but the following worked for me as is :

Code: Select all

//Set the paths. All xlsb files are in d:\XLSB and all xlsx files will be saved to d:\XLSX 
Let>FilePathIn=d:\XLSB
Let>FilePathOut=d:\XLSX

//Get the list of xlsb files
GetFileList>%FilePathIn%\*.xlsb,files
Separate>files,;,file_names

//Loop through the files.  Open, Save as xlsx, quit 
Let>k=0
Repeat>k
  Let>k=k+1

//Open xlsb file
XLOpen>file_names_%k%,1,xlh

//Get the file name without the extension
ExtractFileName>file_names_%k%,filename,1

//Save as xlsx
xlsave>xlh,%FilePathOut%\%filename%.xlsx,51

//Quit Excel
xlquit>xlh

wait>0.5
Until>k,file_names_count

Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts