1

I'm looking to use COM objects within AHKscript to copy/paste rows of data contained in an Excel worksheet into a web form. I'm having trouble with extracting the last row of the data contained in the Excel worksheet so that I can tell the script to loop only over rows that contain data.

I'm using the answer on stackflow here and here to calculate the last row, but having a hard time getting the code to work:

filepath := "C:\.....\test.xlsx"
X1 := ComObjCreate("Excel.Application")  
X1.Visible := 1  
X1.Workbooks.Open(filepath)  
X1.ActiveWorkbook.Sheets("Input").Activate  
Rows := X1.ActiveSheet.Cells.Find(What:="*", After:=X1.ActiveSheet.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox % "value is" . Rows
Return

The code breaks on line 6 (Rows := ...), returning a 0x800200005 - Type mismatch error specifically with the 'Find' method.

What's puzzling me is that:

  1. The code in line 6 works when I remove the SearchOrder and SearchDirection parameters
  2. The code in line 6 works when pasted as a macro in VBA within Excel
  3. The code is similar (excluding some parameters) to what is recorded in VBA by Excel when using the macro recorder

Running Windows 7 Enterprise, MS Excel 2010 and Autohotkey 1.1.16.05.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Sam
  • 11
  • 1

1 Answers1

0

The constants xlByRows and xlPrevious don't mean anything to AutoHotkey, they are blank variables. They need to be assigned numerical values at some point in the script before line 6.

Include these lines somewhere appropriate in the script:
xlByRows := 1
xlPrevious := 2

Source:
Microsoft Excel Constants [Excel 2003 VBA Language Reference]
https://msdn.microsoft.com/en-us/library/office/aa221100(v=office.11).aspx

Note: Constants can be retrieved from within Excel VBA proper (i.e. not via AutoHotkey) by a line such as:
MsgBox xlByRows & " " & xlPrevious.

Note: To view a running AutoHotkey script's variables, you can right-click its tray icon and click Open, then on the menu bar navigate to View, Variables and their contents, and then check for any xl constants not yet assigned a number.

vafylec
  • 965
  • 1
  • 6
  • 23