Followers 0

Beginner Question - Filepath - change letters - search for specific data

5 posts in this topic

#1 ·  Posted

Hey Community,

cause im too new in the Auto it world i will try it with the your help. hopefully.

I woud like to know how i can handle my Problem.

----

I have a Excel Data with 362 random numbers.
For Example:

 1166642335374 1172899897343 ..... this numbers are a part of the filepath ...example D:\Projekte\1166_64233_5374 as u can see its the first number of the Excel data. After the first 4 numbers it shoud make a "_" than another 5 "_" This is my first question. How can i handle this to make it Shell execute.   -------- Second question: If i am in the path. For Example: D:\Projekte\1166_64233_5374 the code shoud search for specific PDF Files. They are named like: 0050569E364B1ED79B900F73E62660EC.pdf the first 15 letters are always the same 0050569E364B1ED when he found this data he has to copy it on a Folder on the Desktop. (There can also be 2 or 3 pdfs in one Folder with this letters) ---- Please give me some help :-)

Share this post

Share on other sites

#2 ·  Posted

Hi @Mag91, and welcome to the AutoIt forum

What you're trying to do is:

1) Read from an Excel worksheet;

2) You have a string, which it's lenght is 13 characters, and, after 4 characters, you have to put an underscore ( "_" ), then, after other 5 characters, you have to put another "_" ( after the 9th character, so );

3) You have to shell these combined paths ( for me, it's not needed the Shell(), and search for PDF files in them, and those PDF files have the first 15 characters which are always the same, copy them to the Desktop folder.

Not so difficult to do

Did you test some code? Show us what you got

1 person likes this

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post

Share on other sites

#3 ·  Posted

Exactly!!!

i know my code is like shit for known Auto it programmers... but here

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("BigBag", 188, 48, 553, 270)$Button1 = GUICtrlCreateButton("Start", 8, 8, 169, 33)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

HotKeySet("{F10}", "stop")

Func STOP()
While 1
Sleep(120)
WEnd
EndFunc

While 2
$nMsg = GUIGetMsg() Switch$nMsg
Case $GUI_EVENT_CLOSE Exit Case$Button1 ;Start

;=============Find Project===============
sleep (2000)

If ProcessExists ("EXCEL.EXE") Then
WinActivate ("BigBag_ab_31.01.17.xlsx - Excel")
EndIf
sleep (500)
Send("{CTRLDOWN}c{CTRLUP}")                                     ;COPY PROJECT NUMBER FROM EXCEL
sleep (500)

ShellExecute ("Z:\Projektdetails\")
WinWaitActive ("Projektdetails", "Projektdetails", 10)
WinMove ("Projektdetails", "", 0, 0, 1000, 750)
sleep (100)
$HWND = WinWaitActive("Projektdetails", "") ControlClick($HWND, "", "[CLASSNN:ToolbarWindow322; INSTANCE:2; ID:1287]", "LEFT", 1)
Send("{CTRLDOWN}v{CTRLUP}")

EndSwitch
WEnd

The code is just form with a start button
It Begins with ctrl c to copy the number from the Excel worksheet.
Then it execute a part of the path and put the copied data behind the path.

i can tell him now to go 4 letters left and set a underscore "_" and another 6 left to set another underscore.

But i know its to simple. i dont want to make him mouseclick or send ("{left}"). i want a better way.

its to superficial (I hope that's the right word) - its not deep enough

for the 3rd Point i have no idea :S

Share this post

Share on other sites

#4 ·  Posted (edited)

First of all, don't say these things

43 minutes ago, Mag91 said:

i know my code is like shit for known Auto it programmers

Everyone starts from the basic, and, within time, improve himself with the right path...

Then, let's talk about the approach you'd like to implement with your script.

I saw that you're opening the Excel worksheet to copy the row of the file to "format" ( xxxx_xxxxx_xxxx ), but, why don't using an _Excel* function?

You already know the name of the Excel file, so you could open it ( without see anything ), load the content of the file in an array ( with _Excel_RangeRead() ), and work on the content of the array, instead of having windows which activate and copy-paste data from program to program.

I can point you to the right direction ( which could be a lot, so my "solution" could not be the best/the only one you have to do... ), but you have to decide how to proceed with your script.

Edited by FrancescoDiMuro
1 person likes this

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post

Share on other sites

#5 ·  Posted

1 hour ago, FrancescoDiMuro said:

First of all, don't say these things

Everyone starts from the basic, and, within time, improve himself with the right path...

Then, let's talk about the approach you'd like to implement with your script.

I saw that you're opening the Excel worksheet to copy the row of the file to "format" ( xxxx_xxxxx_xxxx ), but, why don't using an _Excel* function?

You already know the name of the Excel file, so you could open it ( without see anything ), load the content of the file in an array ( with _Excel_RangeRead() ), and work on the content of the array, instead of having windows which activate and copy-paste data from program to program.

I can point you to the right direction ( which could be a lot, so my "solution" could not be the best/the only one you have to do... ), but you have to decide how to proceed with your script.

Thanks a lot!

will try to handle the first step when im home <3

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Followers 0

• Similar Content

• By e45678mail
Hi~
I have some question about how to enable the test mode via "Run" or "shellexecute" of command,
was to tried :
-----
Run("bcdedit.exe /-set testsinging on", "C:\Windows\System32")
and
shellexecute("bcdedit.exe /-set testsinging on", "C:\Windows\System32")
-----
since this command must to run through administrator competence,
have any method can accomplish it?

• By Nareshm
How to Activate Opened Excel Windows Using Class not Tittle, Because Some time opened defferent excel that have different name.
I Tried with
Winactivate ("[CLASS:XLMAIN]") but not working
• By willichan
Here is another one from my archives that filled a specific need.

Here is the back story if you are interested.

Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks.  It also requires the use of GraphViz to build the graph.
#cs ---------------------------------------------------------------------------- Project Name: ExcelLinksMapper Description: Analyse an Excel file's links and map them out. Creation Date: 9/26/2014 AutoIt Version: Author: willichan Requires: Graphviz (http://graphviz.org/) #ce ---------------------------------------------------------------------------- Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declare Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause Opt("TrayMenuMode", 0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name Global Const$MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app Global$SQLloaded = False Global $sDbName = @ScriptDir & "\db2gv.db" ConsoleWrite($sDbName & @crlf) If _MutexExists($MyMutex) Then Exit #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> #include <array.au3> #include <excel.au3> _ConfigInitialize() _Main() Func _ConfigInitialize() OnAutoItExitRegister("_ConfigDestroy") ;initializers here Global$sSQliteDll = _SQLite_Startup() If @error Then MsgBox(0, "SQLite Error", "could not load the DLL") Global $sSQLiteDB = _SQLite_Open($sDbName) If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database")$SQLloaded =True __CreateTables() EndFunc ;==>_ConfigInitialize Func _ConfigDestroy() ;destructors here If $SQLloaded Then _SQLite_Close() _SQLite_Shutdown() EndIf EndFunc ;==>_ConfigDestroy Func _MutexExists($sOccurenceName) Local $ERROR_ALREADY_EXISTS = 183,$handle, $lastError$sOccurenceName = StringReplace($sOccurenceName, "\", "")$handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName)$lastError = DllCall("kernel32.dll", "int", "GetLastError") Return $lastError[0] =$ERROR_ALREADY_EXISTS EndFunc ;==>_MutexExists Func __CreateTables() _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);") _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);") EndFunc Func _Main() Local $sInfile,$vResult, $iErrLoop$sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2) If Not FileExists($sInfile) Then MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file") Exit EndIf$vResult = $SQLITE_IOERR$iErrLoop = 5 While $vResult =$SQLITE_IOERR $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);") If Not$vResult = $SQLITE_OK Then Sleep(100)$iErrLoop -= 1 If $iErrLoop = 0 Then ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF)$vResult = $SQLITE_OK EndIf WEnd _GetExcelLinks($sInfile) Global $hOutfile = FileOpen(@ScriptDir & "\" &$MyName & ".gv", 2) If $hOutfile = -1 Then MsgBox(0,$MyName & " ERROR", "Unable to upen file for output") Exit EndIf _WriteHeader() _WriteNodes() _WriteLinks() _WriteFooter() FileClose($hOutfile) _GenerateGraph() ShellExecute(@ScriptDir & '\ExcelLinksMapper.png') EndFunc ;==>_Main Func _GetExcelLinks($strFileName) Local $hQuery,$aCount, $iErrLoop,$vResult ConsoleWrite($strFileName & @CRLF) Local$iLoop, $iExists Local$aLinks Local Const $xlExcelLinks = 1 Local$oExcel = _Excel_Open() Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True) Local$err = @error If $err Then If Not IsObj($oExcel) Then ConsoleWrite($ret & " - " &$err & @CRLF) Exit EndIf EndIf $aLinks =$oExcel.ActiveWorkbook.LinkSources($xlExcelLinks) _Excel_BookClose($oExcel, False) _Excel_Close($oExcel, False, True) If IsArray($aLinks) Then If UBound($aLinks) > 0 Then For$iLoop = 0 To UBound($aLinks) - 1 If$aLinks[$iLoop] <>$strFileName Then $iExists = FileExists($aLinks[$iLoop]) ConsoleWrite("DEBUG - Calling WriteNode()") __WriteNode($aLinks[$iLoop],$iExists) ConsoleWrite("DEBUG - Calling WriteLink()") __WriteLink($strFileName,$aLinks[$iLoop]) If$iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop]) EndIf Next EndIf EndIf EndFunc ;==>_GetExcelLinks Func __WriteNode($sName, $iExists) Local$iErrLoop = 5 ;Number of attempts to make Local $vResult Do ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF)$vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");") If Not$vResult = $SQLITE_OK Then Sleep(100)$iErrLoop -= 1 If $iErrLoop = 0 Then$vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until$vResult = $SQLITE_OK EndFunc ;==>__WriteNode Func __WriteLink($sName1, $sName2) Local$iErrLoop = 5 ;Number of attempts to make Local $vResult,$hQuery, $vCount ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF) _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery) ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF) _SQLite_FetchData($hQuery, $vCount) If UBound($vCount) > 1 Then _ArrayDisplay($vCount) If$SQLITE_OK And UBound($vCount) > 1 Then$vCount = $vCount[1] + 1 Else$vCount = 1 EndIf Do If $vCount = 1 Then ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF)$vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " &$vCount & ");") Else ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";") EndIf If Not$vResult = $SQLITE_OK Then Sleep(100)$iErrLoop -= 1 If $iErrLoop = 0 Then$vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until$vResult = $SQLITE_OK EndFunc ;==>__WriteLink ; #FUNCTION# ==================================================================================================================== ; Author ........: SEO <locodarwin at yahoo dot com> ; Modified.......: litlmike, water, GMK, willichan ; =============================================================================================================================== Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath,$bReadOnly = Default, $bVisible = Default,$sPassword = Default, $sWritePassword = Default) If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $bReadOnly = Default Then$bReadOnly = False If $bVisible = Default Then$bVisible = True ;; changing the second parameter on the following line to a 0 tells Excel not to update any links. Local $oWorkbook =$oExcel.Workbooks.Open($sFilePath, 0,$bReadOnly, Default, $sPassword,$sWritePassword) If @error Then Return SetError(3, @error, 0) $oExcel.Windows($oWorkbook.Name).Visible = $bVisible ; If a read-write workbook was opened read-only then return an error If$bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0,$oWorkbook) Return $oWorkbook EndFunc ;==>_Excel_BookOpen_NoUpdate Func _GenerateGraph() RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' &$MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"') EndFunc ;==>_GenerateGraph Func _WriteHeader() __OutLine(0, "digraph main {") EndFunc ;==>_WriteHeader Func _WriteNodes() Local $aResult,$iRows, $iColumns,$iRval Local $iLoop,$sStyle __OutLine(1, "// Nodes") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult,$iRows, $iColumns) If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable") If$iRval = $SQLITE_OK Then For$iLoop = 1 To $iRows If$aResult[($iLoop * 2) + 2] > 0 Then$sStyle = "normal" Else $sStyle = "missing" EndIf __OutNode($aResult[1 + (2 * $iLoop)],$sStyle) ;; Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " &$iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()") Exit EndIf EndFunc ;==>_WriteNodes Func __OutNode($sName,$sStyle = Default) If $sStyle = Default Then$sStyle = "Normal" Switch StringLower($sStyle) Case "missing" __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];') Case Else ;"normal", Default __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];') EndSwitch EndFunc ;==>__OutNode Func _WriteLinks() Local$aResult, $iRows,$iColumns, $iRval Local$iLoop, $sStyle,$aCount __OutLine(1, "// Links") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult,$iRows, $iColumns) If$iRval = $SQLITE_OK Then For$iLoop = 1 To $iRows __OutLink($aResult[1 + (2 * $iLoop)],$aResult[2 + (2 * $iLoop)]) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) Exit EndIf EndFunc ;==>_WriteLinks Func __OutLink($sName1, $sName2,$iWeight = 1) __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';') EndFunc ;==>__OutLink Func _WriteFooter() __OutLine(0, "}") EndFunc ;==>_WriteFooter Func __OutLine($iTabs,$sText) Local $iLoop If$iTabs > 0 Then For $iLoop = 1 To$iTabs FileWrite($hOutfile, " ") ;ConsoleWrite(" ") Next EndIf FileWriteLine($hOutfile, $sText) EndFunc ;==>__OutLine Func __MakeName($sText) Local $sNewName = StringReplace($sText, "\", " ") $sNewName = StringReplace($sNewName, "/", " ") $sNewName = StringReplace($sNewName, "'", " ") $sNewName = StringReplace($sNewName, '"', " ") $sNewName = StringReplace($sNewName, ':', " ") $sNewName = StringReplace($sNewName, '.', " ") $sNewName = StringReplace($sNewName, '-', " ") $sNewName = StringReplace($sNewName, '$', " ")$sNewName = StringStripWS($sNewName, 8) Return StringLower($sNewName) EndFunc ;==>__MakeName
• By Dimmae
Hello,
at first: i'm new here, so please forgive me my mistakes, and show them to me, just that i can learn to do better in the future.
Now to my Problem: i have an excel sheet, where i just need some columns for further actions, but i have no idea how to add single columns to a new array.
I found the following code(the one i just added as a file) from 'water' in this forum, but i wont get how i could add multiple columns into a new array.
The biggest problem in my situation is that i dont know the count of the rows i need for the array, i just got a fix number of rows, which is 4.

Hope you can help me, and sry again for this 'unlucky illustration'.

btw: how can i add code shown as code here, instead of posting it as a attached file?.

autoit-select-column.au3
defects.xlsx
• By Iznogoud
Hi,
I am trying to find more information on generating PDF files from AutoIT and found the UDF topic
Also found some info on creating .FDF file and use PDFTK for merging, but i can't find info about a custom based layout.
What i trying to find out is, if it is possible to generate a variable layout. For an example i created a script which is based out 20 fields and every field can contain information. If one of those fields are not filled, it shouldn't be printed on the PDF either.
To make it more difficult, the PDF should contain a heading, the first 10 fields and then a horizontal line across the PDF and then a new heading and then show the last 10 fields.
But only the fields which are filled.
Is this possible?