【Excel】マクロや計算式を除外して保存する方法

資料を作る時はVLOOKUPとか、いっぱい数式が入ってる。

しかし、誰かに渡す時は安心して扱えるように全ての数式をデータに変換しておきたい時がある。

※何気にキーを押すと数式が#ERRORを吐くことがあるから

具体的には、

  1. 全シート(但し、非表示シートを除く)を選択状態
  2. 全セルを選択
  3. ワークシートをコピー
  4. 値(あるいは計算結果)を使用するモードで、ワークシートにペースト
  5. 普通のExcelファイルに保存

するダケ。

'全シート選択
Dim worksheet As Worksheet
Dim wss() As String
Dim cnt As Integer
cnt = 0
For Each worksheet In Worksheets
  If worksheet.Visible = xlSheetVisible Then
    cnt = cnt + 1
    ReDim Preserve wss(cnt -1)
    wss(cnt - 1) = woeksheet.Name
  Else
    '確認メッセージを非表示
    Application.DisplayAlerts = False
    'ワークシートを削除
    worksheet.Delete
    '確認メッセージを表示
    Application.DisplayAlerts = True
  End If
Next
Sheets(wss).Select

'コピー
Cells.Select
Selection.Copy

’データ貼り付け
Selection.PasteSpecial Paste:= xlPasteValues, _
  Operation:= xlNone, _
  SkipBlanks:= False, _
  Transpose:= False

'セル選択の解除
Application.CutCopyMode = False

全シート選択時は非表示シートが含まれているとエラるので注意。

非表示シートは不要なら削除して手渡しした方が良いかもしれないけど

確認メッセージが出るのがうざい。

そうなるとマクロも消して保存したくなる。

名前を変えて保存で、ファイルの種類を「Excel ブック (*.xlsx)」に切り替えて保存すればいいが

マクロ記録したコードでは

読めないExcelファイルを保存しまいがち

実際エラったり散々なようだ。

  • MSのサイトではエラったら・・・・・・・・
  • FileFormatパラメータは 1を使う
  • FileNameパラメータは 拡張子を省く

ことをお勧めしていた。

確かにエラーは発生しないものの。

使い物にならないEXCELファイルになってしまう

※開くと「アカン!読めんw」と泣きが入る

※開くと「一部読めんw」と呟いて、書式が壊れCSVファイルを読んだ様な画面になる

色々試した結果

こうなった。

Dim bookName as String
bookName = ActiveWorkbook.Name
bookName = Replace(ActiveWorkbook.Name,".xlsm","xlsx")
Dim bookNamePath
bookNamePath = Split(ActiveWorkbook.FullName,"\")
ReDim Preserve bookNamePath(UBound(bookNamePath)-1)
bookNamePath = Join(bookNamePath,"\")
ChDir bookNamePath
'確認メッセージを非表示
Application.DisplayAlerts = False
'普通のExcelファイルに保存
ActiveWorkbook.SaveAs Filename:= bookName, _
  FileFormat:= xlOpenXMLWorkbook, _
  Password:= "", _
  WriteResPassword:= "", _
  ReadOnlyRecommended:= False, _
  CreateBackup:= False
'確認メッセージを表示
Application.DisplayAlerts = True
'処理完了メッセージ
MsgBox bookNamePath & "\" & bookName & vbCrLf & "へ名前を変えて保存"

SaveAsのパラメータは

  • FileFormatパラメータは xlOpenXMLWorkbook を使う
  • FileNameパラメータは 拡張子をxlsx に変える

これで、確認メッセージは出てくるもののちゃんと保存できたし、マクロも消えていた。

しかし、このコードがいつのまにか使い物にならなくなっている可能性がきわめて高いのが

MSクオリティ。

PS.2022/6/10

てか、サブPCの古いExcelで動作をチェックしてたので、色々支障がありました。

今のExcelなら【Microsoft365用】Excelのマクロや計算式を除外して保存する方法を参考にしてください。

後、ReadOnlyRecommendedの綴りがまちがっていたので訂正。多分、何度も貼り換えてるうちに変になったんだろうなぁ・・・




コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA