Save all open workbooks as xls

alogoc

New Member
Joined
Sep 25, 2009
Messages
8
Hello

I am using a macro to open .txts in excel in seperate workbooks.
I am looking for a macro to save all open workbooks in .xls

I use the following macro from microsoft but it only saves them in their original form (.txt)

Code:
Sub SaveAll()
    ' Store the Activeworkbook in a variable.
    Set aw = ActiveWorkbook
        For Each wb In Workbooks
            If wb.Path <> "" Then ' Save file if it has been saved
                                  ' previously
                wb.Save
            Else  ' If not previously saved, activate and show the
                  ' Save As dialog box.
                wb.Activate
                Application.Dialogs(xlDialogSaveAs).Show
            End If
        Next
    aw.Activate   ' Activate the original Activeworkbook.
End Sub
</pre>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Try:

Code:
Sub SaveTxt()
    
Dim wb As Workbook
    For Each wb In Workbooks
        
        If Right(wb.Name, 4) = ".txt" Then
        
            wb.SaveAs ThisWorkbook.Path & Left(wb.Name, Len(wb.Name) - 4) _
                & ".xls", FileFormat:=xlNormal
        
        End If
    Next
    
End Sub

Dom
 
Upvote 0
Hey!Thanks for your reply!It's working but it is possible to be able to choose the path?

Because isaves them in dekstop and they are hunderds of files...
 
Upvote 0
Do you want to choose it each time you run the macro or just specify it in the code?

Dom
 
Upvote 0
Try:

Code:
Sub SaveTxt()
    
Dim wb As Workbook
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With

For Each wb In Workbooks
        
    If Right(wb.Name, 4) = ".txt" Then
        
        wb.SaveAs ThisWorkbook.Path & Left(wb.Name, Len(wb.Name) - 4) _
            & ".xls", FileFormat:=xlNormal
        
    End If
Next
    
NextCode:
Set fldr = Nothing
   
End Sub

Dom
 
Upvote 0
Hey there
It's still saving them on the desktop... :eek:


I was wrong!You just have to put the full path!Thanks a lot!!!
 
Last edited:
Upvote 0
You were right I didn't amend the code properly:

Code:
Sub SaveTxt()
    
Dim wb As Workbook
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With

For Each wb In Workbooks
        
    If Right(wb.Name, 4) = ".txt" Then
        
        wb.SaveAs sItem & "\" & Left(wb.Name, Len(wb.Name) - 4) _
            & ".xls", FileFormat:=xlNormal
        
    End If
Next
    
NextCode:
Set fldr = Nothing
   
End Sub

Dom
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top