Page 1 of 1

Running an Excel macro

Posted: 10 Oct 2017, 12:25
by jaaksbus
This is not necessarily a new problem, but I am struggling with it.

I am trying to open an Excel workbook, run a macro, then have the ahk script continue to run. I can get the file to open and the macro to run, but it goes south after that. Excel throws a hissy fit because it sees any ahk code after calling the spreadsheet and running the script as part of the macro. Why it does this, I haven’t a clue.
Here is the code:

-::
run, I:\Dropbox\01aaaMisc\ACES\contact downloads\Chief (32).xlsm
oExcel := ComObjActive("Excel.Application")
oExcel.Run("Macro9")
oexcel.quit
oexcel :=" "

This does work if I don’t have any code after it.

Now, I understand it is better to use Excel_Get subroutine but I am completely in the fog about how to use it. I don’t know where to put in the Excel path name. I tried exchanging %WinTitle% with % I:\Dropbox\01aaaMisc\ACES\contact downloads\Chief (32).xlsm%. When I do this, I get:

I:\Dropbox\Hot Key Mouse Control\scripts\excel_get_.ahk (9) : ==> The following variable name contains an illegal character: I:\Dropbox\01aaaMisc\ \contact downloads\Chief (32).xlsm)"

If it isn’t glaringly obvious, I am fairly new to using AHK and would appreciate all the help I can get.

Re: Running an Excel macro

Posted: 12 Oct 2017, 16:06
by Guest
bump if you figured it out.

I'm new to AHK as well and want to learn how to use AHK with Excel and Firefox.

Re: Running an Excel macro

Posted: 12 Oct 2017, 19:59
by jaaksbus
#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.
SetTitleMatchMode, 2
;OPEN excel file
xl := ComObjCreate("Excel.Application")
xl.Visible := true
FilePath := "CI:Your Directoy\Your File.xlsm"
xl.Workbooks.Open(FilePath)
xl.Run("TheMacro")
If you want it to close, add:
xl.quit

Much of this is lifted from a script I found. I would give create but can't find the post..
I've received nothing about Excel_get method.

Re: Running an Excel macro

Posted: 13 Oct 2017, 13:58
by FanaticGuru
jaaksbus wrote:This is not necessarily a new problem, but I am struggling with it.

I am trying to open an Excel workbook, run a macro, then have the ahk script continue to run. I can get the file to open and the macro to run, but it goes south after that. Excel throws a hissy fit because it sees any ahk code after calling the spreadsheet and running the script as part of the macro. Why it does this, I haven’t a clue.
Here is the code:

-::
run, I:\Dropbox\01aaaMisc\ACES\contact downloads\Chief (32).xlsm
oExcel := ComObjActive("Excel.Application")
oExcel.Run("Macro9")
oexcel.quit
oexcel :=" "

This does work if I don’t have any code after it.

Now, I understand it is better to use Excel_Get subroutine but I am completely in the fog about how to use it. I don’t know where to put in the Excel path name. I tried exchanging %WinTitle% with % I:\Dropbox\01aaaMisc\ACES\contact downloads\Chief (32).xlsm%. When I do this, I get:

I:\Dropbox\Hot Key Mouse Control\scripts\excel_get_.ahk (9) : ==> The following variable name contains an illegal character: I:\Dropbox\01aaaMisc\ \contact downloads\Chief (32).xlsm)"

If it isn’t glaringly obvious, I am fairly new to using AHK and would appreciate all the help I can get.
Part of the problem I see with your code is I would not expect oExcel.Run("Macro9") to cause AHK to wait for "Macro9" to finish. AHK will send that command to Excel and then immediately continue on doing whatever is next in the AHK script.

If the Excel macro takes very long to finish, you are going to have the Excel macro and the AHK script both doing stuff at the same time (asynchronous threads). This can have unexpected results if the two things interfere with each other.

FG

Re: Running an Excel macro

Posted: 27 Oct 2017, 13:27
by Geramont
Hi, I am having nearly the same problem. I have an app that gives me some data as an excel sheet. I have to change some cell formats and stuff to manipulate the format of the cells. Then I write this excel sheet into a uft8.txt file, which separates the cells content by tabs. I then ahk reads this utf8 file into an array for further use. That is all working fine.

Excel is the problem for me. I recorded a macro for the cell format manipulation. Then exported that macro to a .bas file. Now I want ahk to do as follows:
  1. start the app and let it produce the excel sheet with the data I need
  2. tell excel to use the .bas file (the vb macro exported by the macro editor like explained above)
  3. Let ahk give system modal message box, so it will be on hold until I press OK, which is, when excel finished saving the sheet as utf8 file.
1. and 3. are working fine, but I cannot get excel to use the .bas file. I just started using AHK and do not know what objects and com are, so most of the code I read the last days I just do not comprehend.

'til now ahk is telling excel what keys to press to make the cell format changes, but that is awfully slow. It works just as if I would click an mark everything manually.

Do I have to save an excel file which holds the macro or can ahk tell excel to run the external .bas file?

Code: Select all

oExcel := ComObjActive("Excel.Application")	;this will start excel
oExcel.Run("MyMacro")						;will open the .xslm file containing the macro "MyMacro" and run it
											;how can I put a pause her to see the excel sheet before exported?
oexcel.quit									;will quit excel
oexcel :=" "								;will clear the object
The code above runs my excel file containing the macro (comments on the thing I think I understood). Putting the system modal message box will put ahk on hold. But how can I just use the generated excel and tell it to import my .bas file?

The external .bas file would be much easier to maintain for me.

Right now my ahk script relies on the window title of the generated excel file. If I had to first open the excel file containing the script I would get other window names. It is a bit confusing to me...but AHK is like a great candy box -- every day I find a new use for this great tool! Cannot think of using windows without it ;o)

Thx for any help on this!

Re: Running an Excel macro

Posted: 27 Oct 2017, 13:38
by Geramont
Just found this :https://autohotkey.com/boards/viewtopic ... xcel+macro

At least now I know hot to start my personal workbook containing my macros. So until I get to know how to run an external .bas file on excel I will try to use my personal workbook. I'll keep working on it ;o)

Re: Running an Excel macro

Posted: 16 Nov 2017, 16:41
by Geramont
It is stil not working. By now I have stored my macro in my personal workbook (PERSONAL.XLSB). The workflow should be as follows:
  1. ahk lets app A call excel to export its data into a new excel sheet
  2. ahk waits for the yet unnamed excel window to appear
  3. if the new excel sheet is opened ahk calls the macro >>MyMacro<< to format the cells
  4. after that ahk replicates the key shortcuts to export the sheet to an utf8.txt file
  5. ahk works on this utf8.txt file to extract some data into an array
But I cannot get it to work. Here is my code so far, just for getting excel to work (omitted the part to export the sheet to utf8.txt)

Code: Select all

IfWinNotActive, ahk_class XLMAIN
{
	MsgBox % "Excel is not active. No action will be performed."
	Return
}

try
oExcel := ComObjActive("Excel.Application")
	
catch
{
	MsgBox % "Unable to execute the shortcut."
	Return
}

try
oExcel.Run("PERSONAL.XLSB!MyMacro")
	
catch
{
	MsgBox % "Unable to run the macro. Are you editing a cell?"
}
oExcel :=

ExitApp
I found this on the website of Enterpad (external grid keyboard to trigger ahk macros). Unfortunately I am a newbi to ahk and objects are unknown lands to me.

I thougt, that when app A opens a new unnamed sheet my PERSONAL.XLSB is already running and so ahk should be able to call MyMacro and run it on the unnamed new sheet. Even if I manually start excel the ahk macro tells me, that excel is not running...

And maybe someone knows a better way to export the excel sheet to utf8.txt than replicating keyboard shortcuts ;o)

Can someone give me a hint? I do not expect to get the code written, I want to understand how it works (thus learn). Thanks a lot!

Re: Running an Excel macro

Posted: 16 Nov 2017, 19:58
by FanaticGuru
Geramont wrote:But I cannot get it to work. Here is my code so far
The code below works as I would expect:

Code: Select all

#F12::
	IfWinNotActive, ahk_class XLMAIN
	{
		MsgBox % "Excel is not active. No action will be performed."
		Return
	}

	try
		oExcel := ComObjActive("Excel.Application")
	catch
	{
		MsgBox % "Unable to execute the shortcut."
		Return
	}

	try
		oExcel.Run("PERSONAL.XLSB!MyMacro")
	catch
	{
		MsgBox % "Unable to run the macro. Are you editing a cell?"
	}
	oExcel :=
return
Just took your code, cleaned up the formatting some and put it in a hotkey.

Has to be in a hotkey because if you just run the script then Excel will not be the active window, the script will be, and the script will fail at the IfWinNotActive.

FG

Re: Running an Excel macro

Posted: 23 Nov 2017, 03:10
by Geramont
Hi FanaticGuru, thanks for your hint. It worked right away but Excel now seems to be the problem: I changed my ahk code to the following:

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

#F12::

WinWait, Microsoft Excel - Mappe1
WinShow, Microsoft Excel - Mappe1
WinActivate, Microsoft Excel - Mappe1

;IfWinActive, ahk_class XLMAIN
oExcel := ComObjActive("Excel.Application")
oExcel.Run("PERSONAL.XLSB!liste_formatieren")
oExcel :=
return
adfasfd
This works fine :D , but now ecxel throws an error :? :
Laufzeitfehler 1004
Die NumberFormat-Eigenschaft des Range-Objektes kann nicht festgelegt werden

I recorded the macro to format my list: This ist excel's vba code:

Code: Select all

Sub export2utf8()
'
' export2utf8 Makro
'
' Tastenkombination: Strg+e
'
    Columns("E:H").Select
    Selection.NumberFormat = "d/m/yy h:mm;@"
    Columns("A:H").Select
    Columns("A:H").EntireColumn.AutoFit
    
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\UserName\Documents\ListeFormatiert.txt", FileFormat:=xlUnicodeText _
        , CreateBackup:=False
    Range("A2").Select
End Sub
The problem is in

Code: Select all

Selection.NumberFormat = "d/m/yy h:mm;@"
Running the macro manually in excel by works fine! The cells are formatted to "5.11.17 13:45" but running the script via ahk it throws an error on the NumberFormat.

I read MS's documentation about the possible formats but none of my tries ("d.m.yy hh:mm" etc.) worked.

Can someone give me a hint? I hope my post is still concerning ahk and I do not have to switch to some excel-forum ;o)

Thanks a lot in advance everyone!

Re: Running an Excel macro

Posted: 24 Nov 2017, 06:11
by Geramont
@jaaksbus
Part of the problem I see with your code is I would not expect oExcel.Run("Macro9") to cause AHK to wait for "Macro9" to finish. AHK will send that command to Excel and then immediately continue on doing whatever is next in the AHK script.

If the Excel macro takes very long to finish, you are going to have the Excel macro and the AHK script both doing stuff at the same time (asynchronous threads). This can have unexpected results if the two things interfere with each other.
What I did concerning the time excel takes to end a task is: I use system modal message boxes. At a point in the script, where excel has to do some stuff and ahk has to wait for the result I pop up a system modal message box. AHK will break until you klick ok (or whatever your button to resume you chose).

Maybe this will help.

Re: Running an Excel macro

Posted: 30 Nov 2017, 16:02
by Geramont
Does any one have a hint for me to get the recorded macro running? Viewing the recorded code in excel's vba editor shows that the generated macro code is not quite what this macro does to the formatted cells.

My cells are not formatted "d/m/yy h:mm;@", the date and time format I get (and want) is "5.11.17 13:45" which is the equivalent to "d.m.yy hh:mm". This does not make sense to me.

The other thing is: running the macro in excel works flawlessly! Calling the macro just works. Calling the macro from ahk I get an error:
>>Laufzeitfehler 1004. Die NumberFormat-Eigenschaft des Range-Objektes kann nicht festgelegt werden<<

Can anyone help me please? Since one week I am struggeling to comprehend com objects, but until now I cannot solve my problem. I managed to create a totally new workbook, edit a cell and export the file to utf8.

But how do I connect to a freshly generated workbook that is not saved to a file? How do I then format some cells? The learning curve is so steep. I will post my experimental code tomorrow.

Thanks in advance ;o)

P.S.: Should I generate a new post, is this the wrong board?