Excel - Com, looping through multiple files Topic is solved

[cmikaitis]

The problem:

I work in an engineering firm, and there is a nice lady who keeps track of the shop drawings that come in and go out for all the various projects that are ongoing. Her process, every couple days, is to open EACH of (3) excel files for our different disciplines for EACH project to make sure that there aren't any outstanding - this winds up being over 100 excel files. She has a number of tricks to make this easier on her, but ultimately, several people log them in, so she winds up opening them all just to make sure she isn't missing anything. She does it this way because that's the way she's always done it and I'd like to help make her life easier.

I have it working when I specify a single excel file - See FilePath below. But when I try to put ComObjGet(FullFilePath) in my parsing loop, moving the closing bracket below the message box to the end, I get an 'Error: 0x800401E4 - Invalid syntax'.

I'm open to suggestions to make my code better, but ultimately, I really am just looking for help to get ComObjGet working inside my loop - I think I can handle it from there!

This is not the entirety of my code - I've got an autoexecute section, a GUI, and a function (dateparse) that are not included for clarity. Let me know if they would be helpful.

Code: Select all

FilePath := "U:\scripts\Mech Log.xls" ; example path
FullFilePath = "U:\scripts\Mech Log.xls", "U:\scripts\Elec Log.xls", "U:\scripts\Plumb Log.xls"

Loop, Parse, FullFilePath, `, ;I'm trying to enclose all the code below within this loop, updating all instances of FilePath with FullFIlePath.
{
Msgbox, %A_LoopField%
}

oWorkbook := ComObjGet(FilePath)

X := 6
While X < 50
{
	If % oWorkbook.Sheets(1).Range("D" . X).Value != "" ;Is there a date in the Submitted column
		{
		If % oWorkbook.Sheets(1).Range("F" . X).Value = "" ;Is there also a date in the Returned Column
			{
				SubmittalName := % oWorkbook.Sheets(1).Range("A" . X).Value
				DateIn := % oWorkbook.Sheets(1).Range("D" . X).Value
				DateIn := DateParse(DateIn)
				DaysOld := % DateNow - DateIn
				LV_Add("", SubmittalName, FilePath, DaysOld) ;Creates a list of folders that match.
			}
		}
	X := X + 1
}

[awel20]

SubmittalName := % oWorkbook.Sheets(1).Range("A" . X).Value -> remove the %

If % oWorkbook.Sheets(1).Range("F" . X).Value = "" -> If (oWorkbook.Sheets(1).Range("F" . X).Value = "")
https://autohotkey.com/docs/commands/IfExpression.htm vs. https://autohotkey.com/docs/commands/IfEqual.htm

FullFilePath = "U:\scripts\Mech Log.xls", "U:\scripts\Elec Log.xls", "U:\scripts\Plumb Log.xls" -> Note the difference between = and := https://autohotkey.com/docs/Variables.htm#Intro

Code: Select all

FullFilePath = "U:\scripts\Mech Log.xls", "U:\scripts\Elec Log.xls", "U:\scripts\Plumb Log.xls"
MsgBox % FullFilePath  ; Note that the quotes are included as part of the string.

; Changed quotes and removed space after comma
FullFilePath := "U:\scripts\Mech Log.xls,U:\scripts\Elec Log.xls,U:\scripts\Plumb Log.xls"
MsgBox % FullFilePath

Another way to do it:

Code: Select all

FullFilePaths := ["U:\scripts\Mech Log.xls", "U:\scripts\Elec Log.xls", "U:\scripts\Plumb Log.xls"]

for FileNumber, FilePath in FullFilePaths
{
    MsgBox % "File #: " FileNumber "`nPath: " FilePath
}

HTH

[FanaticGuru]

I'm open to suggestions to make my code better, but ultimately, I really am just looking for help to get ComObjGet working inside my loop - I think I can handle it from there!

My advice is never use ComObjGet. It is kind of a shortcut way to get a workbook handle that can cause more problems than it solves.

In general it is better to get a handle to the application object and then work from there. Many things are going to require this application handle anyways.

Code: Select all

xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := true	; after testing make false to hide operations
SourceFolder := A_Desktop "\Test\"
Loop, Files, %SourceFolder%*.xlsx
{
	if A_LoopFileAttrib contains H,R,S
		continue
	xlWorkbook := xlApp.Workbooks.Open(A_LoopFileLongPath)
	;;; Stuff to do here
	MsgBox % xlWorkbook.Name
	;;;
	xlWorkbook.Close
}
xlApp.Quit()

This loops through all xlsx files in a folder.

This sounds like a great task to automate with AHK. An AHK script could search through a hundred Excel files and create some kind of report in seconds, completely in the background without ever even showing Excel.

FG

[cmikaitis]

In general it is better to get a handle to the application object and then work from there. Many things are going to require this application handle anyways.

I have no idea what this means, but it sure worked!!!! Thank you. It's not perfect, but it does what I want and will make a nice persons days a bit better - thanks to both of you!

Full script below for reference - it's messy, but it works.

Code: Select all

; Globals ======================================================================
#SingleInstance, Force ; Allow only one running instance of the script
#Persistent ; Keep the script permanently running until terminated
#NoEnv ; Avoid checking empty variables for environment variables
#Warn ; Enable warnings to assist with detecting common errors
;#NoTrayIcon ; Disable the tray icon of the script
SendMode, Input ; Method for sending keystrokes and mouse clicks
SetWorkingDir, %A_ScriptDir% ; Set the working directory of the script
SetBatchLines, -1 ; Run the script at maximum speed
SetWinDelay, -1 ; The delay to occur after modifying a window
SetControlDelay, -1 ; The delay to occur after modifying a control
SetTitleMatchMode 2 ;Match partial name.
SetTitleMatchMode, Slow
DetectHiddenText, on

AppName := "Shop Drawing Organizer"
WinTitle := AppName
MainWinWidth := A_ScreenWidth - 100
MainWinHeight := A_ScreenHeight - 100



FormatTime, DateNow,, yyyyMMdd

Gui, +LastFound +Resize +HwndGui1
Gui, Margin, 10, 10
Gui, Add, ListView, % " x" 10 " y+" 10 " w" MainWinWidth - 20 " r" (MainWinHeight-60)/20 " gControlHandler vListViewMain", % "Filename|Location|Days old"
LV_ModifyCol(1, 400)
LV_ModifyCol(2, 300)

Gui, Show, % " w" MainWinWidth " h" MainWinHeight, % WinTitle ;Show the GUI.


	xlApp := ComObjCreate("Excel.Application")
	xlApp.Visible := false	; after testing make false to hide operations



loop, Files, G:\Admin\*, D
{
	SubmittalLog = %A_LoopFileFullPath%\Mech Log.xls,%A_LoopFileFullPath%\Plumb Log.xls,%A_LoopFileFullPath%\Elec Log.xls
	Loop, parse, SubmittalLog, `,
	{
		Loop, Files, %A_LoopField%
		{
			xlWorkbook := xlApp.Workbooks.Open(A_LoopFileLongPath)
			X := 6
			While X < 50
			{
				If % xlWorkBook.Sheets(1).Range("D" . X).Value != "" ;Is there a date in the Submitted column
				{
					If % xlWorkBook.Sheets(1).Range("F" . X).Value = "" ;Is there also a date in the Returned Column
					{
						SubmittalName := % xlWorkBook.Sheets(1).Range("A" . X).Value
						DateIn := % xlWorkBook.Sheets(1).Range("D" . X).Value
						DateIn := DateParse(DateIn)
						DaysOld := % DateNow - DateIn
						LV_Add("", SubmittalName, A_LoopFileLongPath, DaysOld) ;Creates a list of folders that match.
					}
				}
				X := X + 1
			}
		}
	}
	xlApp.Quit
}



ControlHandler:
If A_GuiEvent = DoubleClick ;When they double click on the main form.
{
	FilePath :=
	LV_GetText(FilePath, A_EventInfo, 2)
	run, %FilePath%
}
Return

Guiescape:
Guiclose:
ExitSub:
xlApp.Quit
ExitApp


enter::
{
ExitApp
}

*$+esc::exitapp ; in case of emergency, hit shift escape to exit script
^+f12::reload  ; edit the script, save it, then hit Ctrl-Shift-F12 to restart it.
+f11::listvars ; Displays the script's variables: their names and current contents.
;^F12::Pause






DateParse(str) {
	static e1 = "i)(\d{1,2})\s*:\s*(\d{1,2})(?:\s*(\d{1,2}))?\s*([ap]m)"
		, e2 = "i)(?:(\d{1,2}+)[\s\.\-\/,]+)?(\d{1,2}|(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\w*)[\s\.\-\/,]+(\d{2,4})"
	str := RegExReplace(str, "((?:" . SubStr(e2, 42, 47) . ")\w*)(\s*)(\d{1,2})\b", "$3$2$1", "", 1)
	If RegExMatch(str, "i)^\s*(?:(\d{4})([\s\-:\/])(\d{1,2})\2(\d{1,2}))?"
		. "(?:\s*[T\s](\d{1,2})([\s\-:\/])(\d{1,2})(?:\6(\d{1,2})\s*(?:(Z)|(\+|\-)?"
		. "(\d{1,2})\6(\d{1,2})(?:\6(\d{1,2}))?)?)?)?\s*$", i)
		d3 := i1, d2 := i3, d1 := i4, t1 := i5, t2 := i7, t3 := i8
	Else If !RegExMatch(str, "^\W*(\d{1,2}+)(\d{2})\W*$", t)
		RegExMatch(str, e1, t), RegExMatch(str, e2, d)
	f = %A_FormatInteger%
	SetFormat, Float, 02.0
	d := (d3 ? (StrLen(d3) = 2 ? 20 : "") . d3 : A_YYYY)
		. ((d1 += 0.0) ? d1 : A_DD) . ((d2 := d2 + 0 ? d2 : (InStr(e2, SubStr(d2, 1, 3)) - 40) // 4 + 1.0) > 0 ? d2 + 0.0 : A_MM) . t1 + (t4 = "pm" ? 12.0 : 0.0) . t2 + 0.0 . t3 + 0.0
	SetFormat, Float, %f%
	Return, d
}

[FanaticGuru]

Full script below for reference - it's messy, but it works.

I guess it does not matter if it works but there are some oddities in your code:

Code: Select all

; Odd
If % xlWorkBook.Sheets(1).Range("D" . X).Value != "" ;Is there a date in the Submitted column
; Normal
If (xlWorkBook.Sheets(1).Range("D" . X).Value != "") ;Is there a date in the Submitted column

; Odd
SubmittalName := % xlWorkBook.Sheets(1).Range("A" . X).Value
; Normal
SubmittalName := xlWorkBook.Sheets(1).Range("A" . X).Value

; Odd
e1 = "i)(\d{1,2})\s*:\s*(\d{1,2})(?:\s*(\d{1,2}))?\s*([ap]m)"
; this will literally put the " " marks in the string saved to variable e1

; Odd
f = %A_FormatInteger%
; Normal
f := A_FormatInteger

% normally means evaluated the literal after this as an expression (this is normally only needed with some old legacry command structures)
:= normally means make the variable before this equal to the expression after this (should generally be used for assignments)
= normally means compare the two expressions on each side of this (should generally only be used for comparisions)
= can mean make the variable before this equal to the literal string after this (generally this should be avoided)

It seems like somewhere these syntaxes would make it fail but maybe not. Maybe it is all technically fine just different. Like a mad genius.

Your code has an odd juxtapose of complexe techniques and primative syntax that changes from line to line. I enjoyed unraveling it.

Good job on doing a good deed!

FG