时间戳

首页 > 解决方案 > 打印时间表 PDF

excel - 打印时间表 PDF

问题描述

因此，我的妻子为她的雇主公司管理时间表，并且必须为参与客户 A 的项目 1 的每位员工打印一份 pdf 时间表。例如：

客户 A - 项目 1 - 员工 1 - 时间和费用
客户 A - 项目 1 - 员工 2 - 时间和费用
客户 A - 项目 1 - 员工 3 - 时间和费用
客户 A - 项目 2 - 员工 2 - 时间和费用
客户 A - 项目 2 - 员工 4 - 时间和费用
客户 A - 项目 3 - 员工 3 - 时间和费用
...
客户 B - 项目 1 - 员工 1 - 时间和费用
客户 B - 项目 2 - 员工 2 - 时间和费用
客户B - 项目 2 - 员工 3 - 时间和费用
客户 B - 项目 3 - 员工 1 - 时间和费用
客户 B - 项目 3 - 员工 4 - 时间和费用
...
等等。

我的妻子有一个非常常规的工作流程，老实说不需要太多的手动操作，所以我试图弄清楚如何自动化/简化她的工作流程，这样她就可以花更多的时间陪伴我们的儿子。

她最多可以有 10 个客户，每个客户最多可以有 20 个活动项目，她每个项目最多可以有 10 名员工。她必须每 15 天做一次。她将所有 PDF 编译成客户项目的合并 pdf，然后手动将信息输入 Quickbooks 以创建汇总发票。然后将发票与各个时间表合并。然后，所有发票都会分发给每个客户。这个过程永远重复。

现在我对编码毫无经验，但我相信我比一般人更了解它。对我来说，当室外温度达到 110 度时，我可以做更多的爱好/活动。到目前为止，我所做的是将所有员工时间表和过滤器加载到一个主数据表中并将信息清理到一个主数据表中的 powerquery。我当前的代码“有效”但需要很多时间并且可能效率很低。我的过程总结如下：

  1. 复制员工姓名列
  2. 将员工姓名粘贴到单独的工作表（临时）范围 A1
  3. 删除重复项，因此我有一列唯一名称。
  4. 对于每个名称，将“名称”过滤器应用于我的主数据表。
    1. 复制客户栏
    2. 在温度范围 B1 上粘贴客户端
    3. 删除重复项，这样我就有了一列员工工作的唯一客户。
    4. 对于员工工作的每个客户，将“客户”过滤器应用于我的主数据表。
      1. 复制项目名称栏
      2. 在临时 C1 上粘贴项目名称
      3. 删除重复项，这样我就有了一列员工为客户工作的独特项目。
      4. 对于员工从事的每个项目，将“项目”过滤器应用于主数据表。
        1. 从主数据表中复制过滤数据（仅可见单元格）
        2. 将数据粘贴到预先格式化的工作表（时间表）表中。
        3. 应用过滤器以隐藏时间表表中的所有空白行。
        4. 在预定位置将时间表打印为 pdf。
        5. 取消隐藏时间表表格中的行并清除下一个项目的内容。

我正在寻找有关如何改进我的流程以减少时间、错误和整体结构的建议。我在代码中还没有任何错误处理。对此的建议也会很好，尽管理想情况下你没有错误;)。我的长而丑陋的代码粘贴在下面。我尝试使用变量，但由于某种原因，我得到了错误，所以我只是重写了变量，它就可以工作了....

Public Sub EAS_AutoTS()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsTemp As Worksheet, po As Worksheet, que As Worksheet
Dim tempEmp As Range, tempClient As Range, tempProject As Range
Dim Emp As String, Client As String, Project As String
Dim PathName As String, SvAs As String
Dim e As Integer, c As Integer, p As Integer, ne As Integer, nc As Integer, np As Integer

Application.ScreenUpdating = False
PathName = ActiveWorkbook.Path

'Refresh Connection
wb.Connections("Query - Timesheets").Refresh
'Clear Filter on PrintOut Sheet
Set que = wb.Sheets("Data")
Set po = wb.Sheets("PrintOut")
'Create new sheet "Temp" and set Temp variable
'Sheets.Add After:=Data
'Sheets("Sheet1").Name = "Temp"
wb.Sheets("Temp").Visible = xlSheetVisible
Set wsTemp = wb.Sheets("Temp")
wsTemp.Cells.Clear
'Copy Name Column in query table to Temp Sheet A1
que.Range("A4").Select
que.AutoFilter.ShowAllData
que.Range("tblTS[Name]").Copy
wsTemp.Range("A1").PasteSpecial Paste:=xlPasteValues
'Remove Duplicates in the tempEmp Named Range to result in unique names only
wsTemp.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo
'Create Dynamic Name Range for Employee Names
wb.Names.Add Name:="tempEmp", RefersTo:="=OFFSET(Temp!$A$1,0,0,COUNTA(Temp!$A:$A),1)"
'Set tempEmp = Range("tempEmp")
e = Range("tempEmp").Count

'Begin first for loop. First loop is for all employee names.
For ne = 1 To e
    'Clear all filters in query table
    que.Select
    que.AutoFilter.ShowAllData
    'Apply name to Printout sheet
    po.Range("E1") = wsTemp.Range("A" & ne).Value
    'Apply name filter to query table
    que.Range("tblTS").AutoFilter Field:=1, Criteria1:="=" & wsTemp.Range("A" & ne).Value, Operator:=xlFilterValues
    wsTemp.Range("$B:$C").ClearContents
    'Copy Client Column in query table to Temp Sheet B1
    que.Range("tblTS[Client]").SpecialCells(xlCellTypeVisible).Copy
    wsTemp.Range("B1").PasteSpecial Paste:=xlPasteValues
    'Remove Duplicate clients to result in unique clients only
    wsTemp.Range("$B:$B").RemoveDuplicates Columns:=1, Header:=xlNo
    'Create Dynamic Name Range for Client Names
    wb.Names.Add Name:="tempClient", RefersTo:="=OFFSET(Temp!$B$1,0,0,COUNTA(Temp!$B:$B),1)"
    'Set tempClient = Range("tempClient")
    'Determine number of Clients for the FOR loop.
    c = Range("tempClient").Count
    'Begin second for loop. Second loop is for all employee names.
    For nc = 1 To c
        'Apply client filter to query table
        que.Range("tblTS").AutoFilter Field:=6
        que.Range("tblTS").AutoFilter Field:=5, Criteria1:="=" & wsTemp.Range("B" & nc).Value, Operator:=xlFilterValues
        'Copy Project Column in query table to Temp Sheet C1
        wsTemp.Range("$C:$C").ClearContents
        que.Range("tblTS[Project Name]").SpecialCells(xlCellTypeVisible).Copy
        wsTemp.Range("C1").PasteSpecial Paste:=xlPasteValues
        'Remove Duplicates in the tempProject Named Range to result in unique projects only
        wsTemp.Range("$C:$C").RemoveDuplicates Columns:=1, Header:=xlNo
        'Create Dynamic Name Range for Project Names
        wb.Names.Add Name:="tempProject", RefersTo:="=OFFSET(Temp!$C$1,0,0,COUNTA(Temp!$C:$C),1)"
        'Set tempProject = Range("tempProject")
        'Determine number of Clients for the FOR loop.
        p = Range("tempProject").Count
        'Begin third (final) for loop: Third loop is for project filtering
        For np = 1 To p
            'Apply Project filter to query table
            If p <> 1 Then que.Range("tblTS").AutoFilter Field:=6, Criteria1:="=" & wsTemp.Range("C" & np).Value, Operator:=xlFilterValues
            'Copy query table to Printout sheet
            If po.Range("A3").Value <> "" Then po.AutoFilter.ShowAllData
            po.Range("tblPrint").ClearContents
            Range("tblTS[Date]:tblTS[Total Expenses]").SpecialCells(xlCellTypeVisible).Copy
            po.Select
            po.Range("A3").Select
            po.Range("A3").PasteSpecial Paste:=xlPasteValues
            'Filter (hide) blank cells.
            po.Range("tblPrint").AutoFilter Field:=1, Criteria1:="<>"
            'Print PDF to folder
            po.Range("W1").NumberFormat = "@"
            po.Range("W1").Value = Format(po.Range("W1").Value, "m-d-yy")
            SvAs = PathName & "\PDFs\" & po.Range("D3") & "_" & po.Range("E3") & "_" & po.Range("E1") & "_" & po.Range("W1") & ".pdf"
            po.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SvAs, Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next 'End of third for loop
    Next 'End of second for loop
Next 'End of first for loop
po.Range("A3").Select
po.AutoFilter.ShowAllData
po.Range("tblPrint").ClearContents
po.Range("E1").Value = ""
po.Range("W1").Value = ""
wsTemp.Range("$A:$C").ClearContents
wsTemp.Visible = xlSheetHidden
e = 0
c = 0
p = 0
ne = 0
nc = 0
np = 0
que.Select
que.AutoFilter.ShowAllData
Application.ScreenUpdating = True
MsgBox "Timesheets have been created at the following location:" & vbNewLine & PathName & "\PDFs", _
    vbOKOnly, "Finished!"
End Sub

标签： excelvbaworkflowexcel-automation

解决方案

嗯，你真的会从 PowerQuery 中受益。

所有范围的东西和选择和清除，我的意思是你需要学习如何从根本上用变量编码来改进它。

但我会说，如果你正在整理表格并进行排序，PQ 有一个很棒的可视化编辑器和可点击的步骤，任何人，包括你的妻子都可以学习。

将 VBA 与重要的其他人一起使用是一个滑坡，最终，错误和问题将影响这种关系。PQ 将在这里成为您的菲尔博士，让每个人都过上自己最好的生活。

请参阅 WillWulliems 在此处回复有关如何使其自动化的信息：此处

推荐阅读