VBA & Macro Problems

How to Compare Data Between Two Excel Workbooks Using Office Scripts

Huma Ashraf Ch Oct 12, 2026 869 views

Question details

The user needs to compare company names in Workbook A against a reference list in Workbook B, and output the matching results into a new column in Workbook A.

How to Compare Data Between Two Excel Workbooks Using Office Scripts

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing datasets across multiple workbooks to identify matching records automatically.
Observed behavior: The user wants an efficient way to automate the cross-workbook data comparison process using Office Scripts without strictly relying on Power Automate.

Before you start

Ensure both workbooks are saved in an accessible cloud location such as OneDrive or SharePoint, and verify that you have the appropriate permissions to create and execute Office Scripts in your Microsoft 365 environment.

Solution 1Recommended

Compare Workbooks Using Linked Data and Office Scripts

This method links the reference data from Workbook B into Workbook A, allowing a local Office Script to compare the lists natively without requiring a complex Power Automate flow.

Instead of attempting complex cross-workbook script executions, the most efficient approach is to bring the reference data directly into the active workbook. By using Excel's native workbook links to place the data on a hidden sheet, you can easily use the LinkedWorkbook.refreshLinks API to keep it updated before your script runs the comparison logic.

Create a Reference Sheet

Open Workbook A and create a new worksheet. You can hide this sheet later by right-clicking the sheet tab and selecting 'Hide' to keep your workspace clean.

Link Data from Workbook B

Use Excel's Workbook Links feature to pull the reference list of company names into the new sheet. Simply type '=' in the first cell, navigate to Workbook B, and select the data range.

Create the Office Script

Navigate to the Automate tab on the Excel ribbon, click on 'New Script' to open the Code Editor, and begin writing your custom TypeScript code.

Refresh Linked Data in the Script

Add the command 'workbook.refreshLinkedWorkbooks()' (or the equivalent LinkedWorkbook.refreshLinks API) at the very start of your script to ensure the linked data from Workbook B is fully up to date.

Execute the Comparison Logic

Complete your script logic by comparing the company names in your main sheet with the newly refreshed reference sheet, outputting the matching results into the desired new column in Workbook A.

Compare Workbooks Using Linked Data and Office Scripts

Additional Scripting Support: If you encounter complex scripting issues or need specific code examples, posting in the Microsoft Power Automate Community is a great way to find advanced Office Script troubleshooting assistance.

Advanced Data Processing in WPS Office

Easily Compare Data Across Workbooks with WPS Spreadsheet

You can quickly compare data between two workbooks in WPS Spreadsheet using built-in functions like VLOOKUP and XLOOKUP, or automate the entire process locally using WPS Macro (VBA/JS) without needing cloud-dependent script environments.

1. Open Your Workbooks: Launch WPS Spreadsheet and open both Workbook A and Workbook B in the same window using the tabbed interface.
2. Apply a Comparison Formula: In Workbook A, create a new column and enter a VLOOKUP, XLOOKUP, or MATCH formula referencing the company list in Workbook B.
3. Automate with WPS Macro (Optional): Navigate to the Tools tab, click on Macros, and write a straightforward VBA or JS script to perform cross-workbook comparisons instantly.
4. Save and Export: Once the matching data is populated, save your workbook. WPS Office perfectly preserves all cross-workbook formulas and macro configurations.

Fully compatible with Microsoft Excel file formats (.xlsx, .xlsm)Supports advanced local VBA and JS Macros for seamless automationFree and lightweight alternative for heavy data tasksFamiliar user interface requiring no learning curve

Frequently Asked Questions

Can I run an Office Script across two workbooks directly?

By default, Office Scripts execute strictly within the context of the active workbook they are running in. To interact with another workbook, you must either use Power Automate to pass data between them or link the external data directly into the active workbook.

How do I refresh linked workbook data automatically within an Office Script?

You can automate the data refresh by calling the 'workbook.refreshLinkedWorkbooks()' API method at the beginning of your Office Script. This ensures your script always processes the most current data before running the comparison logic.

Is Power Automate absolutely necessary for cross-workbook comparison?

No, it is not required. By utilizing Excel's native Workbook Links to pull the external reference list into a hidden sheet in your active workbook, you can perform the entire data comparison locally using just an Office Script.

Excel & Data Problems