is there more professional way to write this Topic is solved

[Barney15]

Hi
is there more professional way to write this:
+ how can i skip the first 3 Rows ?
thank you

Code: Select all

Loop, read, Class2019-2020.xlsx
{
    Loop, parse, A_LoopReadLine, %A_Tab%
    {
		Arr_Column:=StrSplit(A_LoopReadLine, A_Tab)
		Send, % Arr_Column[1] "{Enter}"
		Sleep,1000
		Send, % Arr_Column[2] "{Enter}"
		Sleep,1000
		Send, % Arr_Column[3] "{Enter}"
		msgbox, Copy Next Name
    }
}

Each time it loops
Send Array1 enter
Send Array2 enter
Send Array3 enter
Wait for MsgBox Ok to send next 3 arrays from excel file

[Delta Pythagorean]

Hi, Barney!

I'm not super, duper amazing at programming, but I do know my way around AHK quite a bit .
If this doesn't work or if this isn't adequite for your needs, please do send a reply my way and someone (or I) get you going!

Code: Select all

Clear := False
Loop, Read, Class2019-2020.xlsx
{
	Line := A_Index
	Loop, Parse, A_LoopReadLine, % A_Tab
	{
		If (Line == 1)											; If the line is the first line, then skip it.
			Continue
		Arr_Column := StrSplit(A_LoopReadLine, A_Tab)
		Loop, 3
		{
			SendInput, % Arr_Column[A_Index] . "{Enter}"
			Sleep, 1000
		}
		MsgBox, 0x1,, Copy Next Name, 3							; Wait 3 seconds, if no input, then close the message box and continue.
		IfMsgBox, Cancel, {										; If you clicked Cancel, then stop reading.
			Clear := True
			Break
		}
	}
} Until (Clear == 1)
MsgBox, 0x0, Stopped reading!, The file was either fully read or you canceled it., 10

Hope this helps!

Cheers, Delta.

[Spark]

Hello,
You can use COM EXCEL
try

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
XL := Excel_Get()
F1:: ; Press F1 to Start
	XL.Range("A3").select ; start from A3
	Loop,
	{
		Name := XL.ActiveCell.Offset(0,0).text
		Seat := XL.ActiveCell.Offset(0,1).text
		Mark := XL.ActiveCell.Offset(0,2).text
        MsgBox, % "Name = " Name "`r`nSeat = " Seat "`r`nMark = " Mark ; Demo mode, you can disable this and enable 3 line below
		;~ Send, %Name% {Enter}
		;~ Send, %Seat% {Enter}
		;~ Send, %Mark% {Enter}
		XL.ActiveCell.Offset(1,0).Select
		BlankData := XL.ActiveCell.text
		if (BlankData = "") {
			break
            MsgBox, Done
        }
	}
return

; This script demonstrates using the Excel_Get function to get a reference to the active Excel application. Excel_Get 
; has a nice feature where it will exit edit-mode if you happen to be editing a cell when the function is called. 
/* excel get example
F7::  ; Press F7 to display Excel's caption and the name of the active workbook.
    xlApp := Excel_Get()
    if !IsObject(xlApp)  ; If Excel_Get fails it returns an error message instead of an object.
    {
        MsgBox, 16, Excel_Get Error, % xlApp
        return
    }
    MsgBox, % "Caption: " xlApp.Caption "`n"
            . "Workbook: " xlApp.ActiveWorkbook.Name
return

Esc::ExitApp  ; Press Escape to exit this script.
*/
; Excel_Get by jethrow (modified)
; https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
Excel_Get(WinTitle:="ahk_class XLMAIN", Excel7#:=1) {
    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
    WinGetClass, WinClass, %WinTitle%
    if !(WinClass == "XLMAIN")
        return "Window class mismatch."
    ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
    if (ErrorLevel)
        return "Error accessing the control hWnd."
    VarSetCapacity(IID_IDispatch, 16)
    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
    if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
        return "Error calling AccessibleObjectFromWindow."
    window := ComObject(9, pacc, 1)
    if ComObjType(window) != 9
        return "Error wrapping the window object."
    Loop
        try return window.Application
        catch e
            if SubStr(e.message, 1, 10) = "0x80010001"
                ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%
            else
                return "Error accessing the application object."
}

; References
;   https://autohotkey.com/board/topic/88337-ahk-failure-with-excel-get/?p=560328
;   https://autohotkey.com/board/topic/76162-excel-com-errors/?p=484371
;   https://autohotkey.com/boards/viewtopic.php?p=134048#p134048
;   Excel_Get forum thread - https://autohotkey.com/boards/viewtopic.php?f=6&t=31840

HTH

[flyingDman]

Actually, Loop, read should not even work on excel files...

[Barney15]

thank you "Delta Pythagorean"
thank you "Spark"

[Barney15]

you too "flyingDman", i missed your comment at first.

[flyingDman]

@Spark's script should work just fine but remember that the larger the spreadsheet and the more COM calls, the slower this gets. Up to a couple of hundred rows like in your spreadsheet should be fine, but if it gets much larger, this type of script might be too slow. Instead of all those COM calls, you can use a single COM call to copy the entire "usedrange" onto the clipboard. The clipboard can then be parsed much faster. Something like this:

Code: Select all

Xl := ComObjActive("Excel.Application")
xl.range("a1").currentregion.copy
data := Rtrim(clipboard,"`n")
loop, parse, data, `n, `r
	if (a_index > 2)
		msgbox % strsplit(a_loopfield,"`t").1 "`t" strsplit(a_loopfield,"`t").2 "`t" strsplit(a_loopfield,"`t").3

[Spark]

@Spark's script should work just fine but remember that the larger the spreadsheet and the more COM calls, the slower this gets. Up to a couple of hundred rows like in your spreadsheet should be fine, but if it gets much larger, this type of script might be too slow. Instead of all those COM calls, you can use a single COM call to copy the entire "usedrange" onto the clipboard. The clipboard can then be parsed much faster. Something like this:

Code: Select all

Xl := ComObjActive("Excel.Application")
xl.range("a1").currentregion.copy
data := Rtrim(clipboard,"`n")
loop, parse, data, `n, `r
	if (a_index > 2)
		msgbox % strsplit(a_loopfield,"`t").1 "`t" strsplit(a_loopfield,"`t").2 "`t" strsplit(a_loopfield,"`t").3

@flyingDman , thanks for your correction

[flyingDman]

Not a correction... merely a different way to do things.