Excel帳票では、データの保存について注意が必要です。
特にExcel帳票をVBAで自動化している場合です。
VBAは事務効率の面で重要ですが、適切な処理が無ければ、その誤動作には要注意です。
販売帳票(見積書、納品書、請求書)を自動で作成するエクセルソフトの組み立てです。
今回は第4回目です。完成した帳票をファイル名を付けて、所定の別フォルダーに保存できるようにします。
こんにちは、じゅんぱ店長(@junpa33)です。
今作成しているのはExcel帳票で「エクセルマクロ有効ブック」です。
このタイプのエクセルの作成帳票保存については単に「名前を付けて保存」ではいけません。
一つ前の記事を確認するのは、こちら↓になります。
印刷設定を行うエクセルVBAで、無用なプリントミスをなくす方法
見積書・納品書・請求書作成に関する記事はこちらです。
コンテンツ
Excel帳票のデータ保存 VBAコード付きで?
Excel VBAを使って、Excel帳票を作っている方なら経験があると思いますが、
複数の「エクセルマクロ有効ブック」を、開いていたら何かのきっかけによりVBAの誤動作でデータが消えてしまう。
なんてこと覚えがあると思います。
あちゃ~😵なんて
シャレにならない事経験あるでしょう。
皆さんは、作成したVBAコード付きの「Excel帳票」を、データ保存のためにリネームして丸々そのまま「エクセルマクロ有効ブック」で保存していませんか?
保存したデータが改変されてしまう事も
Excel帳票を速く簡単に作成するために、完全自動化とは行かないまでも一部でもVBAコードを利用しておられる方も多いと思います。
そのExcel帳票だけで、シート数を増やすことによって、データの加工から保存まで行っていればいいのですが、
別名で保存する場合注意が必要となります。
データ保存用にしたExcel帳票も「VBAコードは有効」状態になっています。
エクセルの保存ダイアログは、基本、同じ種類のブックで保存する用に表示されます。
ここではVBAを無効にするためには、
間違いなくタダの「Excelブック(.xlsx)」で保存しなければなりません。
これを行っていないと、「データ保存用として保存したExcel帳票」を複数開いて作業いた時、何かの拍子にVBA誤動作によって、
大切なデータが変わってしまうなんてことにもなりかねません。
たとえばとして次に、今回このテーマで扱っているExcel帳票の保存対策と方法について説明していきます。
VBA誤動作させない対策、Excel帳票の保存法
今回作成してる「見積納品請求書作成エクセルソフト」で考えていきます。
ここで作成した帳票を控え資料として保存しなければいけませんので、その保存のためのVBAコードと共に説明します。
いちばん簡単な方法
いちばん簡単な方法は、
「エクセルマクロ有効ブック(.xlsm)」で作成されているところを、
データ保存のために名前を変えて保存する時に「Excelブック(.xlsx)」で保存することです。
けれども、データとして保存したい部分は、3つのシート「請求書」・「納品書」・「見積書」だけです。
たとえ「Excelブック(.xlsx)」で保存しても要らないシートはそのままです。その要らないシートは削除したいところです。
3つのシートを保存するために他の3つのシートを削除することが必要です。
そこで、これを解決するコードを次に説明します。
Excel帳票を保存するためのエクセルVBAコード
コードの説明をしていきます。
VBAコードはModule4に記述していきます。新たにモジュールを挿入してください。
関連記事
データ保存のVBAコードの全体
Sub 伝票保存()
Dim FolderPathD As String
Dim Shell As Object
Dim Newbook As Object
Dim FP As String, FPN As String, FPM As String, FPMN As String
Dim FPL As String, FPLN As String, FN As String
Dim a As Long
’保存フォルダを選択
Mypath = ThisWorkbook.Path & “\作成伝票”
Set Shell = CreateObject(“Shell.Application”) _
.BrowseForFolder(0, “フォルダを選択してください”, &H1, Mypath)
If Shell Is Nothing Then
FolderPathD = “”
Exit Sub
Else
FolderPathD = Shell.Items.Item.Path
End If
’フォルダ名の取得
FP = FolderPathD
FPN = Mid(FP, InStrRev(FP, “\”) + 1)
FPM = Left(FP, InStrRev(FP, “\”) – 1)
FPMN = Mid(FPM, InStrRev(FPM, “\”) + 1)
FPL = Left(FPM, InStrRev(FPM, “\”) – 1)
FPLN = Mid(FPL, InStrRev(FPL, “\”) + 1)
’ファイルを保存
ChDir FP
Set Newbook = Workbooks.Add
For a = 2 To 4
Workbooks(“見納請3点伝票作成.xlsm”).Activate
Worksheets(a).Cells.Copy
Newbook.Activate
Worksheets(“sheet” & a – 1).Select
With ActiveSheet
.Paste
.Name = Workbooks(“見納請3点伝票作成.xlsm”).Worksheets(a).Name
’行高さ設定
.Rows.RowHeight = 24
.Rows(“1:12”).RowHeight = 19.5
.Rows(2).RowHeight = 9
.Rows(4).RowHeight = 7.5
.Rows(“7:9”).RowHeight = 15.75
.Rows(10).RowHeight = 6.75
.Rows(11).RowHeight = 22.5
.Range(“A1”).Select
’印刷余白の設定
.PageSetup.TopMargin = Application.CentimetersToPoints(1.5)
.PageSetup.LeftMargin = Application.CentimetersToPoints(2.5)
.PageSetup.RightMargin = Application.CentimetersToPoints(1)
.PageSetup.BottomMargin = Application.CentimetersToPoints(1)
.PageSetup.HeaderMargin = Application.CentimetersToPoints(0.5)
.PageSetup.FooterMargin = Application.CentimetersToPoints(0.5)
’プリントタイトル
.PageSetup.PrintTitleRows = “$13:$13”
End With
Application.CutCopyMode = False
Next a
’保存ファイルに名前を付ける
FN = Format(Now(), “yymmdd-hhmm”)
If FPN = “作成伝票” Then
Newbook.SaveAs Filename:=FolderPathD & “\” & FPN & “請求書_” & FN & “.xlsx”
ElseIf FPMN = “作成伝票” Then
Newbook.SaveAs Filename:=FolderPathD & “\” & FPN & “請求書_” & FN & “.xlsx”
ElseIf FPLN = “作成伝票” Then
Newbook.SaveAs Filename:=FolderPathD & “\” & FPMN & FPN & “請求書_” & FN & “.xlsx”
Else
Newbook.SaveAs Filename:=FolderPathD & “\” & FPLN & FPMN & FPN & “請求書_” & FN & “.xlsx”
End If
Range(“A1”).Select
Newbook.Close
Workbooks(“見納請3点伝票作成.xlsm”).Activate
Worksheets(“作業シート”).Activate
Range(“A1”).Select
End Sub
保存フォルダを選択
「作成伝票」というフォルダは名称固定でこの「Excel帳票」ブックと同列で設置必須になります。
このコードが実行されると、この「作成伝票」から下層がツリー表示されます。
フォルダ名の取得
「作成伝票」から3階層下まで取得することが出来ます。
保存時のファイル名を設定するのにそのフォルダ名を利用します。
InStr関数、InStrRev関数の使い方についてはこちらの記事を参考にしてください。
エクセルVBA Instr関数・InstrRev関数で文字列を検索する
Mid関数、Right関数、Left関数についてはこちらを参考にしてください。
ファイルを保存
Excel帳票、保存3シートをコピーペーストで、新しく作成したエクセルBOOKに移します。
シートも同名にします
行高さ設定
エクセルの仕様として、コピーペーストでは行の高さをコピー先に引き継ぐことはできません。
ですので、
テンプレートを再生するためには、改めて各行の高さ情報をコードにセットする必要があります。
印刷余白の設定
印刷余白についてもコピー先には引き継がれません。
印刷余白も改めてコードをセットします。
プリントタイトル
プリントタイトルも再設定します。
保存ファイルに名前を付ける
基本的に名前は自動で振られる設定にしています。
保存するExcel帳票の名付けのルールは、
「1階層下フォルダ名」「2階層下フォルダ名」「3階層下フォルダ名」請求書_年月日-時分
になります。
このケースで説明すると「作成伝票」→「ABショップ」→「CD店」→「EF営業所」であれば、
一階層 | 2階層 | 3階層 |
![]() |
![]() |
![]() |
この様にフォルダ名を使ってファイル名としています。
ファイル名に「時分」を入れているのは、項目追加や項目修正で同名ファイルにならないようにするためのものです。
(追加修正帳票を60秒以内に行うと上書き保存になってしまいます、ご注意を!)
エクセル帳票保存のまとめ
単なるエクセルファイルをデータ保存する時は、別に考えなくてもいいですが、
今このテーマで作成しているような、VBA付きのExcel帳票系のものをデータ保存しようとしたときは特に注意してください。
「データを保存するためのExcel帳票は、VBAコードを必ず削除する」ことを対策としてお勧めします。
VBAコードが誤動作してしまってからでは、全てが「あとの祭り」になります。
それと、
通常のExcelブック(.xlsx)化して保存しても、保存データではない要らないシートが残る場合は、
予め、Excel帳票のVBAコード組み立ての時点で、保存出力のVBAコードを作っておくことがいいと思います。
次回は、今まで作ったVBAコードをコントロールするユーザーフォームを作成します。
実用に耐えるユーザーフォーム作成のコツは具体的実例を教科書に
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAを独学で習得するためのポイントは?良書との出会いは重要
エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。
エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておくといい内容を纏めてみました。
今回の記事はここまでです。 最後までご覧いただき有難うございました。