Excel將多個工作表的函數(shù)計算結(jié)果轉(zhuǎn)換為數(shù)值

Sub?FunctionTransValue_Sheets() Dim?sht?As?Worksheet For?Each?sht?In?Worksheets?'遍歷工作表 sht.UsedRange.Value?=?sht.UsedRange.Value Next End?Sub

Sub?FunctionTransValue_Workbooks()?'全部工作簿 Dim?strPath?As?String,?sht?As?Worksheet Dim?strWbName?As?String,?wb?As?Workbook With?Application.FileDialog(msoFileDialogFolderPicker)?'獲取文件夾路徑 If?.Show?Then?strPath?=?.SelectedItems(1)?&?""?Else?Exit?Sub End?With On?Error?Resume?Next With?Application .ScreenUpdating?=?False?'取消屏幕刷新 .DisplayAlerts?=?False?'取消警告信息 .EnableEvents?=?False?'取消事件 .Calculation?=?xlCalculationManual?'取消公式重算 .AskToUpdateLinks?=?False?'取消外鏈詢問 End?With strWbName?=?Dir(strPath?&?"*.xls*") Do?While?strWbName??""?'dir語句遍歷excel文件 If?strWbName??ThisWorkbook.Name?Then Set?wb?=?Workbooks.Open(strPath?&?strWbName)?'打開工作簿 For?Each?sht?In?wb.Worksheets?'遍歷工作表公式轉(zhuǎn)數(shù)值 sht.UsedRange.Value?=?sht.UsedRange.Value Next wb.Close?True?'保存關(guān)閉工作簿 End?If strWbName?=?Dir()?'下一個excel文件 Loop With?Application?'恢復系統(tǒng)設(shè)置 .ScreenUpdating?=?True .DisplayAlerts?=?True .EnableEvents?=?True .Calculation?=?xlCalculationAutomatic .AskToUpdateLinks?=?True End?With If?Err.Number?Then MsgBox?Err.Description Else MsgBox?"轉(zhuǎn)換完成。" End?If End?Sub