PT Sterling Tulus Cemerlang
www.sterling-team.com

Phone
+62-877-8655-5055
+62 (21) 580-6055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower 18th
Jl Jend. Sudirman kav 45-46
Jakarta 12930 - Indonesia

350+ FREE Tips for you from Sterling Tulus Cemerlang (STEM) - SAP Indonesia Gold Partner, and Become SAP B1 Super User

Split Your Master Data Document for Specific Total Rows

SHARE ON

As a routine for go live preparation, you always have a lot of master data to uploaded on the production database. And when you’re using Data Transfer Workbench (DTW), sometimes it will be an error if you use files that have more than 10000 rows on one text files (.txt)

So, you need to split your excel files that contains you raw data.
We can use Macro feature on Microsoft Excel.

On View tab, click on icon Macros.

SAP_BusinessOne_Tips-STEM-Using Macro for Spliting Excel Documents_01

Then, input the Macros name, it’s up to you.

SAP_BusinessOne_Tips-STEM-Using Macro for Spliting Excel Documents_02

After that, just click OK button. Then, open your split query.txt, copy-paste your query.

Sub split()
Dim rLastCell As Range
    Dim rCells As Range
    Dim strName As String
    Dim lLoop As Long, lCopy As Long
    Dim wbNew As Workbook
    Dim rowloop As Long
    Dim sheetname As String

    rowloop = 4999
    sheetname = ThisWorkbook.Sheets(1).Name
    With ThisWorkbook.Sheets(1)
        Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
For lLoop = 2 To rLastCell.Row Step rowloop
            lCopy = lCopy + 1
            Set wbNew = Workbooks.Add
            wbNew.Sheets(1).Name = sheetname
            .Range(.Cells(1, 1), .Cells(1, .Columns.Count)).EntireRow.Copy _
            Destination:=wbNew.Sheets(1).Range("A1")

            .Range(.Cells(lLoop, 1), .Cells(lLoop + rowloop, .Columns.Count)).EntireRow.Copy _
            Destination:=wbNew.Sheets(1).Range("A2")
            wbNew.SaveAs Filename:=Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) & "_" & lCopy & ".xls", FileFormat:=56
            wbNew.Close SaveChanges:=False
         lLoop = lLoop + 1

     Next lLoop

    End With
End Sub

(Or, you can save as to .txt that query first, so you can use it later)

SAP_BusinessOne_Tips-STEM-Using Macro for Spliting Excel Documents_03

Open your excel document again, then press Alt + F8 to show a Macro menu.

SAP_BusinessOne_Tips-STEM-Using Macro for Spliting Excel Documents_04

Click Edit button, then paste your split query into the field.

SAP_BusinessOne_Tips-STEM-Using Macro for Spliting Excel Documents_05

Paste it to there, so it will look like this:

SAP_BusinessOne_Tips-STEM-Using Macro for Spliting Excel Documents_06

Set your own preferences for rowloop. On the picture above, we set a rowloop for 4999, so the final document will split into 5000 rows (max). You can change it by yourself.

Then, click Run icon (see picture below), and your document will be split into a separated file (base on your choice on rowloop).

SAP_BusinessOne_Tips-STEM-Using Macro for Spliting Excel Documents_07

And you can easily change the final document type, either it’s .doc (Ms. Word) or .txt (Text file). After that, you can click the Run button to execute this code for splitting the document again.

Change template to .txt (text file)

Finally, you can preview your split document on the same directory with your previous ‘raw’ data.

SAP_BusinessOne_Tips-STEM-Using Macro for Spliting Excel Documents_09

 

Run Your Business With The Piece of Mind Using SAP Business One

This article was written by PT. Sterling Tulus Cemerlang, SAP Business One GOLD Partner in Indonesia.
More details about SAP Business One,
email to: sales-enquiry@sterling-team.com or call +6221-5806336 or wa +6287786555055

, ,