Jump to content

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

excel and com?


  • Please log in to reply
17 replies to this topic
cherrygirl
  • Members
  • 6 posts
  • Last active: Nov 25 2011 09:24 AM
  • Joined: 24 Nov 2011
So i'm trying to rewrite this excel macro into autohotkey and i'm having some trouble.

Sub Format()
'
' Format Macro
'

'


ActiveSheet.Columns("A").ColumnWidth = 4
ActiveSheet.Columns("B").ColumnWidth = 16
ActiveSheet.Columns("C").ColumnWidth = 10
ActiveSheet.Columns("D").ColumnWidth = 10
ActiveSheet.Columns("E").ColumnWidth = 40
ActiveSheet.Columns("F").ColumnWidth = 60

    Cells.Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("F:F"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A:M")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

i have this so far to do my sorting
xl := ComObjActive("Excel.Application")
xl.cells.sort(xl.columns(6), 1)
...but i can't wrap my head around changing the column widths. any ideas?

tidbit
  • Administrators
  • 2709 posts
  • Hates playing Janitor
  • Last active: Jan 15 2016 11:37 PM
  • Joined: 09 Mar 2008
many people don't think this library is that useful to use, but more useful to learn from. so maybe you can learn from it? :)
It has a Excel_SetColWidth() function.
<!-- m -->http://www.autohotke...pic.php?t=70421<!-- m -->


edit. aaah. I just missed you on IRC.

rawr. be very afraid
*poke*
. Populate the AutoHotkey city. Pointless but somewhat fun. .


Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009
Press F1
F1::
oSheet := ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet
oSheet.Columns("A").ColumnWidth := 4,  oSheet.Columns("B").ColumnWidth := 16
oSheet.Columns("C").ColumnWidth := 10, oSheet.Columns("D").ColumnWidth := 10
oSheet.Columns("E").ColumnWidth := 40, oSheet.Columns("F").ColumnWidth := 60
oSheet.Cells.Select

oSheet.Sort.SortFields.Clear
oSheet.Sort.SortFields.Add(oSheet.Range("F:F"),0,1,ComObjMissing(),0)
oSheet.Sort.SetRange(oSheet.Range("A:M"))
oSheet.Sort.Header := 1, oSheet.Sort.MatchCase := 0
oSheet.Sort.Orientation := 1, oSheet.Sort.SortMethod := 1
oSheet.Sort.Apply
return


cherrygirl
  • Members
  • 6 posts
  • Last active: Nov 25 2011 09:24 AM
  • Joined: 24 Nov 2011
thanks tidbit. i did have a look at that before i posted, but i couldn't figure out how to sort, which is why i took the other route.


learning one: thank you so much xoxox :) that worked perfectly!

i was wondering. if this hotkey is used outside an excel window, an error pops up. is there any way to disable this function unless excel is the active window so i don't get an error popping up all the time if i accidentally press the hotkey?

dmg
  • Members
  • 2395 posts
  • Last active: Nov 04 2015 06:46 AM
  • Joined: 19 Nov 2010
Some combination of these may do the trick:
<!-- m -->http://www.autohotke... ... eTitle.htm<!-- m -->
<!-- m -->http://www.autohotke... ... chMode.htm<!-- m -->

I apologize for not being more specific. I personally have never had occasion to work with windows in AHk before.
"My dear Mr Gyrth, I am never more serious than when I am joking."
~Albert Campion

-----------------------------------------------------------------------------------------------
Website | Demo scripts | Blog | External contact

cherrygirl
  • Members
  • 6 posts
  • Last active: Nov 25 2011 09:24 AM
  • Joined: 24 Nov 2011
hmmm that looks like my excel file needs to have the same name each time i open it. my excel files that are opened are all different sets of characters. like one could be "DdfjlixihS.xls" and another could be "AJzIlixOOc.xls".

nimda
  • Members
  • 4368 posts
  • Last active: Aug 09 2015 02:36 AM
  • Joined: 26 Dec 2010
But it still ends the same, like "- Microsoft Excel", right? That's what SetTitleMatchMode is for.

cherrygirl
  • Members
  • 6 posts
  • Last active: Nov 25 2011 09:24 AM
  • Joined: 24 Nov 2011
ooh yeah, i didn't realize that! :oops:

F1::
SetTitleMatchMode, 2

IfWinActive, Microsoft Excel
{
oSheet := ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet
oSheet.Columns("A").ColumnWidth := 4,  oSheet.Columns("B").ColumnWidth := 16
oSheet.Columns("C").ColumnWidth := 10, oSheet.Columns("D").ColumnWidth := 10
oSheet.Columns("E").ColumnWidth := 40, oSheet.Columns("F").ColumnWidth := 60
oSheet.Cells.Select

oSheet.Sort.SortFields.Clear
oSheet.Sort.SortFields.Add(oSheet.Range("F:F"),0,1,ComObjMissing(),0)
oSheet.Sort.SetRange(oSheet.Range("A:M"))
oSheet.Sort.Header := 1, oSheet.Sort.MatchCase := 0
oSheet.Sort.Orientation := 1, oSheet.Sort.SortMethod := 1
oSheet.Sort.Apply
}
return

that seems to be working for me. thanks everybody! :D

cherrygirl
  • Members
  • 6 posts
  • Last active: Nov 25 2011 09:24 AM
  • Joined: 24 Nov 2011
actually, after i went to work and tried it there... i get this error... any ideas?

Error: 0x800401E3 - Operation unavailable

Line#
---> 060: oSheet:=
ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet

Continue running the script?

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
Prolly accessing an invisible Excel Process.

:arrow: For all those that post Excel Com code, if you're gonna use ComObjActive, I recommend adding a test to make sure the code accesses the expected Excel Application object.

cherrygirl
  • Members
  • 6 posts
  • Last active: Nov 25 2011 09:24 AM
  • Joined: 24 Nov 2011
hmmm... so i figured out when i get the error. it will only come up when there is only one instance of excel open. if the error pops up, i can click yes to continue with the script, and the hotkey will work. if i keep that spreadsheet open, and go on to open my next one, the hotkey will work without any errors coming up. if i open a third excel file, no error and the hotkey works, and so on. the error only comes up on the first spreadsheet that is opened.

this is an issue with the computer i work at because for some reason, i can only have one instance of excel open at a time. my computer won't let me dl & open another excel file until i close the one that is currently open. so basically, i get this error every time... :x

tank
  • Administrators
  • 4345 posts
  • AutoHotkey Foundation
  • Last active: Oct 13 2016 01:04 AM
  • Joined: 21 Dec 2007
in your origional post i think for assignment of widths your missing a colon
ActiveSheet.Columns("F").ColumnWidth [color=red]:[/color]= 60
As jethrow suggested using ComObjActive could be risky. I myself like to get the workbook path and then do something like this
FileSelectFile,file
osheet := ComObjGet(file).activesheet
osheet.cells(2,3).value := "hello world"
ExitApp

Never lose.
WIN or LEARN.

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Oooh that's nice. :O

I think I can use that. Thanks for the snippet. :wink:

Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009
#IfWinActive, ahk_class XLMAIN

F1::

oExcel := Excel_Get(), oSheet := oExcel.ActiveWorkbook.ActiveSheet

oSheet.Columns("A").ColumnWidth := 4,  oSheet.Columns("B").ColumnWidth := 16

oSheet.Columns("C").ColumnWidth := 10, oSheet.Columns("D").ColumnWidth := 10

oSheet.Columns("E").ColumnWidth := 40, oSheet.Columns("F").ColumnWidth := 60

oSheet.Cells.Select



oSheet.Sort.SortFields.Clear

oSheet.Sort.SortFields.Add(oSheet.Range("F:F"),0,1,ComObjMissing(),0)

oSheet.Sort.SetRange(oSheet.Range("A:M"))

oSheet.Sort.Header := 1, oSheet.Sort.MatchCase := 0

oSheet.Sort.Orientation := 1, oSheet.Sort.SortMethod := 1

oSheet.Sort.Apply

return

#IfWinActive





#Include Acc.ahk			; by Sean and Jethrow	http://www.autohotkey.com/forum/viewtopic.php?t=82767



Excel_Get(WinTitle="ahk_class XLMAIN") {	; by Sean and Jethrow, minor modification by Learning one

	ControlGet, hwnd, hwnd, , Excel71, %WinTitle%

	if !hwnd

		return

	Window := Acc_ObjectFromWindow(hwnd, -16)

	Loop

		try

			Application := Window.Application

		catch

			ControlSend, Excel71, {esc}, %WinTitle%

	Until !!Application

	return Application

}	; http://www.autohotkey.com/forum/viewtopic.php?p=492448#492448
Edit: Excel_Get() updated

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
FYI - the Application call will be rejected if the worksheet is being edited. Here's a workaround:
Excel_Get(WinTitle="ahk_class XLMAIN") {

	ControlGet, hwnd, hwnd, , Excel71, %WinTitle%

	Window := Acc_ObjectFromWindow(hwnd, -16)

	Loop

		try

			Application := Window.Application

		catch

			ControlSend, Excel71, {esc}, %WinTitle%

	Until !!Application

	return Application

}