Excel VBA로 템플릿을 열고 채우는 방법

Excel 템플릿은 일반적으로보고 프레임 워크가있는 통합 문서이며 함수에서 지원하는 경우가 많습니다. 템플릿 (xltx)은 데이터를 오염시키지 않고 계속해서 사용할 수 있습니다. 데이터로 채운 후 템플릿 통합 문서는 xltx 자체의 초기 상태를 유지하면서 xlsx로 저장됩니다.

이 연습에서는 VBA 코드를 사용하여 템플릿을 열고 채 웁니다. 템플릿 찾을 수 있습니다 여기에서 확인하세요 사용 된 엑셀 매크로를 찾을 수 있습니다. 여기에서 확인하세요.

이 문서에서는 독자에게 개발자 리본이 표시되고 VBA 편집기에 익숙하다고 가정합니다. 그렇지 않은 경우 Google "Excel 개발자 탭"또는 "Excel 코드 창"을 사용하십시오.

템플릿

먼저 다음과 같이 데이터, 피벗 테이블 및 차트로 채워진 더미 템플릿을 빌드합니다.

새 Excel 파일을 엽니 다. "Sheet1"을 "Chart"로, "Sheet2"를 "Data"로 이름을 바꿉니다.

제목을 포함한 다음 텍스트를 "데이터"탭의 D1에 복사합니다.

열 머리글을 포함하여 모든 데이터를 선택하고 아래와 같이 "데이터"시트의 A1에 피벗 테이블을 삽입합니다.

피벗 테이블을 데이터 소스로 사용하여 '차트'탭에서 차트를 만듭니다.

D2 : F15의 데이터를 제거합니다. 피벗 테이블 데이터 범위를 재설정 할 필요는 없습니다. 데이터가 없더라도 채워진 상태로 둡니다.  

통합 문서를“VacancyTemplate.xltx.” 매크로 통합 문서가 상주 할 하위 디렉토리에 있습니다. 저장하는 동안 Excel의 경고에 "아니요"로 응답하십시오.

Reports 하위 디렉토리도 필요합니다. 예를 들면 :

엑셀 보고서 (여기에 저장된 xlsm)

| _템플릿 (여기에 저장된 xxlxt)

       | _ 보고서 (각 xlsx가 여기에 저장 됨)

일단 저장되면 xltx, 템플릿 닫기

매크로

코드를 보관할 새 통합 문서를 엽니 다. "Sheet1"의 이름을 "Main"으로, "Sheet2"를 "Database"로 바꿉니다.

애플리케이션을 구동하려면 "Main"에 버튼을 놓습니다.

일반적으로 데이터는 데이터베이스에서 검색됩니다. 모든 사람이 편리한 데이터베이스를 가지고있는 것은 아니기 때문에 "데이터베이스"시트는 데이터베이스 테이블을 에뮬레이트합니다.

s에서 찾은 데이터를 복사합니다.tar이 문서의 내용을 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 X lib를 참조해야합니다.rar와이. 코드 창에서 도구> 참조를 통해이 작업을 수행하십시오.

코드 테스트

"Main"의 버튼을 하위 Openworkbook. 통합 문서를 "Populating Templates.xlsm"으로 저장합니다.

통합 문서를 닫았다가 다시 엽니 다.

버튼을 누르면 결과를 볼 수 있습니다. "데이터베이스"의 데이터 행 수를 늘리고 다시 실행하여 차트가 추가 정보로 업데이트되었는지 확인합니다.

위의 코드에서 우리는 템플릿을 일찍 보여주었습니다. XL.보이는 = 참. 라이브 환경에서 이것은 화면 업데이트가 보이지 않도록 맨 끝에 수행 할 수 있습니다.

데이터 재해에 대처하십시오!

많이 개발 된 Excel 파일 충돌, 원본 파일 손상 및 백업 복사본 사용 불가능보다 더 실망스러운 것은 없습니다. 이러한 경우 Excel에서 손상된 파일을 복구하지 못하는 경우 모든 작업은 다음과 같습니다.ost 편리한 도구가 없다면 Excel 수정 파일.

귀중한 작업을 자주 백업하는 것도 현명합니다.

저자 소개 :

Felix Hooker는 데이터 복구 전문가입니다. DataNumen, Inc.는 다음과 같은 데이터 복구 기술 분야의 세계적 리더입니다. rar 수리 및 SQL 복구 소프트웨어 제품. 자세한 내용은 WWW.datanumen.COM