Search found 881 matches

by flyingDman
Today, 00:15
Forum: Ask For Help
Topic: Want to Look for Empty Cells in Excel and Select it, then Set Value on that Cell and Some Cells beside it in that Row.
Replies: 4
Views: 80

Re: Want to Look for Empty Cells in Excel and Select it, then Set Value on that Cell and Some Cells beside it in that Ro

try:

Code: Select all

xl := ComObjActive("excel.application")
frstblnk := xl.activesheet.range("D1").End(-4121).offset(1,0).row
xl.range("D" frstblnk).value := "b"
xl.range("D" frstblnk).offset(0,1).value := "c"
xl.range("D" frstblnk).offset(0,-1).value := "a"
by flyingDman
14 Jan 2021, 18:26
Forum: Ask For Help
Topic: Save all open MS word documents?
Replies: 2
Views: 58

Re: Save all open MS word documents?

Try:

Code: Select all

oWord := ComObjActive("Word.Application")
loop % oWord.documents.count
	if fileexist(oWord.documents(a_index).fullname)
		oWord.documents(a_index).save
It will only save files that have previously been saved.
by flyingDman
04 Jan 2021, 16:47
Forum: Ask For Help
Topic: "Sticky" Control Key in Excel? Topic is solved
Replies: 6
Views: 121

Re: "Sticky" Control Key in Excel? Topic is solved

I do not think the COM tutorials are obtuse. And if COM is obtuse, so is VBA (and I might agree...). This said, COM for Excel is superior in more than one way to simulating keystrokes. And if you do this type of pasting regularly, I would avoid the latter. Although the code above works when more tha...
by flyingDman
23 Dec 2020, 23:52
Forum: Ask For Help
Topic: Excel scroll wheel through tabs
Replies: 14
Views: 148

Re: Excel scroll wheel through tabs

To understand more about COM, see these COM tutorials on the forum: https://autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/ https://www.autohotkey.com/boards/viewtopic.php?f=7&t=8978 https://www.autohotkey.com/boards/viewtopic.php?f=6&t=77#p495 xl := ComObjActive("excel.applicat...
by flyingDman
23 Dec 2020, 21:26
Forum: Ask For Help
Topic: Excel scroll wheel through tabs
Replies: 14
Views: 148

Re: Excel scroll wheel through tabs

You can not do this when you are in edit mode. You can use Excel_get to catch that.
Forum: https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
Github: https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
by flyingDman
23 Dec 2020, 14:15
Forum: Ask For Help
Topic: Excel scroll wheel through tabs
Replies: 14
Views: 148

Re: Excel scroll wheel through tabs

No; only some programs are COM "compliant" and even if they are, it does not mean that they have a macro language like VBA that AHK can interface with.
by flyingDman
21 Dec 2020, 21:41
Forum: Ask For Help
Topic: Excel scroll wheel through tabs
Replies: 14
Views: 148

Re: Excel scroll wheel through tabs

Using COM (which is superior to sending simulated keystrokes):

Code: Select all

xl := ComObjActive("excel.application")
+WheelUp::xl.ActiveSheet.Previous.Select
+WheelDown::xl.ActiveSheet.Next.Select
by flyingDman
16 Dec 2020, 18:47
Forum: Ask For Help
Topic: How to make AHK click in an Excel Macro Button Topic is solved
Replies: 4
Views: 104

Re: How to make AHK click in an Excel Macro Button Topic is solved

I would avoid sending simulated keystrokes, unless there is no other way. You can run macros by their names using COM like so: xl := ComObjActive("excel.application") xl.Run("macro1") return or, if you have the macros stored in your Personal.xlsb file, use this: xl := ComObjActive("excel.application...
by flyingDman
12 Dec 2020, 19:38
Forum: Ask For Help
Topic: Looping over cells in a row in excel
Replies: 2
Views: 54

Re: Looping over cells in a row in excel

You have to be much more specific re: the incorporation of the content of the cells in a Word document. Does the entire text (or all cells in a row) replace the entire content of the word document? Just a specific portion? Etc. Etc.
by flyingDman
04 Dec 2020, 12:08
Forum: Ask For Help
Topic: copy list of filenames in active folder to clipboard
Replies: 15
Views: 641

Re: copy list of filenames in active folder to clipboard

Try: F12:: list := "" path := Explorer_GetPath() Loop, Files, %path%\*.* { SplitPath, A_LoopFileName , , , , OutNameNoExt list .= OutNameNoExt "`n" } msgbox % list return Explorer_GetPath() { WinGetClass, winClass, % "ahk_id" . hWnd := WinExist("A") if !(winClass ~= "(Cabinet|Explore)WClass") Return...
by flyingDman
01 Dec 2020, 20:51
Forum: Ask For Help
Topic: Paste data to Excel Topic is solved
Replies: 2
Views: 156

Re: Paste data to Excel Topic is solved

The last non-empty row in a column can be established using this:

Code: Select all

xl := ComObjActive("Excel.application")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
to the first empty row is lstrw + 1
by flyingDman
17 Nov 2020, 18:53
Forum: Ask For Help
Topic: Improving a simple code snippet
Replies: 8
Views: 190

Re: Improving a simple code snippet

Or this:

Code: Select all

str := "C:\MyFiles\AHK All\AHK 07.03.20\B FolderAndFiles"
StrReplace(str, "\",  , cnt)
loop % cnt
	msgbox % substr(str,1,instr(str,"\",,,cnt-a_index+1)-1)
by flyingDman
17 Nov 2020, 14:37
Forum: Ask For Help
Topic: Improving a simple code snippet
Replies: 8
Views: 190

Re: Improving a simple code snippet

I guess the original OP script should have been: str := "C:\MyFiles\AHK All\AHK 07.03.20\B FolderAndFiles" d := [] F := str Loop % strSplit(str, "\").length() - 1 { F := substr(str,1,InStr(F, "\",0,0) - 1) d[A_Index] := F } Loop % d.length() ;<<<<<< MsgBox % d[A_Index] Now try this: str := "C:\MyFil...
by flyingDman
09 Nov 2020, 10:50
Forum: Ask For Help
Topic: Help to speed up my script
Replies: 5
Views: 173

Re: Help to speed up my script

"Find" is another way. But very limited or cumbersome when searching for multiple values. You do not have to invoke COM twice. This suffices: xl := ComObjCreate("Excel.Application") xl.Visible := false file := A_ScriptDir "\xlfind.xlsx" ; Path to file xl.Workbooks.Open(file) ; Open the file rng := x...
by flyingDman
08 Nov 2020, 18:37
Forum: Ask For Help
Topic: Help to speed up my script
Replies: 5
Views: 173

Re: Help to speed up my script

These "automator" functions are slow for larger spreadsheets because a COM call is made for each cell. Either use a safearray like this: needle := "test" xl := ComObjActive("Excel.application") sarr := xl.activesheet.usedrange.value Loop % sarr.MaxIndex() if (sarr[A_Index,1] = needle) MsgBox % sarr[...
by flyingDman
01 Nov 2020, 18:18
Forum: Ask For Help
Topic: Excel cell value to variable
Replies: 4
Views: 177

Re: Excel cell value to variable

The following steps through each selected cell in a spreadsheet (contiguous or not): xl := ComObjActive("excel.application") For c In xl.Selection MsgBox % c.Address " " c.text You can push each value to an array and then loop through the array if needed. You also have to be mindful of the order in ...
by flyingDman
30 Oct 2020, 16:01
Forum: Offtopic
Topic: « What's on your mind? »
Replies: 4477
Views: 1065772

Re: « What's on your mind? »

Thanks @garry. It will take a while before I have one of those in my driveway!....
by flyingDman
30 Oct 2020, 14:57
Forum: Ask For Help
Topic: EXCEL: find string of 9 or more digits
Replies: 8
Views: 187

Re: EXCEL: find string of 9 or more digits

I believe the OP wanted to search in every sheet:

Code: Select all

xl := ComObjActive("excel.application")
for sheet in XL.WorkSheets
	for c in sheet.usedrange
		if (c.value ~= "(\d{9,}|\d{6}-\d{3})")
			msgbox % c.text " found in cell " c.address(0,0) " in sheet " sheet.name

Go to advanced search