![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
Office365です。
現在開いているマクロブックの
「一番左にある1シート」もしくは「〇〇〇〇という特定の名称シート」のみを、
.xlsx(マクロなしブック)として保存するにはどのような処理になるのでしょうか?
その時の保存先は、
「今開いているマクロブックと同じ場所(フォルダ)」または「今開いているユーザのデスクトップ」に
したいです。(つまりどのユーザが操作しても統一された挙動となる)
シート名をファイル名にし、末尾に保存した瞬間の日時が入って、複数回実行した時に重複しないようにしたいです。
〇〇〇〇というシート名だったとして、現在2021年3月22日18時56分だっとしたら、
〇〇〇〇_20210322_1856.xlsx としたいです。
ただ不明点は、該当シートには、マクロ実行用のフォームボタンが設置されており、
それを削除出来た方がスマートなのにと思ってます。
No.8ベストアンサー
- 回答日時:
取敢えず、今のロジックを変えない場合は、こんな感じでどうでしょう。
ちなみに、メモリースタックするようでしたら、ロジック自体変えた方が良いかもしれません。
Sub Sample2()
Dim FolPath As String
Dim NewBK_name As String
Dim obj As Button
FolPath = CreateObject("WScript.Shell").SpecialFolders("desktop")
NewBK_name = Sheets(1).Name & "_" & Format(Date, "yyyymmdd") & "_" & Format(Time, "hhmmss")
Application.ScreenUpdating = False
Sheets(1).Copy
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs FolPath & "\" & NewBK_name & ".xlsx", FileFormat:=51
Application.DisplayAlerts = True
.Worksheets(1).UsedRange.Value = .Worksheets(1).UsedRange.Value
For Each obj In .Worksheets(1).Buttons
obj.Delete
Next
.Close SaveChanges:=True
End With
Application.ScreenUpdating = True
End Sub
他に削除するものが無く、フォームボタンすべてを消すなら、
’#4 fujillin様のコードがスマートと思いますのでActiveSheet.Buttons.Delete で
No.10
- 回答日時:
こんばんは、
なんか意地悪したみたいでごめんなさい。
自身で考える事や試す事がプログラムを作ろ時の面白さと言うか、醍醐味です。出来たものを試すだけだと、好奇心や探究心は生まれず、私の様に趣味でVBAをこさえる輩を理解でき、、、いやいや理解してもらいたい訳では無いのですが、少しうれしくて。。
本題です。
列や行を削除するコードは、
Columns(2).DeleteやColumns("B").Deleteなどとなります。
他にも.EntireColumnを使ってRange("B1").EntireColumn.Delete
この場合明示したセルを含む(がある)列が対象になります。
(.EntireColumnを使うのはCellsで指定している場合やRangeオブジェクト変数で範囲を指定している場合などが多いです)
更にRangeを使用する場合だとRange("B:B").Delete
これがむしろわかり易いかも知れません。
行に関しては、同様に Columns部分がRowsに代わるだけです。
記録マクロで考えてみましょう。
私も記録マクロは今でも使います。メソッドが記録できるか、プロパティなど、デフォルトなどの確認に使います。
最初に記録マクロを整理するヒントです。これも最初に覚えた事です。
言葉で書くと 例えば、
私はテーブルのお皿にあるりんごを選んで、選んでいるそれを食べました
どこにあるりんごなのかは、とても大切な事ですが、
行動(実行)として端的に考えると
私はお皿にあるりんごを食べました。で解ります。
つまり、
Columns("B:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
選んでSelect と 選んでいるSelectionは同じものを指しています
なので
Columns("B:D").Delete Shift:=xlToLeft
でB,C,D列が削除されます。Shift:=xlToLeftはデフォルトなので
Columns("B:D").Delete だけでも問題なく削除され左にシフトします。
Application.CutCopyMode = False これは何か?
これは、コピーモード、、範囲指定してコピーした時のコピー範囲点線
を解除(ESCキー)をした時に書き込まれるコードです。
今回不要と考えられます。Range("A1").Select これは、意外に必要になる場合があります。ただし、マクロの終了時のセル選択位置を思う場所にしたい時です。 マクロ実行中のコード内には極力.Selectを行わないのが処理を早く、またわかり易くします。(例外もあります)
では、挿入場所です。(#9の*をよく読んで理解するようにしてください)
#9に書かせていただいた注意点をふまえるとシート加工の最後に入れるのが良いと思います。
Nextの下の行に入れるのが良いと思います。
ここで注意点があります。
With ActiveWorkbookの中でActiveWorkbookは新しいブックです。
そのボタンや値を操作して列を削除するのですから、
先ほどのりんごの文章にあるように どこの(テーブルの)が大事になります。(この時ブックは2つ以上開いている状態なので)
With ActiveWorkbookの中で書かれているほかのオブジェクトの書き方を参考に
.Columns("B:D").Delete Shift:=xlToLeft
とします。 .Columnsの前にはActiveWorkbookが省略(ちょっと違うけど)しているのと同じ事になります。
付近を書くとこんな感じ
Sheets(1).Copy
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs FolPath & "\" & NewBK_name & ".xlsx", FileFormat:=51
Application.DisplayAlerts = True
.Worksheets(1).UsedRange.Value = .Worksheets(1).UsedRange.Value
For Each obj In .Worksheets(1).Buttons
obj.Delete
Next
.Columns("B:D").Delete Shift:=xlToLeft
.Close SaveChanges:=True
説明べたで長文になってしまい申し訳ありません。
最後に
#9のお礼の場所に削除コードを入れるとコピー元シートのB:D列が削除されると思うにですが、、ではありませんか。。
検証用表組みになっていて確認しにくくなっているのでは無いかと思いますがどうでしょう。
ありがとうございます!
ネットで調べると、例題の事例に対しての解説は載ってますが、
実際の自分向けの解説ではなく、、、
今回は直接的な解説なので感動です。
非常に分かりやすくて何度も読み返してます。
マクロの記録だと、目的を予測せずリアルタイムの行動を逐一コード化してるだけですね。
「範囲を指定して(点線が付いて)、その範囲を削除」みたいな・・・
↓それを効率よく書くと
「この範囲を削除」
と余計な処理を省いて目的だけ処理する。という事が出来るんだな、という理解です。
>#9のお礼の場所に削除コードを入れるとコピー元シートのB:D列が削除されると思うにですが、、ではありませんか。。
確かにそうでした。後々、試してみて気づきました。失礼しました。
こんな事までも出来てしまうんだ、、、という感動の反面、
まだまだこの可能性を知らず、使ってない人が沢山いるんだなという思い。
跳躍してRPAとか話題になってますが、大半はExcelでまだまだ解決できそうだなと可能性を感じてます。
まだ自分が書かれたソースを都合よく改変する程度ですが、
めちゃ楽しいです。
No.9
- 回答日時:
>理想通りに動きました。
良かったです。
.Worksheets(1).UsedRange.Value = .Worksheets(1).UsedRange.Value
の下の行にカラムを削除するコードを入れれば出来ると思います。
「B列、C列、D列」と連続したカラムなら1行コードだと思います。
カラム削除コードは、多分ご存知かと思いますが、調べてみてくださいね。
*フォームボタン以外のシェイプ、グラフなどがありセルに合わせてサイズを変更するなどとしている場合、サイズが変更される可能性があります。
また、.Worksheets(1).UsedRange.Value = の前にカラム削除を実行した場合、削除される列を参照している関数の解がエラーになるので注意が必要だと思います。
作り甲斐をすべて奪いたくないので、すみません。あしからず。
ありがとうございます。
超初心者です。マクロ記録からいじる事しかできません(恥
B~D列を削除するために、下記コードを入れました。
Columns("B:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets(1).Copy
With ActiveWorkbook の直前に入れました。
動作的には問題なく、エラーも置きませんでした。
おすすめの書き方などあったらお願いします。
No.7
- 回答日時:
Application.DisplayAlerts = False を言いたかっただけです
>ボタンが2つ配置してあって、右側だけ消すみたいなのも出来るのでしょうか?
たとえば、左のボタンがA1セルにかかっている場合は、こんな感じで残せますと言う例でした。。
失礼しました。
こちらこそ流し読みで失礼しました。
「Application.DisplayAlerts = False」
何とかこれは存じ上げておりました。
・画面表示の有無
・中断メッセージの有無
・上書保存確認の有無
等がありますよね。
ありがとうございます!
No.6
- 回答日時:
ちなみに
よく考えると保存するシートモジュールにマクロが記載されている場合、
アラートが出力されるので、抑制が必要になりますね。
Dim obj As Button
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs FolPath & "\" & NewBK_name & ".xlsx", FileFormat:=51
Application.DisplayAlerts = True
For Each obj In .Worksheets(1).Buttons ’フォームボタン
If obj.TopLeftCell.Address(0, 0) <> "A1" Then
obj.Delete
End If
Next
.Close SaveChanges:=True
End With
ボタンの左上がA1セルにかかっているボタンは削除されません。
ありがとうございます。
A1セルにかかっているボタンが削除されないというのは、
「そうせざるを得ない」理由があるのでしょうか。
それともこれを利用する事により応用ができて便利なのでしょうか?
No.5
- 回答日時:
>では、ボタンが2つ配置してあって、右側だけ消すみたいなのも出来るのでしょうか?
>その時はボタンを特定する何かが必要になりますよね???
ボタンを特定する方法は、いくつかあります。
すべてでないので For Each などで処理する必要がありますが
インデックスが分かっていれば、インデックスで対象又は対象から外す。
同様にオブジェクトの名前が分かっていれば、名前で、
更に配置が分かっていれば、If obj.TopLeftCell.Address ・・みたいに
配置アドレスなどを取得して 処理するとか、、、色々考えられますね。
No.4
- 回答日時:
こんばんは
すでに回答が出ていますが、ご参考までに。
(フォームボダン限定で削除しています。ActiveXボタンは残ります)
Sub sample_12270179()
Dim n
n = Worksheets(1).Name & Format(Date, "_yyyymmdd") & Format(Time, "_hhmmss") & ".xlsx"
Worksheets(1).Copy
ActiveSheet.Buttons.Delete
ActiveWorkbook.SaveAs CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & n
ActiveWorkbook.Close False
End Sub
No.3
- 回答日時:
まずいです。
本当に申し訳ない。#2について
obj.Delete、、図形なども消してしまいますね。
下記に修正してみてください。
For Each obj In .Worksheets(1).Shapes
If obj.Type = 8 Then
obj.Delete
End If
Next
8 は、msoFormControl です。
もし、ActiveXコントロールを使用している様でしたら
obj.Type = 12 又は or で
更にフォームボタン限定なら、
Dim obj ’宣言を書き換えて
For Each obj In .Worksheets(1).Buttons
obj.Delete
Next
で行けるかと、、、連投すみません。
オブジェクトの消し方にも色々種類があるんですね。
質問だけです。
では、ボタンが2つ配置してあって、右側だけ消すみたいなのも出来るのでしょうか?
その時はボタンを特定する何かが必要になりますよね???
No.2
- 回答日時:
#1です
ごめんなさい。
>ただ不明点は、該当シートには、マクロ実行用のフォームボタンが設置されており、それを削除出来た方がスマートなのにと思ってます。
追加しました。。
Sub Sample2()
Dim FolPath As String
Dim NewBK_name As String
Dim obj As Shape
FolPath = CreateObject("WScript.Shell").SpecialFolders("desktop")
NewBK_name = Sheets(1).Name & "_" & Format(Date, "yyyymmdd") & "_" & Format(Time, "hhmmss")
Application.ScreenUpdating = False
Sheets(1).Copy
With ActiveWorkbook
.SaveAs FolPath & "\" & NewBK_name & ".xlsx"
For Each obj In .Worksheets(1).Shapes
obj.Delete
Next
.Close SaveChanges:=True
End With
Application.ScreenUpdating = True
End Sub
No.1
- 回答日時:
こんばんは、
よくある処理なので取敢えず実行コードを書きます。
内容をデバッグして確認してくださいね。
サンプルは、一番左にある1シート にしました。
理由は、必ずあるからです。特定名称の場合、見つからない場合の処理が必要になりますので、悪しからず。
Sub Sample1()
Dim FolPath As String
Dim NewBK_name As String
FolPath = CreateObject("WScript.Shell").SpecialFolders("desktop")
NewBK_name = Sheets(1).Name & Format(Date, "yyyymmdd") & "_" & Format(Time, "hhmmss")
Application.ScreenUpdating = False
Sheets(1).Copy
ActiveWorkbook.SaveAs FolPath & "\" & NewBK_name & ".xlsx"
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub
試していないので、コピーブックなどでテストしてください。
ありがとうございます!
No.3までを反映させて試してみたところ正常に動きました。
希望の仕様をいくつか選択肢で出しましたが、
そのチョイスも納得できて勉強になりました。
「必ず存在するシートだから」とか「なかった時の処理が必要になるから」とか。ズバリ期待どおりでした。
またフォームボタンも消えてスッキリしました。
※あっても問題ないのですが、より後処理がしっかりしてる感が出せますね。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBA Shapes コピーと名前
-
セルに入力するたびにマクロを...
-
エクセル メニューバーの中の...
-
再度,ExcelVBA,public変数が消える
-
エクセルVBAコンボボックス...
-
VB.NETによるEXCELの行挿入
-
【Excel】マクロでページを追加...
-
Excelでマクロ実行中に画面を固...
-
エクセルのバージョンアップに...
-
エクセルに写真を挿入するマ...
-
エクセルファイルを値でのみし...
-
ExcelのVBAで自動採番したい...
-
VBAで保存しないで閉じると空の...
-
WPSOffice_マクロの有効化について
-
Excelのマクロでボタンを押すと...
-
エクセル関数>参照ファイル名...
-
マクロの保存先、開いてるすべ...
-
Excel マクロの編集がグレーに...
-
エクセルで、「いいね」のよう...
-
複数のマクロボタンをまとめて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセル連番自動印刷について
-
VBA Shapes コピーと名前
-
Excelでマクロ実行中に画面を固...
-
エクセルファイルを値でのみし...
-
セルに入力するたびにマクロを...
-
EXCEL 行番号や列番号が選択で...
-
エクセル マクロ写真帳に一括で...
-
【Excel】マクロでページを追加...
-
ブックの共有でVBAエラー
-
EXCEL VBA 他のアプリケーショ...
-
【ExcelVBA】マクロブックを通...
-
エクセル 図形の寸法を取得したい
-
VBAで行コピーして挿入
-
エクセルVBAで納期管理システム...
-
エクセルファイルの回数期限の...
-
再度,ExcelVBA,public変数が消える
-
グラフ要素のセルを参照したい...
-
ExcelのVBAで自動採番したい...
-
VB.NETによるEXCELの行挿入
-
マクロで非表示シート内にある...
おすすめ情報
後から気づいてしまい申し訳ないのですが、
他のシートを参照する式が入っていたため、
デスクトップに保存されたファイルを開こうとすると「リンクを更新するか」聞かれてしまいます。
保存するxlsxファイルの方は、セルの数式を全て値に置き換える事は可能でしょうか?
手作業で言うと以下の操作になります。
Ctrl+A → コピー →値で貼り付け
でも元のxlsmの一番左のシート自体の数式は値に変換されたくないのです。
可能であれば教えてください。
またまた追加ですみません。
値で保存するのは完璧でした。
値で貼り付けて、保存する直前に、「B列、C列、D列」を削除しておきたい場合は、
どんな追加をすれば良いですか?
つまり保存後、デスクトップのxlsxファイルを開くと上記3列が無くなっているイメージです。
(E列以降が左にシフト)
追記ですみません。
B:D列の削除は出来ました。
ちなみに
.Columns("B:D").Delete Shift:=xlToLeft
↑ではうまくいかず、
Columns("B:D").Delete Shift:=xlToLeft
↑のようにピリオドを消したらうまくいったのは何故でしょう???
あともし、
A:D列を削除したかった場合は、
また記述が変わるんですよね?(やってみたらエラーが出ました)
余裕があったらお願いいたします。