Wednesday, October 17, 2007

We have been using XLS file for managing localization string in our projects. With some custom macro, we have generated resource files from worksheets (which corresponded to each form in our ASP.NET application). Quite comfortable for translators, but when using in distributed environment with more programmers sharing the work through SVN repos, conflicts often appeared.

So we need to split this Excel workbook into separate files (one for each worksheet), keeping macros intact.

Saving files according to sheets name was quite easy (found it here).

Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.PATH & "/" & W.Name
Next W
End Sub

Second problem was removing redundant worksheets from all files. So I have written something like this (must have all files opened).

Sub RemoveSheetsFromWB()
Dim W As Worksheet
Dim B As Workbook

For Each B In Workbooks
For Each W In B.Worksheets
If W.Name & ".xls" <> B.Name Then
Application.DisplayAlerts = False
W.Delete
Application.DisplayAlerts = True
End If
If B.Worksheets.Count = 1 Then Exit For
Next W
Next B
End Sub