Created
November 28, 2023 08:35
-
-
Save kinuasa/738dd402362a472d6c34eea5e6baa8bd to your computer and use it in GitHub Desktop.
Excelファイル転記後にPDF変換するPower Automate for desktopフロー(動作確認:バージョン 2.38 ) 関連Webサイト:https://rpacommunity.connpass.com/event/302505/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET TemplateFilePath TO $'''C:\\Test\\Excel\\Template\\invoice.xlsx''' | |
SET ListFilePath TO $'''C:\\Test\\Excel\\顧客リスト.xlsx''' | |
SET PdfFolderPath TO $'''C:\\Test\\PDF\\請求書''' | |
Workstation.GetDefaultPrinter PrinterName=> DefaultPrinterName | |
Workstation.SetDefaultPrinter PrinterName: $'''Microsoft Print to PDF''' | |
/# ☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★ | |
PDF保存先フォルダーに作成するPDFと同名のファイルがあると処理が停止するため事前に削除 | |
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★#/ | |
Folder.GetFiles Folder: PdfFolderPath FileFilter: $'''*.pdf''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> PdfFiles | |
IF PdfFiles.Count > 0 THEN | |
Display.ShowMessageDialog.ShowMessage Title: $'''ファイルの削除確認''' Message: $'''PDFファイルの保存先フォルダーにすでにファイルが存在します。ファイルを削除して処理を続行してもよろしいでしょうか?''' Icon: Display.Icon.Question Buttons: Display.Buttons.YesNo DefaultButton: Display.DefaultButton.Button1 IsTopMost: True ButtonPressed=> ButtonPressed | |
IF ButtonPressed.ToLower = $'''yes''' THEN | |
File.Delete Files: PdfFiles | |
ELSE | |
EXIT Code: 0 | |
END | |
END | |
/# ☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★ | |
顧客リスト読み込み | |
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★#/ | |
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: ListFilePath Visible: True ReadOnly: True Instance=> ExcelInstance | |
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''顧客リスト''' | |
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow | |
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData | |
Excel.CloseExcel.Close Instance: ExcelInstance | |
/# ☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★ | |
①読み込んだデータを請求書ブックに書き込む | |
②プリンター経由で請求書ブックをPDFに変換 | |
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★#/ | |
LOOP LoopIndex FROM 0 TO ExcelData.RowsCount - 1 STEP 1 | |
/# ☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★ | |
読み込んだデータを請求書ブックに書き込み保存 | |
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★#/ | |
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: TemplateFilePath Visible: True ReadOnly: False Instance=> ExcelInstance | |
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''請求書''' | |
SET CustomerName TO ExcelData[LoopIndex]['顧客名'] | |
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CustomerName Column: $'''B''' Row: 8 | |
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''デスクトップパソコン''' Column: $'''C''' Row: 23 | |
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: ExcelData[LoopIndex]['数量'] Column: $'''D''' Row: 23 | |
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: ExcelData[LoopIndex]['単価'] Column: $'''E''' Row: 23 | |
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance | |
/# ☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★ | |
プリンター経由で請求書ブックをPDFに変換 | |
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★#/ | |
Folder.GetFiles Folder: PdfFolderPath FileFilter: $'''*.pdf''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> BeforePrintFiles | |
Folder.GetFiles Folder: PdfFolderPath FileFilter: $'''*.pdf''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> AfterPrintFiles | |
Workstation.PrintDocument DocumentPath: TemplateFilePath | |
WAIT (UIAutomation.WaitForWindowContent.WindowToContainElementInState Element: appmask['ExcelWindow']['FileName'] State: UIAutomation.State.Enabled) | |
/# ☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★ | |
ファイル名として使用できない文字を事前に削除 | |
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★#/ | |
Text.Replace Text: CustomerName TextToFind: $'''[<>:\\\"\\/\\\\\\|\\?\\*]''' IsRegEx: True IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> CustomerName | |
UIAutomation.PopulateTextField.PopulateTextField TextField: appmask['ExcelWindow']['FileName'] Text: $'''%PdfFolderPath%\\%CustomerName%.pdf''' Mode: UIAutomation.PopulateTextMode.Replace ClickType: UIAutomation.PopulateMouseClickType.SingleClick | |
UIAutomation.PressButton Button: appmask['ExcelWindow']['SaveButton'] | |
/# ☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★ | |
PDF保存先フォルダーのファイル数を使ってPDF化処理待機 | |
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★#/ | |
LOOP WHILE (AfterPrintFiles.Count) <= (BeforePrintFiles.Count) | |
Folder.GetFiles Folder: PdfFolderPath FileFilter: $'''*.pdf''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> AfterPrintFiles | |
WAIT 1 | |
END | |
END | |
/# ☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★ | |
変更した通常使うプリンターを元に戻す | |
☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★#/ | |
Workstation.SetDefaultPrinter PrinterName: DefaultPrinterName | |
# [ControlRepository][PowerAutomateDesktop] | |
{ | |
"ControlRepositorySymbols": [ | |
{ | |
"IgnoreImagesOnSerialization": false, | |
"Repository": "{\r\n\"Screens\": [\r\n{\r\n\"Controls\": [\r\n{\r\n\"AutomationProtocol\": \"uia3\",\r\n\"ScreenShot\": null,\r\n\"ElementTypeName\": \"Edit\",\r\n\"InstanceId\": null,\r\n\"Name\": \"FileName\",\r\n\"SelectorCount\": 1,\r\n\"Selectors\": [\r\n{\r\n\"CustomSelector\": \" > window[Class=\\\"bosa_sdm_XL9\\\"] > window[Class=\\\"#32770\\\"] > pane[Class=\\\"DUIViewWndClassName\\\"] > pane[Class=\\\"DirectUIHWND\\\"] > pane[Class=\\\"FloatNotifySink\\\"] > combobox[Class=\\\"ComboBox\\\"][Name=\\\"ファイル名:\\\"] > edit[Class=\\\"Edit\\\"]\",\r\n\"Elements\": [],\r\n\"Ignore\": false,\r\n\"IsCustom\": true,\r\n\"IsWindowsInstance\": false,\r\n\"Name\": \"DefaultSelector\",\r\n\"Properties\": []\r\n}\r\n],\r\n\"Tag\": null,\r\n\"ScreenshotPath\": null\r\n},\r\n{\r\n\"AutomationProtocol\": \"uia3\",\r\n\"ScreenShot\": null,\r\n\"ElementTypeName\": \"Button\",\r\n\"InstanceId\": null,\r\n\"Name\": \"SaveButton\",\r\n\"SelectorCount\": 1,\r\n\"Selectors\": [\r\n{\r\n\"CustomSelector\": \" > window[Class=\\\"bosa_sdm_XL9\\\"] > window[Class=\\\"#32770\\\"] > button[Class=\\\"Button\\\"][Name=\\\"保存(S)\\\"]\",\r\n\"Elements\": [],\r\n\"Ignore\": false,\r\n\"IsCustom\": true,\r\n\"IsWindowsInstance\": false,\r\n\"Name\": \"DefaultSelector\",\r\n\"Properties\": []\r\n}\r\n],\r\n\"Tag\": null,\r\n\"ScreenshotPath\": null\r\n}\r\n],\r\n\"ScreenShot\": null,\r\n\"ElementTypeName\": \"Window\",\r\n\"InstanceId\": null,\r\n\"Name\": \"ExcelWindow\",\r\n\"SelectorCount\": 1,\r\n\"Selectors\": [\r\n{\r\n\"CustomSelector\": \":desktop > window[Class=\\\"XLMAIN\\\"][Process=\\\"EXCEL\\\"]\",\r\n\"Elements\": [],\r\n\"Ignore\": false,\r\n\"IsCustom\": true,\r\n\"IsWindowsInstance\": false,\r\n\"Name\": \"DefaultSelector\",\r\n\"Properties\": []\r\n}\r\n],\r\n\"Tag\": null,\r\n\"ScreenshotPath\": null\r\n}\r\n],\r\n\"Version\": 1\r\n}", | |
"ImportMetadata": { | |
"DisplayName": "LocalComputer", | |
"ConnectionString": "", | |
"Type": "Local", | |
"DesktopType": "local" | |
}, | |
"Name": "appmask" | |
} | |
], | |
"ImageRepositorySymbol": { | |
"Repository": "{\r\n \"Folders\": [],\r\n \"Images\": [],\r\n \"Version\": 1\r\n}", | |
"ImportMetadata": {}, | |
"Name": "imgrepo" | |
}, | |
"ConnectionReferences": [] | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment