Open every file in the folder, remove merge and wrap, save and close files

Hi,

I would appreciate it if someone could help me with this problem.

- I have about 300 files in a folder. Each of excel 2003 format.
- I would like to open each file
- remove wrap and merge from each cell
- close and save that file
- then repeat the cycle till every file in the folder is done

I am pretty new to VBA and have been trying this for the past few days using articles online but with no luck.

I have come up with the following code:

Sub test()

Dim sThisFilePath As String
Dim sFile As String
Dim wbBook As Workbook

sThisFilePath = ActiveWorkbook.Path
If (Right(sThisFilePath, 1) <> "\") Then sThisFilePath = sThisFilePath & "\"

sFile = Dir(sThisFilePath & "*.xls*")
MsgBox "The path is " & sThisFilePath
ChDir (sThisFilePath)
Do While sFile <> vbNullString
MsgBox "The next file is " & sFile
'if the next file is same as "active file" then skip the file
'If (sFile = ActiveWorkbook.Name) Then GoTo Next_File

Set wbBook = Workbooks.Open (sThisFilePath & sFile)

activeworksheet.Cells.UnMerge

wbBook.Close SaveChanges:=True

Next_File:
sFile = Dir
Loop
Set wbBook = Nothing

End Sub

At Set wbBook = Workbooks.Open (sThisFilePath & sFile). I get error 1004 method open of object workbooks failed.

Please advise me on what needs to be done. I appreciate your time and effort.

Thank you.

Please Login or Register  to view this content.

Thank you so much!! works perfectly.