UniX24 Posted September 6, 2018 Posted September 6, 2018 Hello everybody, I work in an engineering office and we draw up plans and put these plans on our server in a structured way. We create a Planlist of our Files like this: Excel Tabelle - From A / 6, the name of the PDF file should be entered. - For Index, the index should be read from the file name. Example: File name is "EP-03061-GR-DB-00-c", <- at the end of the name is the index and should be entered in the Excel table under "Index". - Date can be left out because it's not about the creation date, but when the plan was drawn and you can not read that from a PDF. - Scale can also be omitted, as it is also only in the header in the PDF. - If it is possible I would like to read the format from the PDF and insert it into Excel in "Format". (if it does not matter also the automatic detection, that if the format should be "210x297", that is entered in excel A4) Here a Table : A0 841 x 1189 A1 594 x 841 A2 420 x 594 A3 297 x 420 A4 210 x 297 - For caption text, nothing has to go first, as it will not be easy and I will make a new thread if I can not do it myself. So I want to write a program that automatically fills in the plan list. For this I have in the menu the function "Set Folder". This will enter the path to the project folder. The path is saved here in a .ini Bin\Sys\cfg\path.ini [Config] Path=I:\RRI\Projekte\02863_WHG & EKZ_Köln-Kalk\ The following path is then always the same: Elektro : "18_Zeichnungen\3_RRI\1_Elektro\1_Grundrisse" "18_Zeichnungen\3_RRI\1_Elektro\2_Schemen" Heizung : "18_Zeichnungen\3_RRI\2_Heizung\1_Grundrisse" "18_Zeichnungen\3_RRI\2_Heizung\2_Schemen" Lüftung : "18_Zeichnungen\3_RRI\3_Lüftung\1_Grundrisse" "18_Zeichnungen\3_RRI\3_Lüftung\2_Schemen" Sanitär : "18_Zeichnungen\3_RRI\4_Sanitär\1_Grundrisse" "18_Zeichnungen\3_RRI\4_Sanitär\2_Schemen" Sprinkler : "18_Zeichnungen\3_RRI\5_Sprinkler\1_Grundrisse" "18_Zeichnungen\3_RRI\5_Sprinkler\2_Schemen" As you can see in the source code I have solved this so that I copy my Excel template into the export folder and would like to edit the file afterwards. Finally, I wanted to export again the plan lists in the 5 different trades. Everything that was found in the directory Elektro is written in Excel, in D / 4 Elektro is entered and saved as an Excel file. All this in the 5 trades, so there are 5 excel files (unless a trades have been skipped, then the spreadsheet for the trades is left out) Here is my source code: expandcollapse popup#include <File.au3> #include <Excel.au3> #Include <Array.au3> #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <ProgressConstants.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <MsgBoxConstants.au3> #include <GuiMenu.au3> #include <FileListToArrayRecursive.au3> $ini = @ScriptDir & "\Bin\Sys\cfg\path.ini" Local $SourceFolder = "Bin\Sys\excl\file\" Local $DestinationFolder = "Export\" ; Form1. $Form1 = GUICreate("RRI Planlisten Exporter 2018", 763, 222, 198, 597) $MenuItem1 = GUICtrlCreateMenu("&File") $MenuItem6 = GUICtrlCreateMenuItem("Set Folder", $MenuItem1) $MenuItem4 = GUICtrlCreateMenuItem("Save", $MenuItem1) $MenuItem3 = GUICtrlCreateMenuItem("Exit", $MenuItem1) $MenuItem5 = GUICtrlCreateMenu("&Update") $MenuItem7 = GUICtrlCreateMenuItem("Check", $MenuItem5) $MenuItem2 = GUICtrlCreateMenu("&?") $MenuItem8 = GUICtrlCreateMenuItem("Info", $MenuItem2) $Pic1 = GUICtrlCreatePic("Bin\Sys\img\EX_BG_2018.jpg", -280, 0, 865, 225) $Progress1 = GUICtrlCreateProgress(16, 168, 550, 17) $Button1 = GUICtrlCreateButton("Export", 592, 160, 161, 33) $ListText = GUICtrlCreateList("", 16, 8, 297, 147) $Radio1 = GUICtrlCreateRadio("Tenbrinke", 600, 32, 145, 33) GUICtrlSetState(-1, $GUI_CHECKED) GUICtrlSetFont(-1, 11, 800, 0, "MS Sans Serif") GUICtrlSetCursor (-1, 0) $Radio2 = GUICtrlCreateRadio("Reim und Reimers", 600, 64, 145, 25) GUICtrlSetFont(-1, 11, 800, 0, "MS Sans Serif") GUICtrlSetCursor (-1, 0) $Label1 = GUICtrlCreateLabel("Planschlüssel ", 600, 8, 118, 24) GUICtrlSetFont(-1, 12, 800, 4, "MS Sans Serif") GUISetState(@SW_SHOW) ; Form1 end. ; Form2. $Form2 = GUICreate("Project path selection", 502, 83, 615, 493) $invoer = GUICtrlCreateInput("Projekt Path here", 24, 16, 449, 21) $voerin = GUICtrlCreateButton("Save Path", 24, 48, 91, 25) $Button3 = GUICtrlCreateButton("Exit", 392, 48, 83, 25) GUISetState(@SW_HIDE) ; Form2 end. ; Loop until the user exits. While 1 $nMsg = GUIGetMsg(1) Switch $nMsg[1] Case $Form1 Switch $nMsg[0] Case $GUI_EVENT_CLOSE, $MenuItem3 Exit Case $MenuItem6 GUISetState(@SW_SHOW, $Form2) GUICtrlSetData($invoer, IniRead($ini, "Config", "Path", "")) Case $MenuItem8 MsgBox($MB_SYSTEMMODAL, "Info", "Version 0.3 // Made By Adam Gabler") Case $MenuItem7 MsgBox($MB_SYSTEMMODAL, "Update Check", "Zurzeit kein Update verfügbar") Case $Button1 , $MenuItem4 GUICtrlSetData($ListText, "Loading...") GuiCtrlSetData($Progress1, 25) Sleep(800) _ExportData() GuiCtrlSetData($Progress1, 44) GUICtrlSetData($ListText, "") GUICtrlSetData($ListText, "Loading...") GUICtrlSetData($ListText, "Create a Excel file") Sleep(1800) GuiCtrlSetData($Progress1, 88) GUICtrlSetData($ListText, "") GUICtrlSetData($ListText, "Loading...") GUICtrlSetData($ListText, "Create a Excel file") GUICtrlSetData($ListText, "") GuiCtrlSetData($Progress1, 100) GUICtrlSetData($ListText, "Finish! The file is saved") Sleep(800) EndSwitch Case $Form2 Switch $nMsg[0] Case $GUI_EVENT_CLOSE GUISetState(@SW_HIDE, $Form2) Case $Button3 GUISetState(@SW_HIDE, $Form2) Case $voerin IniWrite($ini, "Config", "Path", GUICtrlRead($invoer)) EndSwitch EndSwitch WEnd Func _ExportData() Local $Array = _FileListToArrayRecursive($SourceFolder, "excel.xlsx", 1, 1) If Not IsArray($Array) Or Not $Array[1] Then Exit Msgbox(4096, 'No Files Found', 'Cannot Find any Files in ' & $SourceFolder) Local $CopyArray[$Array[0] + 1][2] $CopyArray[0][0] = $Array[0] For $i = 1 to $Array[0] $CopyArray[$i][0] = $Array[$i] $CopyArray[$i][1] = StringRegExpReplace($Array[$i], StringReplace($SourceFolder, "\", "\\"), StringReplace($DestinationFolder, "\", "\\")) Next For $i = 1 to $CopyArray[0][0] FileCopy($CopyArray[$i][0], $CopyArray[$i][1], 9) Next EndFunc I am still a beginner, this is my first program with Autoit and until this time I managed alone. I also realize that the code .. Case $Button1 , $MenuItem4 GUICtrlSetData($ListText, "Loading...") GuiCtrlSetData($Progress1, 25) Sleep(800) _ExportData() GuiCtrlSetData($Progress1, 44) GUICtrlSetData($ListText, "") GUICtrlSetData($ListText, "Loading...") GUICtrlSetData($ListText, "Create a Excel file") Sleep(1800) GuiCtrlSetData($Progress1, 88) GUICtrlSetData($ListText, "") GUICtrlSetData($ListText, "Loading...") GUICtrlSetData($ListText, "Create a Excel file") GUICtrlSetData($ListText, "") GuiCtrlSetData($Progress1, 100) GUICtrlSetData($ListText, "Finish! The file is saved") Sleep(800) .. is pretty bad but I did not know now synonymous as I can see the text with each other (as in an installation). I hope it is not too much to ask and that I have expressed myself understandable otherwise just ask. I thank you for your efforts
FrancescoDiMuro Posted September 6, 2018 Posted September 6, 2018 Hi @UniX24 21 minutes ago, UniX24 said: File name is "EP-03061-GR-DB-00-c", <- at the end of the name is the index and should be entered in the Excel table under "Index". For this, I suggest you StringSplit(), using "-" as delimiter, and taking the last index of the array returned by the function. Something like this: Spoiler Global $strFileName = "EP-03061-GR-DB-00-c", _ $arrFileNameSplitted ; Use of StringSplit() $arrFileNameSplitted = StringSplit($strFileName, "-", BitOR($STR_NOCOUNT, $STR_ENTIRESPLIT)) ; Use of StringSplit() - Shortest way ConsoleWrite("Index: " & StringSplit($strFileName, "-", BitOR($STR_NOCOUNT, $STR_ENTIRESPLIT))[5] & @CRLF) ; If the String has ALWAYS 5 "-" ConsoleWrite("Index: " & $arrFileNameSplitted[5] & @CRLF) ; ; If the String has n "-", but the Index is always after the last "-" ConsoleWrite("Index: " & $arrFileNameSplitted[UBound($arrFileNameSplitted) - 1] & @CRLF) 29 minutes ago, UniX24 said: So I want to write a program that automatically fills in the plan list. For this I have in the menu the function "Set Folder". This will enter the path to the project folder.The path is saved here in a .ini For this one, I suggest you to take a look at Ini* functions, with which you can read/write from/to Ini files 31 minutes ago, UniX24 said: All this in the 5 trades, so there are 5 excel files (unless a trades have been skipped, then the spreadsheet for the trades is left out) For this last one, I suggest you Excel UDF, to manage the creation/population and export of your Excel/PDF files. And, at last, but not the least important, for designing Forms, I suggest you Koda Form Designer. P.S.: The Help file will be almost always your best friend! Click here to see my signature: Spoiler ALWAYS GOOD TO READ: Forum Rules Forum Etiquette
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now