How to formatting multiple excel files??

Custom / N/A
October 26, 2009 at 20:14:57
Specs: Windows XP, 1024
I just wondering that are there anyway to format multiple excel files at once? I don't mine using 3rd party software or some vba script.

See More: How to formatting multiple excel files??

Report •

October 26, 2009 at 20:34:14

Are all the workbooks identical in structure, number of worksheets, worksheet names and the addresses of cells to be formatted?

How do you specify what formatting is to be applied? Is the formatting information in a text file, are you copying formatting from an existing file, or are you hoping to format on the fly, and if so how.


Report •

October 26, 2009 at 20:40:04
Yes. 100% identical, but only with different names.
I only want all the files have the same format and page setup.

Report •

October 27, 2009 at 07:40:40

Here is a macro that will copy formating from a master workbook to a series of other, named workbooks.

I haven't tested if page settings are copied with this, so more code may be needed to setup pages for printing. Anyway see if this works for the formatting.

As noted, all workbooks are identical (same worksheets in each).

The names are all hardcoded in this example code. The array holding the names will have to be re-sized appropriate to the number of files you have, only 4 in this example. I didn't use the zero element of the array : strWBNames(0).
Dim strWBNames(4)

The path to the files is hardcoded :
strPath = "C:\temp\"
Change as required - don't forget the closing "\"

The following code is entered in a standard module in the master workbook. Named "Copy_Master.xls" for this example.

Option Explicit
Public Sub WBFormat()

Dim strMasterFile As String
Dim strWBNames(4)
Dim strPath As String
Dim n As Integer
Dim objWS As Worksheet

On Error Resume Next

'set master file name
strMasterFile = "Copy_Master.xls"

'set path to files (note closing "\")
strPath = "C:\temp\"

'setup file names
strWBNames(1) = "Copy_1.xls"
strWBNames(2) = "Copy_2.xls"
strWBNames(3) = "Copy_3.xls"
strWBNames(4) = "Copy_4.xls"

'Copy formatting from each worksheet in Copy_Master.xls to
'each worksheet in the named workbooks
For n = 1 To 4
    Workbooks.Open (strPath & strWBNames(n))
    For Each objWS In Workbooks(strMasterFile).Worksheets
        Workbooks(strWBNames(n)).Worksheets(objWS.Name).Range("A1") _
        .PasteSpecial Paste:=xlPasteFormats
    Next objWS
Next n
End Sub

To enter the subroutine code:
From your workbook enter Alt + f11 (both the Alt key and the #11 function key at the same time), or Tools-Macro-Visual Basic editor.

This opens up the VBA Editor

In the left side Project Explorer pane, look for the name of your master workbook (all workbooks are preceded by VBAProject).

Right click on the name and select Insert.
Insert Module (not class module)
A new module (Module1) will be added.
Double click on it.

Now in the main window paste the code,
then Save the project (File - Save)

This code is pretty basic.

I didn't include any specific error checking, - just let it continue even if an error occurs. You could comment out the On Error Resume Next and the code should stop if an error occurs.

The reformatted files are saved, but not closed - you could add a close file to each loop once you have the whole thing up and running properly.

Finally link the subroutine code to a button on a toolbar to make it easy to run.
For testing just place cursor inside the code on the VBA page. Hit f8 to start single stepping and f5 to run.


Report •

Related Solutions

October 27, 2009 at 10:21:20

As I thought, page setup does not get copied with the PasteSpecial command.

If you add this code to the routine I posted before, you will be able to copy specific page setups. I just included headers and footers in the example.
You can see where this fits in the existing code from the 1st and last lines of the new code.

    For Each objWS In Workbooks(strMasterFile).Worksheets
        With Workbooks(strWBNames(n)).Worksheets(objWS.Name)
            .Range("A1").PasteSpecial Paste:=xlPasteFormats
            With .PageSetup
                .LeftHeader = objWS.PageSetup.LeftHeader
                .CenterHeader = objWS.PageSetup.CenterHeader
                .RightHeader = objWS.PageSetup.RightHeader
                .LeftFooter = objWS.PageSetup.LeftFooter
                .CenterFooter = objWS.PageSetup.CenterFooter
                .RightFooter = objWS.PageSetup.RightFooter
            End With
        End With
    Next objWS

If you record a macro when you setup page formating you will be able to look at the recorded macro and identify the formating items that you need to include, such as

The macro recording will include lots of default settings which you don't need to copy over.



Report •

Ask Question