How to open specific dialogue box in MS Excel on pressing a HotKey? Topic is solved

[Sabestian Caine]

Hello friends..

I have to import text files in ms excel too much and for that I have to first click on Data tab and then From Text button. As you can see in this screen shot-




In the above screen shot, you can see there is a button named- From Text in the red box, I want that whenever I press f1 hotkey then it should show Import Text File dialogue box as shown in the below screen shot-




In the above image, you can see a dialogue box which appears when we press From Text button, but I want to appear it when I press f1 hotkey. I know there are several other ways to show this dialogue box, like we can press shortcut key- ALT then A then FT. I know we can use controlclick or mouseclick command also for this purpose, but I want some static codes to do this task.

First I tried to use PostMessage command like this-

Code: Select all

f1::
PostMessage, 0x111, ????,,, Microsoft Excel - Book1
Return

But I am unable to figure out what value I have to put at the place of ???? in the above codes. To figure out the value of ???? I tried to use winspector spy, but it is also not giving the true value. Moreover WinMenuSelectItem command is also not working in the present scenario. Can this be done using COM interface in ms excel?

Please help me..

Thanks a lot..

[CyL0N]

Get Info from Context Menu (x64/x32 compatible)

[jeeswg]

Two ideas. Cheers.

Code: Select all

w:: ;Excel - click 'Data, From Text'
;note: holding Alt displays the keys to press
SendInput, !aft
return

q:: ;Excel - click 'Data, From Text'
;[Acc functions]
;Acc library (MSAA) and AccViewer download links - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=26201
;[to get Acc path]
;Acc: get text from all window/control elements - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=40615

WinGet, hWnd, ID, A
oAcc := Acc_Get("Object", "4.2.4.1.4.1.4.1.4.1.4.14.1.1.1.3", 0, "ahk_id " hWnd)
if (oAcc.accName(0) = "From Text")
	oAcc.accDoDefaultAction(0)
oAcc := ""
return

Has anyone obtained/experimented with any ribbon APIs?

[Sabestian Caine]

Two ideas. Cheers.

Code: Select all

w:: ;Excel - click 'Data, From Text'
;note: holding Alt displays the keys to press
SendInput, !aft
return

q:: ;Excel - click 'Data, From Text'
;[Acc functions]
;Acc library (MSAA) and AccViewer download links - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=26201
;[to get Acc path]
;Acc: get text from all window/control elements - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=40615

WinGet, hWnd, ID, A
oAcc := Acc_Get("Object", "4.2.4.1.4.1.4.1.4.1.4.14.1.1.1.3", 0, "ahk_id " hWnd)
if (oAcc.accName(0) = "From Text")
	oAcc.accDoDefaultAction(0)
oAcc := ""
return

Has anyone obtained/experimented with any ribbon APIs?

Thank you so much dear jeeswg ... You are really great and awesome at AHK... I appreciate your skills and knowledge...


Sir, i have one more problem. As I have to first activate Data tab in MS Excel manually and then your codes works and they open Import Text File dialog box. If Data tab is not activated then your codes do not work. sir is there a way to directly open Import Text File dialog box? Please tell me if we can do this..

Secondly, sir, I want to understand the codes you provided, as-

Code: Select all

oAcc := Acc_Get("Object", "4.2.4.1.4.1.4.1.4.1.4.14.1.1.1.3", 0, "ahk_id " hWnd)

As, in the above codes, you wrote a value 4.2.4.1.4.1.4.1.4.1.4.14.1.1.1.3
I know 14.1.1.1.3 is the value which is shown by AccViewer in Path, as shown in the below image-




But I don't know from where you brought the value- 4.2.4.1.4.1.4.1.4.1.4

Please guide me sir...

I be highly obliged to you...

Thanks a lot sir,,,

[jeeswg]

- I used the 'Acc: get text from all window/control elements' link to get the path.
- I see, SendInput, !aft changes the active tab to 'Data', and for the other script, the Acc path was only correct if the Data tab was active. (Furthermore, my script found no 'From Text' element to 'click' on, when the Data tab was not active.)
- So here is a variant. It checks what the active tab is, shows the dialog, and then restores the active tab afterwards. Cheers.

Code: Select all

;[Acc functions]
;Acc library (MSAA) and AccViewer download links - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=26201
;[to get Acc path]
;Acc: get text from all window/control elements - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=40615

;4.2.4.1.4.1.4.1.4.1.4.8 page tab list [Ribbon Tabs][Ribbon Tabs List]
;4.2.4.1.4.1.4.1.4.1.4.8.1 client [][]
;4.2.4.1.4.1.4.1.4.1.4.8.1.1 page tab [Print Preview][]

q:: ;Excel - get focused tab, click 'Data, From Text', restore focused tab
WinGet, hWnd, ID, A
oAcc := Acc_Get("Object", "4.2.4.1.4.1.4.1.4.1.4.8.1", 0, "ahk_id " hWnd)
if !(oAcc.accParent.accName(0) = "Ribbon Tabs")
	return
for _, oChild in Acc_Children(oAcc)
{
	if (oChild.accState(0) & 0x2) ;STATE_SYSTEM_SELECTED := 0x2
	{
		MsgBox, % oChild.accName(0)
		SendInput, !aft
		WinWaitNotActive, % "ahk_id " hWnd
		oChild.accDoDefaultAction(0)
		break
	}
}
oAcc := oChild := ""
return

[Sabestian Caine]

- I used the 'Acc: get text from all window/control elements' link to get the path.
- I see, SendInput, !aft changes the active tab to 'Data', and for the other script, the Acc path was only correct if the Data tab was active. (Furthermore, my script found no 'From Text' element to 'click' on, when the Data tab was not active.)
- So here is a variant. It checks what the active tab is, shows the dialog, and then restores the active tab afterwards. Cheers.

Code: Select all

;[Acc functions]
;Acc library (MSAA) and AccViewer download links - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=26201
;[to get Acc path]
;Acc: get text from all window/control elements - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=40615

;4.2.4.1.4.1.4.1.4.1.4.8 page tab list [Ribbon Tabs][Ribbon Tabs List]
;4.2.4.1.4.1.4.1.4.1.4.8.1 client [][]
;4.2.4.1.4.1.4.1.4.1.4.8.1.1 page tab [Print Preview][]

q:: ;Excel - get focused tab, click 'Data, From Text', restore focused tab
WinGet, hWnd, ID, A
oAcc := Acc_Get("Object", "4.2.4.1.4.1.4.1.4.1.4.8.1", 0, "ahk_id " hWnd)
if !(oAcc.accParent.accName(0) = "Ribbon Tabs")
	return
for _, oChild in Acc_Children(oAcc)
{
	if (oChild.accState(0) & 0x2) ;STATE_SYSTEM_SELECTED := 0x2
	{
		MsgBox, % oChild.accName(0)
		SendInput, !aft
		WinWaitNotActive, % "ahk_id " hWnd
		oChild.accDoDefaultAction(0)
		break
	}
}
oAcc := oChild := ""
return

Thank you so much dear jeeswg for your kind reply... You are really marvellous at coding...


Sir, pardon me, but still i am not able to understand why you used these numbers - 4.2.4.1.4.1.4.1.4.1.4. in the your following codes-

Code: Select all

oAcc := Acc_Get("Object", "4.2.4.1.4.1.4.1.4.1.4.14.1.1.1.3", 0, "ahk_id " hWnd)

Please tell me sir, I am quite eager to understand it.. Thank you so much dear jeeswg ... Have a great time...

[jeeswg]

I use this script, it gives me a path/name/value for each GUI element in a window. I search for elements with the relevant name/value and then copy and paste the appropriate path for use with Acc_Get.
Acc: get text from all window/control elements - AutoHotkey Community
https://autohotkey.com/boards/viewtopic.php?f=6&t=40615

[Sabestian Caine]

I use this script, it gives me a path/name/value for each GUI element in a window. I search for elements with the relevant name/value and then copy and paste the appropriate path for use with Acc_Get.
Acc: get text from all window/control elements - AutoHotkey Community
https://autohotkey.com/boards/viewtopic.php?f=6&t=40615

Thank you so much dear jeeswg... Now i have understood very well why you used these above numbers...

Thank you so much once again for making me understand this...

Sir, one thing more i want to know, that is- Can we set values also for specific fields by using Acc_Get function???
Please look at this screen shot-


In the above screen shot you can see that in formula bar in excel i put a formula =SUM(A2:A10), I want to know can this formula be put in formula bar using your Acc_Get function? or Acc_Get function works only to retrieve values?? I know there are several other command that can be used for setting values in specific fields like- ControlSetText and PostMessage etc. But i want to learn more about Acc_Get function and AccViewer that's why I am asking this...


Please tell me...

Thank a lot sir,,..

[awel20]

Can this be done using COM interface in ms excel?

Code: Select all

xlDialogImportTextFile := 666
ComObjActive("Excel.Application").Dialogs(xlDialogImportTextFile).Show

[jeeswg]

- @Sabestian Caine: Unfortunately, when I try AccViewer and my JEE_AccGetTextAll function, on the formula bar, I don't see any value/name text.

- @awel20: Cheers.

- The main information for Acc is here:
IAccessible | Microsoft Docs
https://docs.microsoft.com/en-gb/window ... accessible

- It appears that the older version of the page had more info. Stating that accValue was read/write (i.e. the value text), and that all the other properties were read-only (including the name text).
IAccessible interface (Windows)
https://web.archive.org/web/20161224102 ... s.85).aspx

[Sabestian Caine]

Can this be done using COM interface in ms excel?

Code: Select all

xlDialogImportTextFile := 666
ComObjActive("Excel.Application").Dialogs(xlDialogImportTextFile).Show

Thanks a lot dear awel20... the codes you provided worked the best... thank you very much awel20 ...

Sir, if you could solve my one more problem then it would be great help of mine...

sir, i have to import text files in excel too much, so i want some ahk codes to directly import specific text file into excel. The following are the VBA codes which i use to import text file into excel sheet-

Code: Select all

Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\htc\Desktop\File_to_import_in_Excel.txt" _
        , Destination:=Range("$A$1"))
        .Name = "File_to_import_in_Excel.txt"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(12, 12, 19, 7, 31, 2, 5, 29, 5, 13, 12, 13)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=0
End Sub

Sir, could you please give me AHK codes (COM interface codes) to import specific text into Excel. I would be grateful to you. Thanks a lot sir..

[Sabestian Caine]

- @Sabestian Caine: Unfortunately, when I try AccViewer and my JEE_AccGetTextAll function, on the formula bar, I don't see any value/name text.

- @awel20: Cheers.

- The main information for Acc is here:
IAccessible | Microsoft Docs
https://docs.microsoft.com/en-gb/window ... accessible

- It appears that the older version of the page had more info. Stating that accValue was read/write (i.e. the value text), and that all the other properties were read-only (including the name text).
IAccessible interface (Windows)
https://web.archive.org/web/20161224102 ... s.85).aspx

Thanks dear jeeswg .... you really solved my problem... so nice of you... have a great day...

[Thoughtfu1Tux]

Can this be done using COM interface in ms excel?

Code: Select all

xlDialogImportTextFile := 666
ComObjActive("Excel.Application").Dialogs(xlDialogImportTextFile).Show

Do you have any links to tutorials on where I can expand my Excel COM knowledge with commands like the one you mentioned here?

[awel20]

Do you have any links to tutorials on where I can expand my Excel COM knowledge with commands like the one you mentioned here?

Sorry for the late reply. There are some things you need to know in AHK first... Make sure you know a little bit about AHK expressions. (Assign values with the := operator, etc.)
Then the next thing you would need to know is that basics of AHK objects. (How to create an array and read or assign values to it. Multi-dimensional arrays. How to call a method.)
I would start with this AHK Excel tutorial: Basic Ahk_L COM Tutorial for Excel
Lots of useful examples here: COM Object Reference
Another tutorial: MS Office COM Basics Pay special attention to the section on the Object Browser and Help. And there are a bunch of useful links at the end.
You can also google "VBA Tutorial" or something like that and you will get a ton of results.
also https://the-automator.com/