Finding Excel File. Loop, Files Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
User avatar
eblanc
Posts: 71
Joined: 08 May 2019, 14:41

Finding Excel File. Loop, Files

27 Jun 2019, 14:41

Hi Everyone,

I built a COM OBJ script that pulls data value from excel. And it works.

I also have a script that finds a folder directory based on the first characters. It also works!

So now I want to combine them both to make a super mega ultra-powered bundle of scripting that will find my excel file in the database and extract its information.

I won't bore you with the first two scripts.

But here is what I got. It never finds the file, and the second msgbox always returns blank.

Code: Select all

!q::
    inputbox, userinput, Enter Full Project ID, , ,300 ,100
    ; If the user pressed cancel or the string is too short
    if (ErrorLevel || StrLen(userinput) = 0) 
    		return
    FoundFile := ""
    Loop, Files, % "I:\My Drive\Webcam\Webcam - Projects\" SubStr(userinput, 1, 4) "\" SubStr(userinput, 1, 15) "*" "\"SubStr(userinput, 1, 15) "*".xlsl , FD
    {
		
        FoundFile := A_LoopFileFullPath
        break
    }
    if (FoundFile != "")
        Run, % FoundFile
	Else
	msgbox, I didn't find the file.
	;IfExist, % "I:\My Drive\Webcam\Webcam - Projects\" SubStr(userinput, 1, 4) "\" SubStr(userinput, 1, 15) "*" "\" SubStr(userinput, 1, 15) "*".xlsl
	msgbox, %foundfile%
	return
Here are the "rules"

My Database is sorted in this way.

Code: Select all

 4 Digit State Code - 5 Digit project ID - company name - Business Name - address.
Here is an example of what I might put into the user input

Code: Select all

TX15 - 78558 - Big business corporation - the business of typing, inc - 9506 Auto hotkey avenue
State code and project Id are always constant. Business, name, and address ID sometimes is shorter. Because our Google drive doesn't allow names to be too long or it crashes. So most likely Business name is shortened in its file and/or folder.

All projects have an excel sheet attached to it. Here's an example of the path I would be trying to find:

Code: Select all

I:\My Drive\Webcam\Webcam - Projects\TX15\TX15 - 78558 - Big business corporation - business of typing - 9506 Auto hotkey avenue\TX15 - 78558 - Big business corporation - the business of typing.xlsl
if I can find the file, then it should be easy for me to use it as a variable, and feed the full path into the second excel script.

Disclaimer: I'm a newbie. 2nd month. doing AHK. So it's possible very obvious things are completely missed in here. Feel free to point them out. I will absorb all constructive information thrown my way.

Ice cream for everyone who pitches in!
User avatar
rommmcek
Posts: 1470
Joined: 15 Aug 2014, 15:18

Re: Finding Excel File. Loop, Files

27 Jun 2019, 20:26

You have to debug your script! Currently the loop does just one cycle!
Start replacing it with e.g.:

Code: Select all

    Loop, Files, % ... "*".xlsl , FD
        {
            MsgBox % FoundFile := A_LoopFileFullPath
            ;if (expression)
                ;break
        }
If you get expected info, then make corresponding if statement and so on...

P.s.: Je veux des fraises et des cerises!
User avatar
eblanc
Posts: 71
Joined: 08 May 2019, 14:41

Re: Finding Excel File. Loop, Files

28 Jun 2019, 13:19

@rommmcek

I think I know what you mean, Start with full path, and then start adding the variables. Yeah, that's a good way to start. thanks.
User avatar
Xtra
Posts: 2744
Joined: 02 Oct 2015, 12:15

Re: Finding Excel File. Loop, Files

28 Jun 2019, 13:41

.xlsl is not a variable

Try using:

Code: Select all

Loop, Files, % etc... "*.xlsl" , FD
HTH
User avatar
eblanc
Posts: 71
Joined: 08 May 2019, 14:41

Re: Finding Excel File. Loop, Files

28 Jun 2019, 13:58

@Xtra

Well now I know why I wasn't able to use extension. So that is fixed. However I'm not ablet to get it to work with 3 substrings.

I'm able to find it if I put in the full path.

Code: Select all

Loop, files, % "I:\My Drive\Webcam\Webcam - Projects\TX68\TX68 - 58777 - Sat Inc - Lirna Retail - 99999 Highway 290\TX68 - 58777 - Sat Inc - Lirna Retail - 99999 Highway 290.xlsx"
I'm also able to find it if I put in the full path, and "guess" the file name,

Code: Select all

Loop, files, % "I:\My Drive\Webcam\Webcam - Projects\TX68\TX68 - 58777 - Sat Inc - Lirna Retail - 99999 Highway 290\" SubStr(userinput, 1, 15) "*.xlsx" ,FD
but I'm not able to find it if I have to guess folder and file name.

Code: Select all

Loop, files, % "I:\My Drive\Webcam\Webcam - Projects\TX68\" SubStr(userinput, 1, 15) "*" "\" SubStr(userinput, 1, 15) "*.xlsx" ,FD
The final product should look similar to this:

Code: Select all

Loop, Files, % "I:\My Drive\Webcam\Webcam - Projects\" SubStr(userinput, 1, 4) "\" SubStr(userinput, 1, 15) "*" "\"SubStr(userinput, 1, 15) "*.xlsx" , FD

I think i may be getting the wrong syntax? When it starts failing, it returns the "foundfile" path blank.
User avatar
Xtra
Posts: 2744
Joined: 02 Oct 2015, 12:15

Re: Finding Excel File. Loop, Files

28 Jun 2019, 15:34

This needs a space between the quoted string and SubStr() function call:
"\"SubStr(userinput, 1, 15)
User avatar
rommmcek
Posts: 1470
Joined: 15 Aug 2014, 15:18

Re: Finding Excel File. Loop, Files

28 Jun 2019, 16:12

@Xtra: Sharp eye!
@eblanc: Again, you have to debug your script!

Code: Select all

Userinput:="TX68\TX68 - 58777 - Sat Inc - Lirna Retail - 99999 Highway 290\TX68 - 58777 - Sat Inc - Lirna Retail - 99999 Highway 290.xlsx"

MsgBox % SubStr(userinput, 1, 15)
MsgBox % SubStr(userinput, 1, 4)
Maybe this script will help you:

Code: Select all

#NoEnv
#SingleInstance, Force
#Persistent
Gui margin, 3
Gui,Add,Edit,xm ym w950 vSearch gSearch
Gui,Add,ListView,xs r15 w950 vLV1 gRun,Name|Path
Gui,Show ; , AutoSize
return

;~ Esc::
GuiClose:
Gui,SubMit
ExitApp
return

Search:
stop:=1
LV_Delete()
SetTimer,SearchNow,-500
Return

SearchNow:
Gui,SubMit,NoHide
stop:=0, fHit:=""
GuiControl, -Redraw, LV1
Loop Files, % "I:\My Drive\Webcam\Webcam - Projects\" Search ".xlsx" , FD
  if stop
    break
  else LV_Add("", A_LoopFileName, A_LoopFileDir), fHit:=1
  if !fHit
    LV_Add("", "No matches")
;LV_ModifyCol(-1,"Auto Sort")
LV_ModifyCol(1,"Auto Sort")
GuiControl, +Redraw, LV1
stop:=0
Return

Run:
If (A_GuiControlEvent="DoubleClick"){
  LV_GetText(file,A_EventInfo,1),LV_GetText(dir,A_EventInfo,2)
  Run % dir "\" file
}
Gui,+LastFound
ControlFocus,Edit1
Return
Note: Enter * to display all files, etc.!
User avatar
eblanc
Posts: 71
Joined: 08 May 2019, 14:41

Re: Finding Excel File. Loop, Files

28 Jun 2019, 18:41

@rommmcek

I tried the message boxes, and they are correct. You had a different user input. this is what I would be using this as user input.

Code: Select all

TX15 - 78558 - Big business corporation - the business of typing, inc - 9506 Auto hotkey avenue
So the output is correct.

Code: Select all

TX15 - 78558 -

Code: Select all

TX15
I tried your other script. Gui. I couldn't get it to work, gave me an error on can't find the label. I'm not too familiar with Gui yet, so I didn't know how to use it.

I have a feeling is my syntax, (cause I'm pretty dum', with syntax, still) either that, or there is a limitation to the string value not being able to use twice, or something that I am not seeing.

Anywho, thanks for all the help. Have an awesome weekend! I don't have to deal with this now, it'll be a clump waiting for Monday me, when he gets back into the office. That silly bastard, always soo sluggish and bitter....

I'll keep trying debugging, working from simple to complicated. Also, I tried removing spaces. No dice.

Code: Select all

Loop, files, % "I:\My Drive\Webcam\Webcam - Projects\TX15\"SubStr(userinput, 1, 15)"*""\"SubStr(userinput, 1, 15)"*.xlsx" ,FD
User avatar
rommmcek
Posts: 1470
Joined: 15 Aug 2014, 15:18

Re: Finding Excel File. Loop, Files

28 Jun 2019, 19:07

Code: Select all

userinput:="TX15 - 78558 - Big business corporation - the business of typing, inc - 9506 Auto hotkey avenue"

;your attempt
MsgBox, % "I:\My Drive\Webcam\Webcam - Projects\TX15\"SubStr(userinput, 1, 15)"*""\"SubStr(userinput, 1, 15)"*.xlsx" ,FD
;my attempt
MsgBox, % "I:\My Drive\Webcam\Webcam - Projects\TX15\" SubStr(userinput, 1, 15) "*\" SubStr(userinput, 1, 15) "*.xlsx" ;,FD - When, checking with MsgBox you commment FD and the comma!!!!!
Regarding other script, which version of Ahk do you use? My is v1.1.30.01.

Edit: When you're happy with the MsgBox output, you can try if the file(s) exist(s):

Code: Select all

if FileExist("I:\My Drive\Webcam\Webcam - Projects\TX15\" SubStr(userinput, 1, 15) "*\" SubStr(userinput, 1, 15) "*.xlsx")
    MsgBox, File(s) Exist(s)!
else MsgBox, No such File!
User avatar
Datapoint
Posts: 294
Joined: 18 Mar 2018, 17:06

Re: Finding Excel File. Loop, Files

28 Jun 2019, 21:02

eblanc wrote:
28 Jun 2019, 13:58
The final product should look similar to this:

Code: Select all

Loop, Files, % "I:\My Drive\Webcam\Webcam - Projects\" SubStr(userinput, 1, 4) "\" SubStr(userinput, 1, 15) "*" "\"SubStr(userinput, 1, 15) "*.xlsx" , FD

Code: Select all

; eblanc wrote:
; "I:\My Drive\Webcam\Webcam - Projects\TX68\TX68 - 58777 - Sat Inc - Lirna Retail - 99999 Highway 290\TX68 - 58777 - Sat Inc - Lirna Retail - 99999 Highway 290.xlsx"
; Loop, Files, % "I:\My Drive\Webcam\Webcam - Projects\" SubStr(userinput, 1, 4) "\" SubStr(userinput, 1, 15) "*" "\"SubStr(userinput, 1, 15) "*.xlsx" , FD

Loop, Files, % "I:\My Drive\Webcam\Webcam - Projects\" SubStr(userinput, 1, 4) "\" SubStr(userinput, 1, 15) "*", D
{
	Loop, Files, % A_LoopFileFullPath "\" SubStr(userinput, 1, 15) "*.xlsx", F
	{
		MsgBox % A_LoopFileFullPath
		break 2
	}
}
User avatar
eblanc
Posts: 71
Joined: 08 May 2019, 14:41

Re: Finding Excel File. Loop, Files

05 Jul 2019, 17:57

@rommmcek

My ahk version is v1.1.30.03

You have the correct syntax. But I'm not able to find the file.

My "attempt" comes

Code: Select all

"Blank"


your comes

Code: Select all

I:\My Drive\Webcam\Webcam - Projects\TX15\TX15 - 78558 - *\TX15 - 78558 - *.xlsx
which seems completely correct. But I still get message box, no such file!.

Full file path of this file is:

Code: Select all

I:\My Drive\Webcam\Webcam - Projects\TX15\TX15 - 78558 - Big business corporation - the business of typing, inc - 9506 Auto hotkey avenue\TX15 - 78558 - Big business corporation - the business of typing, inc - 9506 Auto hotkey avenue.xlsx
User avatar
eblanc
Posts: 71
Joined: 08 May 2019, 14:41

Re: Finding Excel File. Loop, Files

05 Jul 2019, 18:13

Thanks@Datapoint, I'm a step closer now. The file is found (I even changed the end of the file name a little bit to confirm this!) but It doesn't want open the file.

any ideas?

Also thanks for sharing, it would have never occurred to me to create a second loop, so this is a completely new approach for me. Could you explain to me why two separate loops to find the path? really curious as to what prompted you to figure it out that way.

This is a copy of the script that I'm running. The message box is accurate and the file name is correct. But I'm not able to open it, still gives me the error, "I didn't find the file"

I have a feeling it's going to have to do with that Foundfile variable. To be honest I'm not 100% sure what it's doing.

Code: Select all

!q::
	clipboard = TX15 - 78558 - Big business corporation - the business of typing, inc - 9506 Auto hotkey avenue
    inputbox, userinput, Enter Full Project ID, , ,300 ,100
    ; If the user pressed cancel or the string is too short
    if (ErrorLevel || StrLen(userinput) = 0) 
	;userinput:="TX15 - 78558 - Big business corporation - the business of typing, inc - 9506 Auto hotkey avenue"
    sleep, 500
    FoundFile := ""
	Loop, Files, % "I:\My Drive\Webcam\Webcam - Projects\" SubStr(userinput, 1, 4) "\" SubStr(userinput, 1, 15) "*", D
{
	Loop, Files, % A_LoopFileFullPath "\" SubStr(userinput, 1, 15) "*.xlsx", F
	{
		MsgBox % A_LoopFileFullPath
		break 2
	}
}
    if (FoundFile != "")
        Run, % A_LoopFileFullPath
	Else
	msgbox, I didn't find the file.
	return
User avatar
Datapoint
Posts: 294
Joined: 18 Mar 2018, 17:06

Re: Finding Excel File. Loop, Files  Topic is solved

05 Jul 2019, 19:55

Try it like this. You need to save A_LoopFileFullPath because it will be blank outside the loop.

Code: Select all

!q::
	inputbox, userinput, Enter Full Project ID, , ,300 ,100
	; If the user pressed cancel or the string is too short
	if (ErrorLevel || StrLen(userinput) = 0) 
		MsgBox, No file specified
	;sleep, 500
	FoundFile := ""
	Loop, Files, % "I:\My Drive\Webcam\Webcam - Projects\" SubStr(userinput, 1, 4) "\" SubStr(userinput, 1, 15) "*", D
	{
		Loop, Files, % A_LoopFileFullPath "\" SubStr(userinput, 1, 15) "*.xlsx", F
		{
			; MsgBox % A_LoopFileFullPath
			; Save the file to a variable
			FoundFile := A_LoopFileFullPath
			break 2
		}
	}
	if (FoundFile != "")
		Run, % FoundFile
	Else
		msgbox, I didn't find the file.
return
eblanc wrote:Could you explain to me why two separate loops to find the path?
As far as I know the docs don't explicitly highlight it, but I don't think you can have a wildcard in the parent folders. ie: In this example, X:\A\B.C, you can have a wildcard in the file/folder name (B) and in the extension (C), but it doesn't work in the parent folder (A). All of the examples in the docs have wildcards only in B and C.

If you search the forums for old posts you can probably find some topics that discuss it. Here's one example: https://autohotkey.com/board/topic/26846-wildcard-folders/
User avatar
rommmcek
Posts: 1470
Joined: 15 Aug 2014, 15:18

Re: Finding Excel File. Loop, Files

06 Jul 2019, 03:56

Obviously wild cards work only for file name and file extension. I wasn't sure, so I proposed testing.
Task could be done with RegEx too, but might be much slower:

Code: Select all

Loop, Files, % "I:\My Drive\Webcam\Webcam - Projects\TX15\*.xlsx", R
    if (A_LoopFileFullPath ~= "I:\\My Drive\\Webcam\\Webcam - Projects\\TX15\\TX15 - 78558 - .*\\TX15 - 78558 - .*\.xlsx")
    	MsgBox % A_LoopFileFullPath
User avatar
eblanc
Posts: 71
Joined: 08 May 2019, 14:41

Re: Finding Excel File. Loop, Files

08 Jul 2019, 12:22

Topic is now closed!

We did it! Ice Cream and Oreos for everybody!

That did it @Datapoint , I went into the linked thread and started reading but got a little bit lost with all the back and forth, but your explanation was a lot clearer. Now I just have to remember that for life, that wildcards only work at the end of the directory, be it a folder or a file. but not before. As well as I've learned that you have to save the filepath because it's only temporary.

Now that I have this, I should be able to start patching both scripts. And any following issue will probably be minor. I should be able to figure out given enough time and monkey keyboard typing power!

Big thanks to @rommmcek and @Xtra , who never gave up believing in me!


Sweet. pretty stoked to get this working.

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: No registered users and 142 guests