MS Excel : Split multiple sheets of One Workbook into Separate Workbooks

In continuation to my previous post on Split data into different sheets based on a column , we will now see on how to split the Workbook with multiple sheets into separate workbooks with sheet names as workbook names.

One we have completed the data split as per my previous post, we are left with a workbook with multiple sheets , each sheet will have data related to a USER and the sheet names are the respective USER_ID's.

Now save the workbook with some name and open the macro editor.

1. Press ALT+F11 , to open macro editor
2. Under Insert , Click on Module
3. Please paste the below code in the module 
4. Press F5 to run

Sub Splitbook()

Dim xPath As String

xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False

Application.DisplayAlerts = False

For Each xWs In ThisWorkbook.Sheets

    xWs.Copy

    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"

    Application.ActiveWorkbook.Close False

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

This code will split the sheets of one workbook into multiple workbooks with sheet name as workbook name in the same folder of the original workbook.

MS Excel - Split data from one sheet to different sheets based on a column

Recently we have had a small issue with the BI Publisher and we had to run a report manually and implement the Bursting technique somehow to split the data generated by the Data Model ,so that we can email the report to the respective Users.

We ran the SQL used in the data model against the EBS  and was able to get the data for all the users.Now the primary agenda here is to split the data on user basis and send them their data respectively.

Lets say my Excel Workbook has one sheet "Sheet1" and has the below columns

USER_ID 

USER_NAME

USER_EMAIL

USER_CITY

USER_COUNTRY

CALENDAR_DATE

USER_ACTIVITY

We have two steps here 

1. Split the data from Sheet1 into multiple sheets based on USER_ID in the same workbook
2. Split the created sheets of workbook into separate Excel workbooks

This can be achieved  by using macros. Lets see how the first step can be done.

• Open the Excel Workbook, Press [ALT+F11] which will open Macro editor
• Under Insert tab, Click on Module
• Paste the below code in the Module window.
• Make sure the highlighted columns are edited as per your requirement.

"vcol " should be column number based on which the split will happen

"Set ws = Sheets("Sheet1")" is sheetname in which the data is available  

title = "A1:G1"  is the headings - my case it's 7 so G.

• Once the code edit is done , Press F5

Macro to be used

Sub parse_data()

Dim lr As Long

Dim ws As Worksheet

Dim vcol, i As Integer

Dim icol As Long

Dim myarr As Variant

Dim title As String

Dim titlerow As Integer

vcol = 1        

Set ws = Sheets("Sheet1")        

lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row

title = "A1:G1"           

titlerow = ws.Range(title).Cells(1).Row

icol = ws.Columns.Count

ws.Cells(1, icol) = "Unique"

For i = 2 To lr

On Error Resume Next

If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then

ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)

End If

Next

myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))

ws.Columns(icol).Clear

For i = 2 To UBound(myarr)

ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""

If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then

Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""

Else

Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)

End If

ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")

Sheets(myarr(i) & "").Columns.AutoFit

Next

ws.AutoFilterMode = False

ws.Activate

End Sub

For splitting the multiple sheets into separate Workbooks please refer here

Unable to start bi_server1 after changing the listening port to 80

We recently tried changing the listener port of the analytics web application from 9704 to 80 using the WebLogic Administration Console. When we tried the restart of bi_server1, the BI Services now fail to start successfully.

When we tried the log files to see what is causing the issue , we found the below error.

After some investigation we found the issue was caused because we didn't perform the system reboot.On the whole below are the steps performed to change the port.

Port Change in Console

Change the Analytics port number from 9704 to 80 following the instructions given in the Configure Listen Ports section of the Oracle Fusion Middleware Oracle WebLogic Server Administration Console Online Help 11g Release 1 (10.3.3) Part Number E13952-03.

Here are the steps:

• Log in to WebLogic console: http://servername:7001/console.

• Go to Environment -> Servers. Click on Lock and Edit button -> Configuration tab -> click on bi_server1 link -> set Listen Port: to 80 -> click on Save.
• Click on Activate Changes button.

Stop the instance:

• Stop the OBIEE components from command prompt:

[FMW_HOME]\instances\instance1\bin\opmnctl stopall

• Stop the Managed server from GUI. Access the weblogic console from a browser (replace "hostname" by the machine name):

http://[hostname]:7001/console

• Log in, Environment -> Servers -> Control -> select bi_server1 -> click on Stop. It will take some time for the Managed server to stop.

• Stop the Admin server from GUI. Access weblogic console from a browser (replace "hostname" by the machine name):

http://[hostname]:7001/console

• Log in, Environment -> Servers -> Control -> select AdminServer(admin) -> click on Stop. It will take some time for the Admin server to stop.

• Stop the Node Manager. Go to Windows Services and stop "Oracle WebLogic Node Manager".

• Reboot the machine. This is required. If you attempt to restart the BI instance without rebooting the machine, it will fail.

Start the instance:

• Start NodeManager. Go to Windows Services and start "Oracle WebLogic Node Manager".

• Start the Admin server from command prompt:

[FMW_HOME]\user_projects\domains\bifoundation_domain\bin\startWebLogic.cmd

It will ask you for the user name and password. Specify the user details that you have mentioned at the time of the 11g install.

Wait until you see a message that the Admin server is in running mode.

• Start the Managed server from GUI. Access weblogic console from a browser (replace "hostname" by the machine name):

http://[hostname]:7001/console

• Log in, Environment -> Servers -> Control -> select bi_server1 -> click on start

It will take some time for the Managed server to start. Wait until you see Running status.

• Start the OBIEE components from command prompt:

[FMW_HOME]\instances\instance1\bin\opmnctl startall

Note: You many want to change the port number in the start up scripts provided when OBIEE is installed. Those scripts are static and port number needs to be updated manually there.