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>
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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
 

alogoc

New Member
Joined
Sep 25, 2009
Messages
8
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...
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Do you want to choose it each time you run the macro or just specify it in the code?

Dom
 

alogoc

New Member
Joined
Sep 25, 2009
Messages
8

ADVERTISEMENT

Specify everytime i run the code would be the best!

Thanks in advance!
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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
 

alogoc

New Member
Joined
Sep 25, 2009
Messages
8
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:

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,190
Messages
5,594,761
Members
413,931
Latest member
acrato

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
Top