Removing unwanted spaces in A1 in lots of diff workbooks 1

[eHanSolo]

Hi all,

I'm really stuck with this one... As the title suggests, I have a directly with a whole host of excel files(with diff file names). Most of them have spaces in the first cell(a1) of which i want removed. It would take me decades to do this manually...can anyone offer their help in the form of a macro for this? Even a little sympathy wouldn't go amiss.. *sob sob*

Thanks...

 

[xlbo]

Why not try something and post back when you get stuck. Look in the VBA help for file OPEN method, loops and the RANGE object and its properties - you will also need to look at the INSTR function to determine where the spaces are

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[tb]

You could also try the
Sheet1.Cells(RowIndex,ColumnIndex)
With the TRIM$ to get rid of the trailing spaces only


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[DrBowes]

This should help, change "mypath" to point to the directory. Not tested so it might need some tweaks.

Sub RemSpacesinA1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim openwb As Workbook
Dim ws As Worksheet
Dim mypath As String
Dim ssf As Boolean
mypath = "C:\"
With Application.FileSearch
  .NewSearch
  .LookIn = mypath
  .SearchSubFolders = false  
  .FileType = msoFileTypeExcelWorkbooks
End With
With Application.FileSearch
If .Execute() > 0 Then
    For i = 1 To .FoundFiles.Count
        x = .FoundFiles(i)
           Set openwb = Workbooks.Open(x, False, False)
                For Each ws In Worksheets
                   ws.Cells(1, 1).Value = Application.WorksheetFunction.Substitute(ws.Cells(1, 1).Value, " ", "")
                Next ws
            openwb.Close True
            Set openwb = Nothing
    Next i
End If
End With
End Sub

 

[eHanSolo]

Thanks for all your help guys! Hope you all had a nice weekend!

 

[eHanSolo]

Hey Doc... thanks for the code...it actually works really well Only problem is that it's really really slow!!

Can it be possible to speed things up a bit???

Thanks.

 

[DrBowes]

I think it is the opening, closing and saving of workbooks that takes the time, and I don't know if this can be sped up. Would be interested to know if anyone has a way...

 

[GrayShades]

Just a note on the code:

You have these two lines:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

but at the end you didn't include the reverse

Application.ScreenUpdating = True
Application.DisplayAlerts = True


Software: XL2002 on Win2K
Humanware: Older than dirt