懒人脚本分享-Excel_Remove_Formulas_Save_Value

carnelian · 发表于 2026-1-27 19:50

受到本论坛坛友的工具启发
https://www.52pojie.cn/thread-2048067-1-1.html

才有了这个脚本

实现单脚本(bat) 拖拽--输出--另存为

源码内容如下：

@echo off
setlocal enabledelayedexpansion

:: ======================================
:: Ultimate Pure English Script (Perfect Quote Matching)
:: Core: No parse error, complete VBS syntax, support all paths
:: ======================================
:: 1. Receive and verify target file
set "src_file=%~f1"
if not defined src_file (
    >&2 echo [Error] No Excel file specified.
    >&2 echo [Usage] Drag .xls/.xlsx to this bat file.
    pause >nul
    exit /b 1
)

if not exist "%src_file%" (
    >&2 echo [Error] File "%src_file%" not found.
    pause >nul
    exit /b 1
)

set "file_ext=%~x1"
if /i not "!file_ext!"==".xls" if /i not "!file_ext!"==".xlsx" (
    >&2 echo [Error] Only .xls and .xlsx are supported.
    pause >nul
    exit /b 1
)

:: 2. Construct new file name (Original_NoFormulas.ext)
set "dst_file=%~dpn1_NoFormulas%~x1"

:: 3. Define temporary VBS path (Same as bat, no space path issue)
set "temp_vbs=%~dp0Excel_Temp_Ultimate.vbs"
if exist "%temp_vbs%" del /f /q "%temp_vbs%" >nul 2>&1

:: 4. Generate VBS with improved formula-to-value conversion
(
echo Set objApp = CreateObject("KET.Application"^)
echo If Err.Number ^<^> 0 Then
echo     Set objApp = CreateObject("ET.Application"^)
echo End If
echo If Err.Number ^<^> 0 Then
echo     Set objApp = CreateObject("Excel.Application"^)
echo End If
echo If Err.Number ^<^> 0 Then
echo     WScript.Quit 1
echo End If
echo objApp.Visible = False
echo objApp.DisplayAlerts = False
echo objApp.ScreenUpdating = False
echo On Error Resume Next
echo Set objWorkbook = objApp.Workbooks.Open("%src_file%"^)
echo If Err.Number ^<^> 0 Then
echo     WScript.Quit 1
echo End If
echo On Error GoTo 0
echo For Each objWorksheet In objWorkbook.Worksheets
echo     Set objUsedRange = objWorksheet.UsedRange
echo     If Not objUsedRange Is Nothing Then
echo         objUsedRange.Copy
echo         objUsedRange.PasteSpecial -4163 ' xlPasteValues
echo         objApp.CutCopyMode = False
echo         ' Alternative method: directly replace formulas with values
echo         On Error Resume Next
echo         For Each objCell In objUsedRange
echo             If objCell.HasFormula Then
echo                 objCell.Value = objCell.Value
echo             End If
echo         Next
echo         On Error GoTo 0
echo     End If
echo Next
echo objWorkbook.SaveAs "%dst_file%", objWorkbook.FileFormat
echo objWorkbook.Close False
echo objApp.Quit
echo Set objUsedRange = Nothing
echo Set objWorksheet = Nothing
echo Set objWorkbook = Nothing
echo Set objApp = Nothing
) > "%temp_vbs%"

:: 5. Verify VBS creation
if not exist "%temp_vbs%" (
    >&2 echo [Error] Failed to create temporary VBS file.
    pause >nul
    exit /b 1
)

:: 6. Execute VBS silently
cscript //nologo "%temp_vbs%" >nul 2>&1
if errorlevel 1 (
    >&2 echo [Error] Failed to process Excel file.
    >&2 echo Possible reasons:
    >&2 echo 1. WPS Office or Excel is not installed
    >&2 echo 2. File is corrupted or in use by another program
    >&2 echo 3. Insufficient permissions
    del /f /q "%temp_vbs%" >nul 2>&1
    pause >nul
    exit /b 1
)

:: 7. Clean up temporary file
del /f /q "%temp_vbs%" >nul 2>&1

:: 8. Verify the output file
if not exist "%dst_file%" (
    >&2 echo [Error] Failed to create output file.
    pause >nul
    exit /b 1
)

:: 9. Exit silently on success
exit /b 0

对比图如下：
https://img.cdn1.vip/i/6978a6551c5cd_1769514581.webp

附件提供编辑完成的，富豪随意~
（其实就是源码粘贴到记事本，另存为bat脚本即可）

更多用法自行探索
如有不当/违规支出，烦请管理大大批评指正

对了，如果大佬不吝帮忙斧正优化，鄙人在此提前表示感谢
（原谅我的shit山代码）