Jump to content

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

AHK and Excel - Send Data to Excel and make new sheet



  • Please log in to reply
11 replies to this topic
dkstar
  • Members
  • 13 posts
  • Last active: Dec 17 2013 11:32 PM
  • Joined: 08 Oct 2013

Hello,

 

Could someone explain me how I can send data to Excel document (closed) with AHK?

 

Like if I want to send "test" in the cell A2.

 

And same if I want to add a sheet into this Excel document?

 

ie: Add a new sheet named "Data1"

 

All this with an Excel document that is not opened

 

 

Thanks 



Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009

Welcome to the forum dkstar!

Take a look at this post to get basic idea how to work with Excel in AHK. There are also other tutorials available on the forum.
Code below will do what you requested.
Enjoy ;)

FullPath := "C:\Book1.xlsx"	; please adjust full path to your Workbook...
oWorkBook := ComObjGet(FullPath)		; get reference to WorkBook
oWorkbook.Application.Windows(oWorkbook.Name).Visible := 1	; just do it - too long to explain why...

oWorkbook.Worksheets("Sheet1").Range("A2").Value := "Test"	; set "Test" in "Sheet1" sheet, "A2" cell
oSheet := oWorkbook.Worksheets.Add()		; add a new Sheet
oSheet.Name := "Data1"	; name new sheet to "Data1"
oWorkbook.Worksheets("Data1").Range("A2").Value := "Test 2"	; set "Test 2" in "Data1" sheet, "A2" cell
oWorkbook.Close(1)	; save changes and close Workbook
oWorkBook := "", oSheet := ""	; release references
ExitApp

P.S. understand that Excel + AHK + COM is not for beginners...


My Website • Recommended: AutoHotkey Unicode 32-bit • Join DropBox, Copy


dkstar
  • Members
  • 13 posts
  • Last active: Dec 17 2013 11:32 PM
  • Joined: 08 Oct 2013

Thanks for info :)



dkstar
  • Members
  • 13 posts
  • Last active: Dec 17 2013 11:32 PM
  • Joined: 08 Oct 2013
✓  Best Answer
.


corjamjon
  • Members
  • 44 posts
  • Last active: Dec 23 2014 07:55 PM
  • Joined: 23 Jan 2014

I found this to be extremely helpful. If I wanted to create a repetitive action in excel such as using {right},{down}or{tab} how might that look using this same process?



Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009

To answer your private message and post above, here's an example;

oExcel := ComObjCreate("Excel.Application"), oExcel.Visible := 1, oExcel.Workbooks.Add

F1::oExcel.ActiveCell.Offset(0, 1).Select	; right
F2::oExcel.ActiveCell.Offset(1, 0).Select	; down

My Website • Recommended: AutoHotkey Unicode 32-bit • Join DropBox, Copy


corjamjon
  • Members
  • 44 posts
  • Last active: Dec 23 2014 07:55 PM
  • Joined: 23 Jan 2014

Awesome thank you so much! In the name of being self-sufficiant how does the (0,1) get me to go right in the workbook? what if i wanted to go left or up?

Thanks again for your time!

 

 



corjamjon
  • Members
  • 44 posts
  • Last active: Dec 23 2014 07:55 PM
  • Joined: 23 Jan 2014

FYI:I am also having trouble filling the cell with data after I get it to select the cell to the right... After I learn this I am going to be dangerous! :)



Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009

In the name of being self-sufficiant how does ...

Read this:

oExcel := ComObjCreate("Excel.Application"), oExcel.Visible := 1, oExcel.Workbooks.Add

F1::oExcel.ActiveCell.Offset(0, 1).Select	; right
F2::oExcel.ActiveCell.Offset(1, 0).Select	; down
F3::oExcel.ActiveCell.Offset(0, -1).Select	; left
F4::oExcel.ActiveCell.Offset(-1, 0).Select	; up

My Website • Recommended: AutoHotkey Unicode 32-bit • Join DropBox, Copy


corjamjon
  • Members
  • 44 posts
  • Last active: Dec 23 2014 07:55 PM
  • Joined: 23 Jan 2014

Learning one:

 

Dude! you are a huge help! I love the fact that you gave me some links to read up on. I have already picked up a lot from them thanks a million!



corjamjon
  • Members
  • 44 posts
  • Last active: Dec 23 2014 07:55 PM
  • Joined: 23 Jan 2014

L1,

After combing through the files you gave me to read, I've learnt alot. However, there was some lost in the jargon that I need you to clear up for me. How do you move to, and select a cell when you already have an excel spreadsheet template that you want to work off of. I know I have to use ComObjGet as opposed to the ComObjCreate. I just dont know how to identify to my template using your examples.

 

Thanks for your patients! #learning



Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009

To answer your private message and post above;

Get cell's address and than you can select it, move it, and do various stuff with it.

 

Read about Range Object Members on msdn.

Selecting - examples

oExcel.Range("B2").Select      ; select B2 cell
oExcel.Range("B2:D4").Select   ; select cells from B2 to D4
oExcel.Cells.Select            ; select all

Moving - example using Cut method

oExcel.Range("A1").Cut(oExcel.Range("B2"))

Moving - example using "get,empty,set" value method

CellValue := oExcel.Range("A1").Value
oExcel.Range("A1").Value := ""
oExcel.Range("B2").Value := CellValue

 


My Website • Recommended: AutoHotkey Unicode 32-bit • Join DropBox, Copy