If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Merge multiple worksheets from Contributor

Started by Jetsfan420, 14 Feb 2011 03:28:13 PM

Previous topic - Next topic

Jetsfan420

I have a model that uses multiple tabs in Contributor Planning.. and creates about 8 different Excel Workbooks.  I'd like to merge all of them into one workbook.

I tried using this VBA macro in excel

QuoteSub CombineWorkbooks()
    Dim FilesToOpen
    Dim x As Integer

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Microsoft Excel Files (*.*), *.*", _
      MultiSelect:=True, Title:="Files to Merge")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    While x <= UBound(FilesToOpen)
        Workbooks.Open Filename:=FilesToOpen(x)
        Sheets().Move After:=ActiveWorkbook.Sheets _
          (ActiveWorkbook.Sheets.Count)
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

But I get an error saying no worksheets are available when I chose the workbooks created by Contributor.  Does anyone else know a solution to merging multiple reports together?