3 Replies Latest reply: May 15, 2014 10:04 AM by Dave-G _ RSS

    Excel Database with multiple sheets

    Dave-G _

      I am trying to take 20 sheets from a workbook and create there own individual workbook without doing manual selection for each sheet through Monarch database. Any suggestions?

        • Excel Database with multiple sheets
          Data Kruncher

          Assuming that your initial workbook has only the 20 sheets that you want to spin off to individual files, you can use this Excel macro to do the work for you.

           

          Sub NewBookPerSheet()

              Dim w As Worksheet

              Dim wks As Worksheet

              Dim b As Workbook

              Const SFOLDER = "C:\Test\"

              Dim sKeep As String

              Dim iCount As Integer

               

              Application.ScreenUpdating = False

               

              For Each w In Worksheets

                  Application.Workbooks.Add

                  Set b = ActiveWorkbook

                  ThisWorkbook.Activate

                  w.Copy After:=b.Worksheets(b.Worksheets.Count)

                  sKeep = ActiveSheet.Name

                  iCount = b.Worksheets.Count

                  On Error Resume Next

                  For Each wks In Worksheets

                      If wks.Name <> sKeep Then

                          With Application

                              .DisplayAlerts = False

                              wks.Delete

                              .DisplayAlerts = True

                          End With

                      End If

                  Next wks

                  On Error GoTo 0

                  b.SaveAs SFOLDER & b.Sheets(1).Name

                  b.Close SaveChanges:=False

                  Set b = Nothing

                  DoEvents

              Next w

              Application.ScreenUpdating = True

              MsgBox "All worksheets have been saved as individual files."

          End Sub

          /codeJust change the SFOLDER value beforehand as necessary.

           

          HTH,

          Kruncher

          • Excel Database with multiple sheets
            Olly Bond

            (Just seen Kruncher's post - that seems a lot easier than doing this job in Monarch!)

             

            Hello Dave,

             

            This sounds like something that wouldn't be too tricky to crack without using Monarch at all but using Visual Basic instead. But if you'd rather not have any code lying around then you might be able to solve the problem in Monarch fine.

             

            You mention that you don't want to do any manual work - from that, would it be right to assume that the input file will be arriving regularly, and that ideally, you just want to click on a button and create 20 files, correctly named?

             

            Do all the sheets in the workbook have the same fields (columns)?

             

            Assuming the file arrives each week, will all the sheets have consistent names?

             

            If the answers to these are yes, you're in business. Create 20 projects that read in each sheet, export the unfiltered table to an Access database, appending to the existing data.

             

            In each model, use the File() function to create a calculated field called Worksheet - complete formula Rsplit(File();2;" - ";1)

             

            In a final project, open the complete dataset and either create 20 filters for the different values of this field, and export all filters, or use this field as a hidden key and export a summary that contains the data you need, splitting on key value changes.

             

            Use the Batch File Generator from www.********************[/url] to automate the 21 steps, and yes, you can create your spreadsheets via Monarch without programming.

             

            Hope this helps,

             

            Olly