ExcelVBAでテンプレートを開いてデータを入力する方法

Excelテンプレートは通常、レポートフレームワークを備えたワークブックであり、多くの場合、関数によってサポートされます。 テンプレート（xltx）は、データで汚染することなく何度も使用できます。 データを入力した後、テンプレートワークブックがxlsxとして保存され、xltx自体の未使用の状態が保持されます。

この演習では、VBAコードを使用してテンプレートを開き、データを入力します。 テンプレート 見つけることができます こちら 使用されているExcelマクロが見つかります こちら.

この記事は、読者が開発者リボンを表示していて、VBAエディターに精通していることを前提としています。 そうでない場合は、Googleの「Excel開発者タブ」または「Excelコードウィンドウ」をご覧ください。

テンプレート

まず、次のように、データ、ピボットテーブル、グラフを入力したダミーテンプレートを作成します。

新しいExcelファイルを開きます。 「Sheet1」の名前を「Chart」に、「Sheet2」の名前を「Data」に変更します。

見出しを含む次のテキストを「データ」タブのD1にコピーします。

以下に示すように、列見出しを含むすべてのデータを選択し、「データ」シートのA1にピボットテーブルを挿入します。

ピボットテーブルをデータソースとして使用して、[グラフ]タブでグラフを作成します。

D2：F15のデータを削除します。 ピボットテーブルのデータ範囲をリセットする必要はありません。 データがない場合でも、データを入力したままにしておきます。  

ブックを「VacancyTemplate」として保存します。xltx。」 マクロブックが存在するサブディレクトリ内。 保存中にExcelからのアラートに「いいえ」と応答します。

Reportsサブディレクトリも必要になります。 例えば：

Excelレポート （ここに保存されているxlsm）

| _テンプレ （xlxtはここに保存されます）

       | _レポート （ここに保存された各xlsx)

として保存されたら xltx、テンプレートを閉じます

マクロ

新しいワークブックを開いて、コードを保持します。 「Sheet1」の名前を「Main」に、「Sheet2」の名前を「Database」に変更します。

「メイン」にボタンを配置して、アプリケーションを駆動します。

通常、データはデータベースから取得されます。 誰もがデータベースを手元に置いているわけではないので、「データベース」シートはデータベーステーブルをエミュレートします。

で見つかったデータをコピーしますtarこの記事のtをA1の「データベース」タブに…

コード

以下のコード構造は、プロセスを明確に定義しています。

• 「データベース」からデータを取得します。
• テンプレートを開きます。
• テンプレートにデータを入力し、ピボットテーブルのデータ範囲をリセットします。
• テンプレートをレポートとして保存する

Option Explicit
    'Create objects to represent the template workbook and worksheets
    Public wb As Object
    Public XL As Object
    Public connDB As New ADODB.Connection
    Public rs As ADODB.Recordset
    Public eRow As Integer
    Public eRec As Integer
    Public dDate As String

Sub openWorksheet()
    Call GetData
    Call OpenTemplate
    Call PopulateTemplate
    
    'Save template as datestamped xlsx
    On Error Resume Next
    dDate = Format(Now(), "yyyy.mm.dd")
    wb.SaveAs Filename:=ActiveWorkbook.Path & "\Reports\Vacancies" & dDate & ".xlsx", _
         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

     Sheets("Main").Activate        'Move off the database tab
     wb.Activate            'Bring the chart to the fore
     Set wb = Nothing
     Set XL = Nothing
     Set rs = Nothing
     Set connDB = Nothing
End Sub

Sub GetData()
    'Emulate database retrieval
    If connDB.State = 1 Then connDB.Close
    Sheets("Database").Activate
    Sheets("Database").Range("A1").Select
    Selection.End(xlDown).Select
    
    eRec = ActiveCell.Row - 1 'establish how many records will be in the recordset
                     'This step won't be needed in a database environment
    eRow = ActiveCell.Row   'the end row, used later in the template
    
    connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Data Source=" & ActiveWorkbook.FullName & ";" & _
      "Extended Properties=Excel 12.0;"
        
    Set rs = New ADODB.Recordset
    rs.Open "Select top " & eRec & " * from [Database$]", connDB, , , adCmdText
End Sub

 Sub OpenTemplate()
    Set XL = CreateObject("Excel.Application")
    XL.Visible = True       'enables us to see what's happening on debug.
    XL.Workbooks.Add ActiveWorkbook.Path & "\Templates\VacancyTemplate.xltx"
    Set wb = XL.ActiveWorkbook             'the new workbook is referenced by "wb"
 End Sub
 
 Sub PopulateTemplate()
    wb.Sheets("Data").Activate
    wb.Sheets("Data").Range("D2").CopyFromRecordset rs
    wb.Sheets("Data").Range("A1").Select
    
    'resize the range driving the pivot table, using the eRow variable.
    wb.Sheets("Data").PivotTables("PivotTable1").ChangePivotCache wb. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C4:R" & eRow & "C6", _
        Version:=xlPivotTableVersion14)
    wb.Sheets("Chart").Select

 End Sub

ActiveXデータオブジェクト

データベースの読み取りをシミュレートするには、Active Xlibを参照する必要がありますrary。 これは、コードウィンドウの[ツール]> [参照]から実行します。

コードをテストする

「メイン」のボタンをに割り当てます サブOpenworkbook。 ブックを「PopulatingTemplates.xlsm」として保存します。

ブックを閉じて、再度開きます。

ボタンを押して結果を表示します。 「データベース」のデータ行数を増やして再度実行し、チャートが追加情報で更新されているかどうかを確認します。

上記のコードでは、テンプレートを早い段階で示しています。 XL.Visible = True。 ライブ環境では、これは最後に実行できるため、画面の更新は表示されません。

データ災害に対処する！

非常に開発されたExcelファイルがクラッシュし、ソースファイルが破損し、バックアップコピーが利用できないことほど、イライラすることはほとんどありません。 このような場合、Excelが破損したファイルの回復に失敗した場合、そこで行われるすべての作業はlです。ost 便利なツールがない限り Excelを修正 ファイル。

また、貴重な作業を頻繁にバックアップすることも賢明です。

著者紹介：

フェリックスフッカーは、のデータ復旧の専門家です DataNumen、Inc。は、以下を含むデータ復旧技術の世界的リーダーです。 rar 修理 およびSQL回復ソフトウェア製品。 詳細については、次のWebサイトをご覧ください。 WWW。datanumen.com