Data Manipulation 1

Basic Efficiency Tool to Automate Your Data Manipulation Tasks

Delete

Clear_Worksheet

Clear_Worksheet

This command can be used to clear data and formatting features of all cells of a worksheet.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Clear_Worksheet  “C:\LB\Data.xlsx” “Sheet1” “Sheet2”

Target File Path

Define full folder path of an Excel Workbook
e.g. C:\LB\Data.xlsx

Target Worksheet

Specify worksheet name of the file
e.g. Sheet1

Target Worksheet (Optional)

Specify worksheet name of the file
e.g. Sheet2

Delete_Worksheet

Delete_Worksheet

This command can be used to delete worksheet(s) of all Excel Workbooks within a file folder.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Delete_Worksheet  “C:\LB\Data” “Sheet1” “Sheet2”

Target Folder Path

Specify full folder path which contains Excel Workbook(s)
e.g. C:\LB\Data

Target Worksheet

Specify a common worksheet name of the Excel Workbook(s)
e.g. Sheet1

Target Worksheet (Optional)

Specify another common worksheet name of the Excel Workbook(s)
e.g. Sheet2

Combine

Combine_Spreadsheet_by_Folder

Combine_Spreadsheet_by_Folder

This command can be used to combine a common worksheet for all Excel Workbooks within a file folder. All combined worksheets will be save as a new Excel Workbook.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Combine_Spreadsheet_by_Folder “C:\LB” “Input Form” “C:\LB\Combine Input Form.xlsx” “Y”

Source Folder Path

Specify a full folder path which contains Excel Workbooks
e.g. C:\LB

Source Common Worksheet

Specify a common worksheet of these Excel Workbooks
e.g. Input Form

Target File Path

Specify a full file path to save all combined worksheets
e.g. C:\LB\Combine Input Form.xlsx

Is Converting Formula to Value

Specify whether to convert all LedgerBase and Excel formulas to values
e.g. Y or N

Combine_Spreadsheet_by_Column_Header

Combine_Spreadsheet_by_Column_Header

This command can be used to combine a common worksheet for all Excel Workbooks in accordance with a list of full file paths. All combined worksheets will be save as a new Excel Workbook.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Combine_Spreadsheet_by_Column_Header “C:\LB\File List.xlsx” “List Worksheet” “List Column”  “Entry” “C:\LB\Combine Worksheet.xlsx” “Y”

Recurring Function by File List

Setting up of file list with this command can process a list of Excel Workbooks. The file list shall be configured in full file path format.

File Path of a File List

Specify a full file path which contains a file list
e.g. C:\LB\File List.xlsx

Worksheet of the File List

Specify a worksheet name which contains the file list
e.g. List Worksheet

Column Header of the File List

Specify a column header which contains the file list
e.g. List Column

“C:\LB\File List.xlsx” “List Worksheet” “List Column”

Source Common Worksheet

Specify a common worksheet of these Excel Workbooks
e.g. Entry

Target File Path

Specify a full file path to save all combined worksheets
e.g. C:\LB\Combine Worksheet.xlsx

Is Converting Formula to Value

Specify whether to convert all LedgerBase and Excel formulas to values
e.g. Y or N

“Entry”

“C:\LB\Combine Worksheet.xlsx” is a new file for generation.

Copy

Copy_Worksheet

Copy_Worksheet

This command can be used to copy a worksheet of an Excel Workbook to current file or a new file.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
e.g. Copy_Worksheet “C:\LB\Data.xlsx” “Sheet1” “Sheet2” “Y”

e.g. Copy_Worksheet “C:\LB\Data.xlsx” “Sheet1” “C:\LB\Data2.xlsx” “Sheet1” “Y”

Source File Path

Specify full file path of a source Excel Workbook
e.g. C:\LB\Data.xlsx

Source Worksheet

Specify a worksheet name of the file
e.g. Sheet1

Target Worksheet

Specify a worksheet name to insert a copied worksheet
e.g. Sheet2

Is Converting Formula to Value

Specify whether to convert all LedgerBase and Excel formulas to values
e.g. Y or N

Alternative Syntax

Source File Path

Specify full file path of a source Excel Workbook
e.g. C:\LB\Data.xlsx

Source Worksheet

Specify a worksheet name of the file
e.g. Sheet1

Target File Path

Specify full file path of a target Excel Workbook
e.g. C:\LB\Data2.xlsx

Target Worksheet

Specify a worksheet name to insert a copied worksheet
e.g. Sheet1

Is Converting Formula to Value

Specify whether to convert all LedgerBase and Excel formulas to values
e.g. Y or N

Copy_Worksheet_by_Column_Header

Copy_Worksheet_by_Column_Header

This command can be used to copy a common worksheet to target Excel Workbooks in accordance with a list of full file paths.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Copy_Worksheet_By_Column_Header “C:\LB\Sample.xlsx” “Cover” “C:\LB\File List.xlsx” “List Worksheet” “List Column” “Y”

Source File Path

Specify a full file path of an Excel Workbook which contains worksheet ready for copying
e.g. C:\LB\Sample.xlsx

Source Worksheet

Specify a worksheet ready for copying
e.g. Cover

File Path of a File List

Specify a full file path which contains a file list of Excel Workbooks to be inserted the copied worksheet
e.g. C:\LB\File List.xlsx

Worksheet of the File List

Specify a worksheet name which contains the file list
e.g. List Worksheet

Column Header of the File List

Specify a column header which contains the file list
e.g. List Column

Is Converting Formula to Value

Specify whether to convert all LedgerBase and Excel formulas to values
e.g. Y or N

Export

Export_Worksheet_List

This command can be used to mark whether required worksheets are exist based on a file list of workbooks.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Export_Worksheet_List  “C:\LB\Export_List.xlsx” “Check List of Data File” “Y” “BS” “P&L” “TB” “Fixed Asset”

Checklist File Path

Define full folder path
e.g. C:\LB\Data

Checklist Worksheet Name

Define Worksheet Name
e.g. Sheet1

Worksheet Name 2 (Optional)

Define Worksheet Name
e.g. Sheet2

Is Display Sort Worksheet

e.g. Y

Sort Worksheet Name

Define Worksheet Name
e.g. BS

Sort Worksheet Name (Optional)

Define Worksheet Name
e.g. P&L

Sort Worksheet Name (Optional)

Define Worksheet Name
e.g. TB

Sort Worksheet Name (Optional)

Define Worksheet Name
e.g. Fixed Asset

Insert

Insert_Column_Row

This command can be used to insert column or row of a worksheet.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters

e.g. Insert_Column_Row “C:\LB\Data.xlsx” “Sheet1” “Column(C8,8)

e.g. Insert_Column_Row “C:\LB\Data.xlsx” “Sheet1” “Row(C8,8)

Target File Path

Speacify full folder path of a target Excel Workbook
e.g. C:\LB\Data.xlsx

Target Worksheet

Specify a target worksheet
e.g. Sheet1

Insert (Column/Row) from a Cell Address

Specify an insert column or row from a cell address with inserting number of column or row
e.g. Column(C8,8) or Row(C8,8)

Move

Move_Worksheet

Move_Worksheet

This command can be used to move a worksheet within an Excel Workbook.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters

e.g. Move_Worksheet “C:\LB\Data.xlsx” “Sheet1” “L”

Note: Move worksheet “Sheet1” to last worksheet.

e.g. Move_Worksheet “C:\LB\Data.xlsx” “Sheet1” “Sheet3” “A”

Note: Move worksheet “Sheet1” after Sheet3.

Target File Path

Speacify full folder path of a target Excel Workbook for moving a worksheet
e.g. C:\LB\Data.xlsx

Target Worksheet

Specify a target worksheet to move
e.g. Sheet1

Move Option

Specify one of move options
e.g. L

F: Move to first worksheet

L: Move to last worksheet

 

Alternative Syntax

Target File Path

Speacify full folder path of a target Excel Workbook for moving a worksheet
e.g. C:\LB\Data.xlsx

Target Worksheet

Specify a target worksheet to move
e.g. Sheet1

Reference Worksheet

Specify a reference worksheet
e.g. Sheet3

Move Option

Specify one of move options
e.g. A

A: Move after a reference worksheet

B: Move before a reference worksheet

 

Move_Worksheet_to_Workbook

Move_Worksheet_to_Workbook

This command can be used to copy a worksheet from an Excel Workbook to another Excel Workbook.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters

e.g. Move_Worksheet_to_Workbook “C:\LB\Data.xlsx” “Note” “C:\LB\Report.xlsx”

Note: This command will not create any new file. If same worksheet name exist of the report.xlsx, worksheet name will be saved as Note(2).

Source File Path

Speacify full folder path of an Excel Workbook for copying a worksheet
e.g. C:\LB\Data.xlsx

Source Worksheet

Specify a worksheet of the file
e.g. Note

Target File Path

Specify full file path of an existing Excel Workbook for inserting the source worksheet
e.g. C:\LB\Report.xlsx

Protect

Set Edit Range

Set_Spreadsheet_Edit_Range

Set_Spreadsheet_Edit_Range

This command can be used to unlock cell ranges which can support data input despite worksheets are protected.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters

e.g. Set_Spreadsheet_Edit_Range “C:\LB\Invoice.xlsx” “Entry!E1:E11”

Target File Path

Speacify full file path of an Excel Workbook for unlocking cell range
e.g. C:\LB\Invoice.xlsx

Cell Range

Specify a cell range to support data input
e.g. Entry!E1:E11

Cell Range (Optional)

Specify another cell range to support data input
e.g. Entry!G1:G11

Set_Spreadsheet_Edit_Range_by_Column_Header

Set_Spreadsheet_Edit_Range_by_Column_Header

This command can be used to unlock cell ranges for all Excel Workbooks in accordance with a list of full file paths. All these workbooks can support data input despite worksheets are protected.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Set_Spreadsheet_Edit_Range_by_Column_Header  “C:\LB\File List.xlsx” “List Worksheet” “List Column” “C:\LB\Invoice.xlsx” “Entry!E1:E11”

File Path of a File List

Specify a full file path which contains a file list of Excel Workbooks
e.g. C:\LB\File List.xlsx

Worksheet of the File List

Specify a worksheet name which contains the file list
e.g. List Worksheet

Column Header of the File List

Specify a column header which contains the file list
e.g. List Column

Target File Path

Specify a full file path to set edit range for all files
e.g. C:\LB\Invoice.xlsx

Cell Range

Specify a cell range to support data input
e.g. Entry!E1:E11

Cell Range (Optional)

Specify another cell range to support data input
e.g. Entry!G1:G11

Set_Spreadsheet_Edit_Range_by_Folder

Set_Spreadsheet_Edit_Range_by_Folder

This command can be used to unlock cell ranges for all Excel Workbooks within a file folder. All these workbooks can support data input despite worksheets are protected.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Set_Spreadsheet_Edit_Range_by_Folder “C:\LB” “Entry!E1:E11”

Source Folder Path

Specify a full folder path which contains Excel Workbooks
e.g. C:\LB

Cell Address Range

Specify a cell range to support data input
e.g. Entry!E1:E11

Cell Address Range (Optional)

Specify another cell range to support data input
e.g. Entry!F1:F11

Clear Edit Range

Clear_Spreadsheet_Edit_Range

Clear_Spreadsheet_Edit_Range

This command can be used to lock cell ranges which cannot support data input if worksheets are protected.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters

e.g. Clear_Spreadsheet_Edit_Range “C:\LB\Invoice.xlsx” “Entry!E1:E11”

Target File Path

Speacify full file path of an Excel Workbook for unlocking cell range
e.g. C:\LB\Invoice.xlsx

Cell Range

Specify a cell range to support data input
e.g. Entry!E1:E11

Cell Range (Optional)

Specify another cell range to support data input
e.g. Entry!G1:G11

Clear_Spreadsheet_Edit_Range_by_Column_Header

Clear_Spreadsheet_Edit_Range_by_Column_Header

This command can be used to lock cell ranges for all Excel Workbooks in accordance with a list of full file paths. All these workbooks cannot support data input if worksheets are protected.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Clear_Spreadsheet_Edit_Range_by_Column_Header  “C:\LB\File List.xlsx” “List Worksheet” “List Column” “C:\LB\Invoice.xlsx” “Entry!E1:E11”

File Path of a File List

Specify a full file path which contains a file list of Excel Workbooks
e.g. C:\LB\File List.xlsx

Worksheet of the File List

Specify a worksheet name which contains the file list
e.g. List Worksheet

Column Header of the File List

Specify a column header which contains the file list
e.g. List Column

Target File Path

Specify a full file path to set edit range for all files
e.g. C:\LB\Invoice.xlsx

Cell Range

Specify a cell range to support data input
e.g. Entry!E1:E11

Cell Range (Optional)

Specify another cell range to support data input
e.g. Entry!G1:G11

Clear_Spreadsheet_Edit_Range_by_Folder

Clear_Spreadsheet_Edit_Range_by_Folder

This command can be used to lock cell ranges for all Excel Workbooks within a file folder. All these workbooks cannot support data input if worksheets are protected.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Clear_Spreadsheet_Edit_Range_by_Folder “C:\LB” “Entry!E1:E11”

Source Folder Path

Specify a full folder path which contains Excel Workbooks
e.g. C:\LB

Cell Address Range

Specify a cell range to support data input
e.g. Entry!E1:E11

Cell Address Range (Optional)

Specify another cell range to support data input
e.g. Entry!F1:F11

Protect Worksheet

Protect_Worksheet

Protect_Worksheet

This command can be used to protect a worksheet of an Excel Workbook.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Protect_Worksheet “C:\LB\Data.xlsx” “Invoice” “P@ssword”

Target File Path

Specify full path of a target Excel Workbook to enable worksheet protection
e.g. C:\LB\Data.xlsx

Target Worksheet

Specify a worksheet to enable worksheet protection
e.g. Invoice

Note: If enable protection for more than one worksheet, please type Worksheet,Worksheet,Worksheet.

Password (Optional)

Specify password for worksheet protection
e.g. P@ssword

Protect_Worksheet_by_Column_Header

Protect_Worksheet_by_Column_Header

This command can be used to protect a common worksheet for all Excel Workbooks in accordance with a list of full file paths.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Protect_Worksheet_by_Column_Header “C:\LB\File List.xlsx” “List Worksheet” “List Column”  “Invoice” “P@ssword”

File Path of a File List

Specify a full file path which contains a file list of Excel Workbooks
e.g. C:\LB\File List.xlsx

Worksheet of the File List

Specify a worksheet name which contains the file list
e.g. List Worksheet

Column Header of the File List

Specify a column header which contains the file list
e.g. List Column

Common Worksheet

Specify a common worksheet of these Excel Workbooks to enable worksheet protection
e.g. Invoice

Password (Optional)

Specify password for worksheet protection
e.g. P@ssword

Protect_Worksheet_by_Folder

Protect_Worksheet_by_Folder

This command can be used to protect a common worksheet for all Excel Workbooks within a file folder.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Protect_Worksheet_by_Folder “C:\LB” “Invoice” “P@ssword”

Source Folder Path

Specify a full folder path which contains Excel Workbooks
e.g. C:\LB

Common Worksheet

Specify a common worksheet of these Excel Workbooks to enable worksheet protection
e.g. Invoice

Password (Optional)

Specify password for worksheet protection
e.g. P@ssword

Unprotect Worksheet

Unprotect_Worksheet

Unprotect_Worksheet

This command can be used to unprotect a worksheet of an Excel Workbook.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Unprotect_Worksheet “C:\LB\Data.xlsx” “Invoice” “P@ssword”

Target File Path

Specify full path of a target Excel Workbook to disable worksheet protection
e.g. C:\LB\Data.xlsx

Target Worksheet

Specify a worksheet to disable worksheet protection
e.g. Invoice

Note: If disable protection for more than one worksheet, please type Worksheet,Worksheet,Worksheet.

Password (Optional)

Specify password for worksheet unprotection
e.g. P@ssword

Set_Var_Unprotect_Worksheet

Set_Var_Unprotect_Worksheet

This command can be used to unprotect a worksheet of an Excel Workbook. A assigned variable will be returned a value (Y or N) to notify whether the the worksheet is unprotected successfully.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Set_Var_Unprotect_Worksheet “Is Unprotect” “C:\LB\Data.xlsx” “Invoice” “P@ssword”

Variable Name

Specify a variable name to store a value upon completion of processing
e.g. Is Unprotect

Target File Path

Specify full path of a target Excel Workbook to disable worksheet protection
e.g. C:\LB\Data.xlsx

Target Worksheet

Specify a worksheet to disable worksheet protection
e.g. Invoice

Note: If disable protection for more than one worksheet, please type Worksheet,Worksheet,Worksheet.

Password (Optional)

Specify password for worksheet unprotection
e.g. P@ssword

Unprotect_Worksheet_by_Column_Header

Unprotect_Worksheet_by_Column_Header

This command can be used to protect a common worksheet for all Excel Workbooks in accordance with a list of full file paths.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Unprotect_Worksheet_by_Column_Header “C:\LB\File List.xlsx” “List Worksheet” “List Column”  “Invoice” “P@ssword”

File Path of a File List

Specify a full file path which contains a file list of Excel Workbooks
e.g. C:\LB\File List.xlsx

Worksheet of the File List

Specify a worksheet name which contains the file list
e.g. List Worksheet

Column Header of the File List

Specify a column header which contains the file list
e.g. List Column

Common Worksheet

Specify a common worksheet of these Excel Workbooks to disable worksheet protection
e.g. Invoice

Password (Optional)

Specify password for worksheet unprotection
e.g. P@ssword

Unprotect_Worksheet_by_Folder

Unprotect_Worksheet_by_Folder

This command can be used to unprotect a common worksheet for all Excel Workbooks within a file folder.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters
Unprotect_Worksheet_by_Folder “C:\LB” “Invoice” “P@ssword”

Source Folder Path

Specify a full folder path which contains Excel Workbooks
e.g. C:\LB

Common Worksheet

Specify a common worksheet of these Excel Workbooks to disable worksheet protection
e.g. Invoice

Password (Optional)

Specify password for worksheet unprotection
e.g. P@ssword

Split_File_by_Worksheet

Split_File_by_Worksheet

This command can be used to save all worksheets of a workbook to individual Excel file.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the relevant manual operations of Excel.

Command with Parameters

e.g. Split_File_by_Worksheet “C:\LB\Many Worksheet.xlsx” “C:\LB” “Y”

Source File Path

Define a full file path of an Excel File
e.g. C:\LB\Many Worksheet.xlsx

Target Folder Path

Define full folder path of a target folder
e.g. C:\LB

Is Converting Formula to Value

Confirm Y or N
e.g. Y