Skip to main content

ExcelPipe Batch Processing | DataMystic Developer

ExcelPipe Batch Processing via Command Line

Perform bulk find-and-replace operations across hundreds of Excel spreadsheets using ExcelPipe's command-line interface. Update data source connections, fix broken hyperlinks, standardize content, and automate recurring spreadsheet maintenance tasks.

Basic Find and Replace

ExcelPipe searches and replaces text in cell values, formulas, sheet names, headers, footers, comments, and hyperlinks within Excel workbooks. 

REM Basic ExcelPipe find-and-replace in a folder of spreadsheets
ExcelPipe.exe /find:"FY2024" /replace:"FY2025" /folder:"C:\Finance\Reports" /include:"*.xlsx"

REM Replace in cell values and formulas
ExcelPipe.exe /find:"SQLSERVER01" /replace:"DBCLUSTER-PROD" /folder:"C:\Finance\Models" /include:"*.xlsx;*.xlsm" /cells /formulas

REM Case-sensitive replacement in sheet names
ExcelPipe.exe /find:"Q4 Draft" /replace:"Q4 Final" /folder:"C:\Finance\Quarterly" /include:"*.xlsx" /sheetnames /matchcase

REM Replace across all element types (cells, formulas, comments, headers, footers, hyperlinks)
ExcelPipe.exe /find:"old-server.company.com" /replace:"new-server.company.com" /folder:"C:\Reports" /include:"*.xlsx" /all

Data Source URL Updates

After server migrations or domain changes, spreadsheets often contain outdated connection strings, ODBC references, and data source URLs. ExcelPipe updates these in bulk without opening each file manually. 

@echo off
REM Data source migration script
REM Updates database connections across all reporting spreadsheets

set REPORT_DIR=C:\Finance\Reports
set LOG_FILE=C:\Logs\excelpipe_datasource_%date:~-4,4%%date:~-7,2%%date:~-10,2%.log

echo [%date% %time%] Data source update started >> "%LOG_FILE%"

REM Step 1: Update ODBC connection strings
ExcelPipe.exe /find:"Server=SQLPROD01" /replace:"Server=SQLCLUSTER.corp.local" /folder:"%REPORT_DIR%" /include:"*.xlsx;*.xlsm" /recurse /formulas /cells
echo [%date% %time%] Step 1: ODBC connections updated >> "%LOG_FILE%"

REM Step 2: Update linked workbook paths
ExcelPipe.exe /find:"\\FILESRV01\Finance\SharedData" /replace:"\\NAS01\Corporate\Finance\SharedData" /folder:"%REPORT_DIR%" /include:"*.xlsx" /recurse /formulas
echo [%date% %time%] Step 2: Linked workbook paths updated >> "%LOG_FILE%"

REM Step 3: Update web query URLs
ExcelPipe.exe /find:"http://datawarehouse.internal:8080" /replace:"https://analytics.corp.local" /folder:"%REPORT_DIR%" /include:"*.xlsx;*.xlsm" /recurse /all
echo [%date% %time%] Step 3: Web query URLs updated >> "%LOG_FILE%"

echo [%date% %time%] Data source migration complete >> "%LOG_FILE%"

Fix broken hyperlinks across spreadsheets after domain changes, SharePoint migrations, or intranet restructuring. ExcelPipe targets hyperlinks specifically without affecting cell values that may contain similar text. 

@echo off
REM Hyperlink repair script - fix broken links after SharePoint migration

set DOC_ROOT=C:\SharedDrive\AllDepartments
set BACKUP_DIR=C:\Backups\ExcelPipe\%date:~-4,4%%date:~-7,2%%date:~-10,2%

REM Create backup before modifying
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"

REM Update old SharePoint URLs to new tenant
ExcelPipe.exe /find:"https://oldtenant.sharepoint.com/sites" /replace:"https://newtenant.sharepoint.com/sites" /folder:"%DOC_ROOT%" /include:"*.xlsx" /recurse /hyperlinks /backup:"%BACKUP_DIR%"

REM Fix HTTP to HTTPS for internal links
ExcelPipe.exe /find:"http://intranet.company.com" /replace:"https://intranet.company.com" /folder:"%DOC_ROOT%" /include:"*.xlsx" /recurse /hyperlinks

REM Update file server paths in hyperlinks
ExcelPipe.exe /find:"file:///\\OLDSERVER\shared" /replace:"file:///\\NEWNAS\corporate" /folder:"%DOC_ROOT%" /include:"*.xlsx;*.xlsm" /recurse /hyperlinks

echo Hyperlink repair complete. Backups saved to: %BACKUP_DIR%

Batch Folder Processing

Process entire folder hierarchies with ExcelPipe. This example demonstrates a complete end-of-quarter workflow that updates fiscal year references, corrects formulas, and standardizes formatting across all department spreadsheets. 

@echo off
REM End-of-quarter spreadsheet standardization
REM Processes all department folders and updates fiscal year references

set ROOT=\\FileServer\Finance
set YEAR_OLD=2024
set YEAR_NEW=2025
set QUARTER=Q1

echo Processing %ROOT% - Updating FY%YEAR_OLD% to FY%YEAR_NEW%...

REM Update fiscal year in cell values
ExcelPipe.exe /find:"FY%YEAR_OLD%" /replace:"FY%YEAR_NEW%" /folder:"%ROOT%\Budget" /include:"*.xlsx" /recurse /cells
ExcelPipe.exe /find:"FY%YEAR_OLD%" /replace:"FY%YEAR_NEW%" /folder:"%ROOT%\Actuals" /include:"*.xlsx" /recurse /cells
ExcelPipe.exe /find:"FY%YEAR_OLD%" /replace:"FY%YEAR_NEW%" /folder:"%ROOT%\Forecasts" /include:"*.xlsx" /recurse /cells

REM Update sheet names containing the old year
ExcelPipe.exe /find:"%YEAR_OLD% %QUARTER%" /replace:"%YEAR_NEW% %QUARTER%" /folder:"%ROOT%" /include:"*.xlsx" /recurse /sheetnames

REM Update header/footer year references
ExcelPipe.exe /find:"Fiscal Year %YEAR_OLD%" /replace:"Fiscal Year %YEAR_NEW%" /folder:"%ROOT%" /include:"*.xlsx" /recurse /headers /footers

echo Batch processing complete.

Scheduled Execution

Automate ExcelPipe operations with Windows Task Scheduler for recurring maintenance. Schedule nightly data source checks, weekly hyperlink validation, or monthly standardization passes.

Using schtasks.exe (Command Line)

REM Schedule nightly data source connection update (2:30 AM)
schtasks /create /tn "DataMystic\ExcelPipe Data Source Fix" /tr "\"C:\Scripts\excelpipe_datasource.bat\"" /sc daily /st 02:30 /ru SYSTEM /rl HIGHEST

REM Schedule weekly hyperlink audit every Sunday at 4:00 AM
schtasks /create /tn "DataMystic\ExcelPipe Hyperlink Audit" /tr "\"C:\Scripts\excelpipe_hyperlinks.bat\"" /sc weekly /d SUN /st 04:00 /ru SYSTEM /rl HIGHEST

REM Run monthly on the 1st at midnight for fiscal year updates
schtasks /create /tn "DataMystic\ExcelPipe Monthly Update" /tr "\"C:\Scripts\excelpipe_monthly.bat\"" /sc monthly /d 1 /st 00:00 /ru SYSTEM /rl HIGHEST

Using PowerShell (Task Scheduler Cmdlets)

# Schedule ExcelPipe nightly data source update
# Runs at 2:30 AM with retry logic and execution time limit

$action = New-ScheduledTaskAction `
    -Execute "C:\Scripts\excelpipe_datasource.bat" `
    -WorkingDirectory "C:\Scripts"

$trigger = New-ScheduledTaskTrigger -Daily -At "02:30"

$settings = New-ScheduledTaskSettingsSet `
    -StartWhenAvailable `
    -RestartCount 2 `
    -RestartInterval (New-TimeSpan -Minutes 10) `
    -ExecutionTimeLimit (New-TimeSpan -Hours 3) `
    -AllowStartIfOnBatteries `
    -DontStopIfGoingOnBatteries

$principal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -RunLevel Highest

Register-ScheduledTask `
    -TaskName "DataMystic\ExcelPipe Nightly Data Source" `
    -Action $action `
    -Trigger $trigger `
    -Settings $settings `
    -Principal $principal `
    -Description "Nightly ExcelPipe batch to update data source connections in finance spreadsheets"

# Verify task creation
Get-ScheduledTask -TaskName "ExcelPipe Nightly Data Source" -TaskPath "\DataMystic\" | Format-List

Next Steps