hi.
I have 400 excel files with xlsb format.can anyone help me to write VBA to change all files formats in one specific folder with their own names?
change 400 files from xlsb to xlsx
-
????706 -
July 29, 2018 at 2:11 PM -
Thread is marked as Resolved.
-
-
-
Thanks to AlphaFrog at MrExcel for this:
Code
Display MoreDim strSourceFile As String Dim strSourceDirectory As String Dim counter As Long strSourceDirectory = "C:\S30\TR\" '[COLOR=#FF0000]Change this to reflect your own actual Path[/COLOR] strSourceFile = Dir(strSourceDirectory & "*.xlsb") Do While strSourceFile <> "" Name strSourceDirectory & strSourceFile As strSourceDirectory & Replace$(strSourceFile, ".xlsb", ".xlsx") counter = counter + 1 strSourceFile = Dir() Loop MsgBox counter & " files renamed.", , "Rename Files Complete"
-
Shouldn't be too difficult, not in atm but have a look at this
http://%22https//www.thespread…given-folder%22
Will just need to modify the save/close part
Edit: ignore this and see above, even better
-
Thanks to AlphaFrog at MrExcel for this:
Code
Display MoreDim strSourceFile As String Dim strSourceDirectory As String Dim counter As Long strSourceDirectory = "C:\S30\TR\" '[COLOR=#FF0000]Change this to reflect your own actual Path[/COLOR] strSourceFile = Dir(strSourceDirectory & "*.xlsb") Do While strSourceFile <> "" Name strSourceDirectory & strSourceFile As strSourceDirectory & Replace$(strSourceFile, ".xlsb", ".xlsx") counter = counter + 1 strSourceFile = Dir() Loop MsgBox counter & " files renamed.", , "Rename Files Complete"
its works but after that the problem is the files can not open any more.
we saved these files in xlsb format and now we want to saved them in xlsx format
i think we should write a vba to open each file and save it as xlsb formats.
[ATTACH=JSON]{"alt":"Click image for larger version Name:\terorr.jpg Views:\t1 Size:\t15.3 KB ID:\t1206184","data-align":"none","data-attachmentid":"1206184","data-size":"full","title":"erorr.jpg"}[/ATTACH] -
its works but after that the problem is the files can not open any more.
we saved these files in xlsb format and now we want to saved them in xlsx format
i think we should write a vba to open each file and save it as xlsb formats.
[ATTACH=JSON]{"alt":"Click image for larger version Name:\terorr.jpg Views:\t1 Size:\t15.3 KB ID:\t1206184","data-align":"none","data-attachmentid":"1206184","data-size":"full","title":"erorr.jpg"}[/ATTACH]this works for me:
Code
Display MoreSub LoopAllExcelFilesInFolder() 'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them 'SOURCE: www.TheSpreadsheetGuru.com Dim wb As Workbook Dim myPath As String Dim myFile As String Dim myExtension As String Dim FldrPicker As FileDialog Dim fpath As String Dim wname As String 'Optimize Macro Speed Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual 'Retrieve Target Folder Path From User Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker) With FldrPicker .Title = "Select A Target Folder" .AllowMultiSelect = False If .Show <> -1 Then GoTo NextCode myPath = .SelectedItems(1) & "\" End With 'In Case of Cancel NextCode: myPath = myPath If myPath = "" Then GoTo ResetSettings 'Target File Extension (must include wildcard "*") myExtension = "*.xls*" 'Target Path with Ending Extention myFile = Dir(myPath & myExtension) 'Loop through each Excel file in folder Do While myFile <> "" 'Set variable equal to opened workbook Set wb = Workbooks.Open(Filename:=myPath & myFile) wname = Left(wb.Name, InStr(wb.Name, ".") - 1) fpath = wb.Path & "\" & wname 'Ensure Workbook has opened before moving on to next line of code DoEvents 'Save and Close Workbook wb.SaveAs Filename:=fpath & ".xlsb", FileFormat:= _ xlExcel12, CreateBackup:=False wb.Close SaveChanges:=True 'Ensure Workbook has closed before moving on to next line of code DoEvents 'Get next file name myFile = Dir Loop 'Message Box when tasks are completed MsgBox "Task Complete!" ResetSettings: 'Reset Macro Optimization Settings Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
place in a different folder to the files you want to change
run it then select folder -
-
i attached one of my 400 files.i removed some sheets to reduce size.
your vba works but saved as xlsb. i need to change them to xlsx. -
-
And change xlExcel12 to xlOpenXMLWorkbook
And add: application.displayalerts = false to the beginning,or the message will pop up about saving macro free for every file. Set to true at end of code
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!