Excel帳票のデータ保存はVBA誤動作の対策を必ずしよう。

3denpyohozoneyecatch

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 始めての起動。VBEの立ち上げ、保存と終了

 

データ保存の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帳票」ブックと同列で設置必須になります。

このコードが実行されると、この「作成伝票」から下層がツリー表示されます。

 

MEMO

フォルダをダイアログ指定する方法はこちらの記事を参考にしてください。

Excel VBA 保存先フォルダをダイアログ指定で変数化

 

フォルダ名の取得

「作成伝票」から3階層下まで取得することが出来ます。

保存時のファイル名を設定するのにそのフォルダ名を利用します。

 

MEMO

InStr関数、InStrRev関数の使い方についてはこちらの記事を参考にしてください。

エクセルVBA Instr関数・InstrRev関数で文字列を検索する

Mid関数、Right関数、Left関数についてはこちらを参考にしてください。

エクセルVBA Mid関数・Right関数・Left関数で文字列の一部を切り出す

 

ファイルを保存

Excel帳票、保存3シートをコピーペーストで、新しく作成したエクセルBOOKに移します。

シートも同名にします

行高さ設定

エクセルの仕様として、コピーペーストでは行の高さをコピー先に引き継ぐことはできません。

ですので、

テンプレートを再生するためには、改めて各行の高さ情報をコードにセットする必要があります。

 

印刷余白の設定

印刷余白についてもコピー先には引き継がれません。

印刷余白も改めてコードをセットします。

 

プリントタイトル

プリントタイトルも再設定します。

 

保存ファイルに名前を付ける

基本的に名前は自動で振られる設定にしています。

保存するExcel帳票の名付けのルールは、

「1階層下フォルダ名」「2階層下フォルダ名」「3階層下フォルダ名」請求書_年月日-時分

になります。

このケースで説明すると「作成伝票」→「ABショップ」→「CD店」→「EF営業所」であれば、

3denpyohozon001

一階層 2階層 3階層
3denpyohozon002 3denpyohozon003 3denpyohozon004

この様にフォルダ名を使ってファイル名としています。

ファイル名に「時分」を入れているのは、項目追加や項目修正で同名ファイルにならないようにするためのものです。

(追加修正帳票を60秒以内に行うと上書き保存になってしまいます、ご注意を!)

MEMO

Format関数の使い方についてはこの記事を参考にしてください。

エクセルVBA Format関数をテッパン関数にする!実務で頻度の高い書式と使用例

エクセル帳票保存のまとめ

 

単なるエクセルファイルをデータ保存する時は、別に考えなくてもいいですが、

今このテーマで作成しているような、VBA付きのExcel帳票系のものをデータ保存しようとしたときは特に注意してください。

「データを保存するためのExcel帳票は、VBAコードを必ず削除する」ことを対策としてお勧めします。

VBAコードが誤動作してしまってからでは、全てが「あとの祭り」になります。

 

それと、

通常のExcelブック(.xlsx)化して保存しても、保存データではない要らないシートが残る場合は、

予め、Excel帳票のVBAコード組み立ての時点で、保存出力のVBAコードを作っておくことがいいと思います。

 

次回は、今まで作ったVBAコードをコントロールするユーザーフォームを作成します。

実用に耐えるユーザーフォーム作成のコツは具体的実例を教科書に

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAを独学で習得するためのポイントは?良書との出会いは重要

 

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておくといい内容を纏めてみました。

「VBA最速理解」の記事一覧を開く

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

 

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません