כיצד להמיר את כל הנוסחאות לערכים בקובץ אחד/מספר קבצים
במדריך הבא נראה לכם כיצד ניתן להפוך באמצעות קוד VBA את כל הנוסחאות לערכים (“הדבקה כערכים”).
ניתן לבצע פעולה זו על:
- גיליון בודד
- כל הגיליונות בקובץ
- כל הגיליונות בכל הקבצים בתיקיה
(מי שלא יודע כיצד להריץ קוד VBA מוזמן להציץ במדריך הנ”ל)
שימו לב – לפני תחילת עבודה עם אחד מהקודים המצורפים, אנו ממליצים בחום לגבות את כל עבודתכם. לא ניתן לשחזר את הנוסחאות לאחר הרצת הקוד!
המרת הנוסחאות לערכים בגיליון בודד:
על מנת להמיר את כל הנוסחאות בגיליון מסוים לערכים, הכניסו את הקוד הבא לתוך Module חדש והריצו אותו:
Sub ConvertFormulasToValuesSinglesheet()
ThisWorkbook.ActiveSheet.UsedRange.Value = ThisWorkbook.ActiveSheet.UsedRange.Value
End Sub
המרת הנוסחאות לערכים בכל הגיליונות בקובץ מסוים:
על מנת להמיר את כל הנוסחאות בכל הגיליונות (כולל הגיליונות המוסתרים!) בקובץ מסוים לערכים, הכניסו את הקוד הבא לתוך Module חדש והריצו אותו:
Sub ConvertFormulasToValuesAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.UsedRange.Value = ws.UsedRange.Value
NextEnd Sub
המרת הנוסחאות לערכים בכל הגיליונות בכל הקבצים בתיקיה מסוימת:
אזהרה נוספת – גבו את הקבצים לפני הרצת הקוד, שכן בסיום הרצת הקוד כל הקבצים בתיקיה יהיו נטולי נוסחאות ולא ניתן יהיה לשחזרן!
כעת, נניח שיש לכם בתיקיה מסוימת 20 קבצי אקסל, ואתם מעוניינים להמיר את כל הנוסחאות שמופיעות בקבצים הללו לערכים.
תוכלו לעשות זאת על ידי הרצת הקוד המצורף, שמבצע את הדברים הבאים:
- פותח תיבת דו שיח המאפשרת לכם לבחור את התיקיה שבה נמצאים הקבצים שלכם
- פותח קובץ אקסל מהתיקיה
- עובר על כל הגיליונות בקובץ האקסל הזה ומחליף את כל התאים המכילים נוסחאות לתאים שמכילים ערכים
- שומר את קובץ האקסל, סוגר אותו ועובר לקובץ הבא
להלן הקוד:
Sub LoopAllExcelFilesInFolderCancelFormulas()
‘Purpose: To loop through all Excel files in a user specified folder and convert all formulas to values
Dim wb As Workbook
Dim ws As Worksheet
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
‘Optimize Macro Speed
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
‘Disable Excel Pop-up messages:
Application.DisplayAlerts = False
‘Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = “Select A Target Folder”
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & “\”
End With
‘In Case of Cancel
NextCode:
myPath = myPath
If myPath = “” Then GoTo ResetSettings
‘Target File Extension (must include wildcard “*”)
myExtension = “*.xls*”
‘Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
‘Loop through each Excel file in folder
Do While myFile <> “”
‘Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)
‘Ensure Workbook has opened before moving on to next line of code
DoEvents
For Each ws In wb.Sheets
ws.UsedRange.Value = ws.UsedRange.Value
Next
‘Save and Close Workbook
wb.Close SaveChanges:=True
‘Ensure Workbook has closed before moving on to next line of code
DoEvents
‘Get next file name
myFile = Dir
Loop
‘Message Box when tasks are completed
MsgBox “Task Complete!”
ResetSettings:
‘Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub
כעת כל הקבצים בתיקיה מכילים ערכים בלבד 🙂
הערה: הקוד שרץ על כל הקבצים בתיקיה ומבצע פעולה מסוימת מתבסס על הקוד המצורף מאתר thespreadsheetguru.
לידיעת המבקרים !
כל מקטעי הקוד העוסקים בבדיקה האם הגיליון מוסתר > מבטלים את הסתרתו ובסוף מסתירים אותו מחדש – מיותרים לחלוטין.
הפקודה: ws.UsedRange.Value = ws.UsedRange.Value
פועלת, היטב, גם על גיליונות מוסתרים.
——
מיקי
תודה רבה על הערתך מיקי, אתה אכן צודק ובעקבות הערתך ועידכנו את הקוד 🙂
תודה !