To Increase the width of the columns in a sheet for multiple files in a folder using VBA

JBMS · Apr 9th 2021

Hi Team,

I need help To Increase the width of the columns in a sheet for multiple files in a folder using VBA.

**Carim** · Apr 9th 2021

Hello and Welcome to the Forum

You do need a macro to loop through your files ... see link :

https://www.thespreadsheetguru…l-files-in-a-given-folder

Hope this will help

**royUK** · Apr 9th 2021

Try this. The code assumes each workbook has one sheet.

Code

Sub LoopFiles()
    Dim oWb As Workbook
    Dim sFldr As String, sFilName As String
    Dim fDialog As Object
    
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    If fDialog.Show = -1 Then
        sFldr = fDialog.SelectedItems(1)
        Else: MsgBox "User cancelled selection"
        Exit Sub
    End If
    
    sFilName = Dir(sFldr & "\*.xls*")
    Do While sFilName > ""
        Set oWb = Workbooks.Open(sFldr & "\" & sFilName)
        oWb.Sheet(1).Cells.Select
        Selection.ColumnWidth = 10
        oWb.Close SaveChanges:=False ''///Close opened worbook w/o saving, change as needed
        sFilName = Dir()
    Loop
    
    MsgBox "All files updated", vbInformation, "Success"
    
End Sub

Display More

**royUK** · Apr 9th 2021

If you want to adjust multiple sheets within each workbook use

Code

Sheets.Select
Selection.ColumnWidth = 10

Code

Sub LoopFiles()
    Dim oWb As Workbook
    Dim sFldr As String, sFilName As String
    Dim fDialog As Object
    
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    If fDialog.Show = -1 Then
        sFldr = fDialog.SelectedItems(1)
        Else: MsgBox "User cancelled selection"
        Exit Sub
    End If
    
    sFilName = Dir(sFldr & "\*.xls*")
    Do While sFilName > ""
        Set oWb = Workbooks.Open(sFldr & "\" & sFilName)
        oWb.Sheets.Select
'        oWb.Sheet(1).Cells.Select
        Selection.ColumnWidth = 10
        oWb.Close SaveChanges:=False ''///Close opened worbook w/o saving, change as needed
        sFilName = Dir()
    Loop
    
    MsgBox "All files updated", vbInformation, "Success"
    
End Sub

Display More

JBMS · Apr 9th 2021

Hi,

Thank you for your help.

**royUK** · Apr 9th 2021

Pleased to help.

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

To Increase the width of the columns in a sheet for multiple files in a folder using VBA

Participate now!

Share