ExcelのVBAを活用して、請求書や見積書、図面などのファイルを自動的に管理するシステムを構築する方法をご紹介します。このシステムでは、ファイルを開いた瞬間にVBAプログラムが自動実行され、指定したフォルダ内のファイル名がセルに区切られて格納されます。これにより、大量のファイルを簡単に探し出し、効率的に管理することが可能になります。
・大量の請求書や見積書、図面ファイルを手動で管理するのが大変だな~
・ファイルを素早く見つけることができず、時間がかかるな。。
・フォルダ内のファイル名が混乱しており、整理が必要だな。。。
こんな質問を解決します。
本ブログの対象者
・Excelの基本的な操作に慣れていて、自動化したい人
・VBAプログラミングに興味がある人
・請求書や見積書、図面ファイルの効率的な管理を望んでいる人
・自動化を通じて作業時間を短縮したい人
・ファイルの整理や検索に手間を感じている人
フォルダ内のファイルを自動で一覧を作成するプログラムを作りました。こんな感じになります。
自動管理システムの作成
大手メーカでも、どこの会社でも書類管理システムはあるものの、使い勝手が悪く、Excelを使った個別の管理など必ず行われております。Grや課、部の単位で簡単にExcelを使ったVBAシステムを作ることができると、チームでの書類管理が楽になるため、今回は簡単なVBAプログラムを紹介します。
今回は、見積書・請求書・図面などの書類の内容を記載するようにします。
まず事前に、下記のような、管理No, 日付け, 書類内容,会社名、ハイパーリンくの表を作成します。
またファイルを下記フォルダに作成します。(フォルダパス:C:\Users\user\デスクトップ\VBAプログラム\書類管理システム\書類元データ)
ファイル名称は、日付_書類内容_会社_の構成でファイル名称を設定するようにします。
※excel VBAで_で区切るようにプログラムを設定するためです。
こんな感じで、ファイル名称を、_で日付け、内容(請求書、見積書、図面など自由でよいです)_会社名で区切ります。
これで準備完了です。これらのファイルを、Excelを開いたときに、自動で、フォルダ内を調べ、Excel Sheetに記載するようにプログラムを作成します。
手順
1. フォルダの指定
まず最初に、請求書や見積書、図面ファイルが保存されているフォルダのパスを指定します。以下のコードを使用して、フォルダパスを設定します。
Dim フォルダパス As String フォルダパス = "指定したフォルダのパス"
2. ファイルの取得とセルへの格納
次に、指定したフォルダ内の各ファイル名を取得し、セルに区切って格納します。以下のコードを使用します。今回は下記のフォルダパスで記載します。このプログラムでは、Workbook_Open()で設定しているため、ファイルw開くと、”ファイルを整理しますか”とポップアップが出るようにしております。
Private Sub Workbook_Open()
' メッセージボックスの表示
Dim response As Integer
response = MsgBox("ファイルを整理しますか?", vbQuestion + vbYesNo, "ファイル整理の確認")
' Yesボタンが押された場合の処理
If response = vbYes Then
' ファイル整理のためのコードを追加する
' 例: 指定したフォルダ内のファイルを自動的に整理する処理
' フォルダの指定
Dim フォルダパス As String
フォルダパス = "C:\Users\user\デスクトップ\VBAプログラム\書類管理システム\書類元データ"
' シートの指定
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)
' ファイルの取得
Dim ファイル名 As String
Dim i As Integer
i = 2 ' B2から開始
ファイル名 = Dir(フォルダパス & "\*.*", vbNormal)
Do While ファイル名 <> ""
' ファイル名を区切ってセルに格納
Dim 分割() As String
分割 = Split(ファイル名, "_")
For j = 0 To UBound(分割)
ws.Cells(i, j + 2).Value = 分割(j)
Next j
' ハイパーリンクの設定
ws.Cells(i, UBound(分割) + 3).Value = "●"
ws.Hyperlinks.Add Anchor:=ws.Cells(i, UBound(分割) + 3), Address:=フォルダパス & "\" & ファイル名
' 次のファイル名を取得
ファイル名 = Dir
i = i + 1
Loop
MsgBox "ファイルが整理されました!"
End If
End Sub
上記のプログラムをVBAに記載します。
こちらのプログラムは下記の内容になっています。
End Sub上記のコードでは、指定したフォルダパスに格納されているファイル名を “_” で区切り、B列から順にセルに格納します。また、最後のセルには「●」を入力し、ファイルのハイパーリンクを設定します。2番目のファイルはB3セルに、3番目のファイルはB4セルに格納されます。
では実際に、Excelファイルを開いてみます。
ファイルを開くと、このようにポップアップが出てきます。
はいを選択すると、
ポップアップにファイルが整理されましたと表示され、フォルダパスを指定した、ファイル名称の一覧がリストに記載されます。
“_”で区切られた文字で、日付け、書類内容、会社が表示され、ハイパーリンクが設定されております。この状態でフィルターを設定すれば、見積書だけのリストを作ることも可能になります。
そのため、フォルダにファイル名称を設定して、保存するだけで、すべてのファイルがリスト化され管理することができます。
もちろん、ハイパーリンクをクリックすると、PDFファイルを開くことができます。
このように、フォルダにファイル名称をルール化して保存する竹で、簡単な管理システムを作成することができます。チームで使用する際には、このような保存が必要になります。
コードがうまくいかない場合
1. Excelファイルを開きます。
2. メニューバーの「開発」タブを選択します。もし表示されていない場合は、「ファイル」→「オプション」→「カスタマイズリボン」で「開発」タブを表示させてください。
3. 「開発」タブの中の「Visual Basic」ボタンをクリックします。
4. 「VBAプロジェクト」ウィンドウが表示されたら、左側の「VBAProject(ファイル名.xlsm)」をダブルクリックします。
5. 「Microsoft Excel オブジェクト」→「This Workbook」をダブルクリックします。
6. コードウィンドウに以下のコードを貼り付けます。
上記の設定を実施してください。
まとめ
以上で設定が完了しました。Excelファイルを開くたびに、VBAプログラムが自動的に実行され、指定したフォルダ内のファイル名がセルに区切られて格納されます。さらに、最後のセルには「●」が表示され、ファイルのハイパーリンクが設定されます。
この自動ファイル管理システムを活用することで、請求書や見積書、図面などのファイルを簡単に探し出し、効率的に管理することができます。VBAの基本的な操作や設定手順に慣れていない初心者の方でも、この記事を参考にしてスムーズにシステムを構築してください。
お役に立てれば幸いです。円滑なファイル管理をお楽しみください!