Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Basic Ahk_L COM Tutorial for Excel


  • Please log in to reply
227 replies to this topic
jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
Xl.Sheets(1).Move(ComObjMissing(), Xl.Sheets("Sheet3")) ;// .Move(Before, After)


Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
ComObjMissing

j--hn
  • Members
  • 176 posts
  • Last active: Oct 26 2011 02:42 PM
  • Joined: 16 Apr 2011
I see. I use next&previous property as workaround before but this is much better, thanks again Mickers & jethrow. :D
AHK_Lw 1.1.05, OS: XP SP3, Firefox 3

Andi
  • Members
  • 195 posts
  • Last active: Apr 18 2014 05:03 PM
  • Joined: 11 Feb 2005
how to detect the next free row for writing new input?
The approach with ...End(xlUp.Row)... doesnt work. Any idea whats wrong here? :roll:

WorkBookPath = C:\Program Files\AutoHotkey\SampleData_1.xls
objExcel := ComObjCreate("Excel.Application") ;create a handle to a new excel application
objWorkBook := objExcel.Workbooks.Open(WorkBookPath) ;opens the existing excel table

objExcel.Worksheets(1).Range("A1").Value:= "this text is set by AHK script in cell A1"
objExcel.Worksheets(1).Range("A2").Value:= "this text is set by AHK script in cell A2"
;objExcel.Visible := True

NextFreeRow := objExcel.Worksheets(1).Cells(.Rows.Count, "A").End(xlUp.Row) + 1 ;this row produces a type conflict error 0x80020005
msgbox NextFreeRow: %NextFreeRow%

i := 1
test := objExcel.Worksheets(1).cells(i, 1).Value
while test <> ""
  {
    i++
    test := objExcel.Worksheets(1).cells(i, 1).Value
    ;msgbox %test%
  }
msgbox NextFreeRow: %i%  ;this workaround shows the next free row

objWorkBook.Save
objExcel.Quit
ExitApp


Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
objExcel.Worksheets(1).Cells(1, "A").End(-4121).Select
Works.
.Rows.Count is not correct syntax. Aslo you need a constant which isn't built into ahk (-4121) which stands for down.
Lastly you have to use a method/property at the end to do anything with it.

MSDN

Andi
  • Members
  • 195 posts
  • Last active: Apr 18 2014 05:03 PM
  • Joined: 11 Feb 2005
Thank you Mickers for your help. :D

Instead of using ".select", for me the ".row"-method shows what I need:

NextFreeRow := objExcel.Worksheets(1).Cells(1, "A").End(-4121).Row + 1


hapyman
  • Members
  • 48 posts
  • Last active: Jan 24 2012 08:13 PM
  • Joined: 21 Oct 2009
Hey guys,

Wondering if someone can lend a hand at deciphering these COM methods. I have tried numerous variations of each to no avail.

The first one is printing. See below for the example posted in MS reference:
expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)

X1.ActiveSheet.PrintOut

I've tried it with and without parameters. I would like to use the parameters though so I can specify the printer. That didn't work so I tried to simplify it and it still doesn't work with default printer. I'm thinking I might just setup a simple ahk script to print if this can't work though.

The other is closing a workbook without saving. Reason is I would like to populate certain fields of a spreadsheet print and then close out without saving the changes for the next run. Here is the code from MS and code I tried:

expression.Close(SaveChanges, Filename, RouteWorkbook)

Workbooks("BOOK1.XLS").Close SaveChanges:=False

Tried:
X1.Workbooks("BookName.xls".Close SaveChanges:= False

X1.Workbooks.Close(False)

X1.Workbooks.Close

Any help is greatly appreciated. Thanks!

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
X1.Workbooks("BookName.xls").Close(False)


hapyman
  • Members
  • 48 posts
  • Last active: Jan 24 2012 08:13 PM
  • Joined: 21 Oct 2009
Thanks for the reply. Unfortunately it still is not working. I copied the book name directly from Au3spy so I am not sure what is going on.

The print function is not working either even with using

X1.ActiveSheet.PrintOut


sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008

Unfortunately it still is not working.


Works fine here, perhaps you should restart your PC and try again.

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
This works for me unless you only wanted the active sheet:
Xl.ActiveWorkbook.PrintOut()
Mabye simply adding ()?

hapyman
  • Members
  • 48 posts
  • Last active: Jan 24 2012 08:13 PM
  • Joined: 21 Oct 2009
Thanks

So this one works but obviously prints the whole workbook. Although this is not a problem for now:

Xl.ActiveWorkbook.PrintOut()

These do not work for me:

X1.ActiveSheet.PrintOut()

X1.Workbooks("BookName.xls").Close(False)

Is there an environment variable or something I can try to make sure I have the BookName right? I can't figure out why this is not working right.

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
FileSelectFile, Path

If ErrorLevel

   ExitApp

Xl := ComObjCreate("Excel.Application")

[color=red]Workbook [/color]:= Xl.Workbooks.Open(Path)

Xl.Visible := True



Xl.Range("A1").Value := "Success!!!"



Workbook.Close(true, Path) ; save at original path
:?:

hapyman
  • Members
  • 48 posts
  • Last active: Jan 24 2012 08:13 PM
  • Joined: 21 Oct 2009
Success!

The close function works now. Thank you.

I am trying to figure out why this works and thought maybe I missed something in your original post. Only thing I can see now is that this looks similar to one of your save methods:

2 Save Methods:

XL.ActiveWorkbook.SaveAs(BookName) ;'bookname' is a variable with the path and name of the file you desire
Xl_Workbook := Xl.Workbooks.Open(Path) ;handle to specific workbook
Xl_Workbook.Save() ;quick save already existing file


With that being said could I use this technique with the PrintOut function I am having trouble with as well?

THanks again.

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
FileSelectFile, Path
If ErrorLevel
   ExitApp
Xl := ComObjCreate("Excel.Application")
Workbook := Xl.Workbooks.Open(Path)
Xl.Visible := True

Workbook.PrintOut()
Untested but I believe this should work as well.
Query: do you have multiple workbooks open at the same time?

Check out the Workbook Object Members