AutoHotkey Community

Let's help each other out
https://www.autohotkey.com/boards/

Make some words in Excel cell bold

https://www.autohotkey.com/boards/viewtopic.php?f=76&t=105877

Page 1 of 1

Make some words in Excel cell bold

Posted: 28 Jun 2022, 13:55
by zvit
I'm looping a selection of cells in Excel via For c In xlApp.selection.cells.
I'm writing text to each cell via a simple c.value:= FinalWord " — " glossory
It's then simple to make any looped cell have bold text via c.Cells.Font.Bold := True.

However, I can't figure out how to make only the first word of a cell bold. (The FinalWord " — " part)

Any ideas?

Re: Make some words in Excel cell bold  Topic is solved

Posted: 28 Jun 2022, 14:41
by flyingDman
Try:

Code: Select all

#\::
xl := ComObjActive("excel.application")
FinalWord := "testing"
Glossary := "test glossary"
input := FinalWord " — " Glossary
msgbox % len1 := strlen(strsplit(input, " — ").1)
for c in xl.selection.cells
	{
	c.value := input
	c.Characters(1, len1).Font.fontstyle := "Bold"
	}
Assuming you want all before " _ " in bold. To include " _ " add 3 to len1.

Re: Make some words in Excel cell bold

Posted: 28 Jun 2022, 15:39
by zvit
Thanks, it worked!!
Probably won't help anyone, but here's the full code:

Code: Select all

FixGlossoryCase:

    SelectedCellsCount:= xlApp.selection.count
    For c In xlApp.selection.cells
    {
        if (c.column != 21) ;Fix later to: EX_ColHeaderName(rng) != "Glossory"
        {
            msgbox % "Only choose Glossory column"
            goto GetOutOfHere
        }
    }
    ;=========================
    ;// NOTE FOR PROGRESS BAR
    ;=========================
    global ProgressBar
    Gui, Add, Progress, xm w300 h20 vProgressBar -Smooth
    Gui, Show,x1456 y575, Fixing Glossory case...
    Gui, +AlwaysOnTop
    No := 100/SelectedCellsCount, N := 0

    For c In xlApp.selection.cells
    {
        if InStr(c.value," — ")
            Continue

        FinalWord := xlApp.Range(c.Cells.Offset(0,-14),c.Cells.Offset(0,-14)).value ;Fix later to column rng name
        STR_LowerCaseFirstLetter(FinalWord)

        glossory:= c.value
        glossory := Trim(glossory, " ")
        glossory:= STR_UpperCaseFirstLetter(glossory)
        FinalOutput:= FinalWord " — " glossory
        len1 := strlen(strsplit(FinalOutput, " — ").1)
        c.value:= FinalOutput
        c.Cells.Interior.Color := 0xC6EFCE
        c.Characters(1, len1+3).Font.fontstyle := "Bold"
        ;=========================
        ;// NOTE FOR PROGRESS BAR
        ;=========================
        GuiControl,, ProgressBar,% N += No
    }
    Gui, Destroy
GetOutOfHere:
return

Re: Make some words in Excel cell bold

Posted: 28 Jun 2022, 19:58
by zvit
flyingDman wrote: ↑
28 Jun 2022, 14:41
 Try:

Code: Select all

#\::
xl := ComObjActive("excel.application")
FinalWord := "testing"
Glossary := "test glossary"
input := FinalWord " — " Glossary
msgbox % len1 := strlen(strsplit(input, " — ").1)
for c in xl.selection.cells
	{
	c.value := input
	c.Characters(1, len1).Font.fontstyle := "Bold"
	}
Assuming you want all before " _ " in bold. To include " _ " add 3 to len1.
Can you please tell me how you knew about putting a .1 at the end of the StrSplit? I don't see this mentioned in the documents here.

Re: Make some words in Excel cell bold

Posted: 28 Jun 2022, 20:26
by gregster
The function StrSplit returns an array object. How to use objects is documented centrally on the Objects page:
https://www.autohotkey.com/docs/Objects.htm#Usage_Objects wrote:For all types of objects, the notation Object.LiteralKey can be used to access a property, array element or method, where LiteralKey is an identifier or integer and Object is any expression. Identifiers are unquoted strings which may consist of alphanumeric characters, underscore and, in [v1.1.09+], non-ASCII characters. For example, match.Pos is equivalent to match["Pos"] while arr.1 is equivalent to arr[1]. There must be no space after the dot.
This means, you are retrieving element 1 from that returned array...

Re: Make some words in Excel cell bold

Posted: 28 Jun 2022, 20:28
by AlphaBravo
zvit wrote: ↑
28 Jun 2022, 19:58
 Can you please tell me how you knew about putting a .1 at the end of the StrSplit? I don't see this mentioned in the documents here.
whether you save the result to an object and then call the object or work on StrSplit directly is the same, I guess you just learn that by experience.

Code: Select all

FinalWord := "testing"
Glossary := "test glossary"
input := FinalWord " — " Glossary
word_array := strsplit(input, " — ")
MsgBox % word_array.1 "`n" word_array[1] "`n" strsplit(input, " — ").1 "`n" strsplit(input, " — ")[1]


@flyingDman I am just picking on you ;) , what is the purpose of StrSplit the variable you've just concatenated?

Code: Select all

FinalWord := "testing"
Glossary := "test glossary"
input := FinalWord " — " Glossary
MsgBox % len1 := strlen(strsplit(input, " — ").1) " vs " StrLen(FinalWord)

Re: Make some words in Excel cell bold

Posted: 28 Jun 2022, 21:06
by flyingDman
Ha Ha!. The idea was that the input does not come in the form of var1 " _ " var2 but as one string that has to be parsed. I was copying and pasting from 2 old scripts and the result is a bit strange...

This was the idea:

Code: Select all

#\::
xl := ComObjActive("excel.application")
Delimiter := " _ "
input := "testing _ test glossary"
len1 := strlen(strsplit(input, Delimiter).1)        ; + strlen(Delimiter)
for c in xl.selection.cells
	{
	c.value := input
	c.Characters(1, len1).font.fontstyle := "Bold"
	}

Re: Make some words in Excel cell bold

Posted: 28 Jun 2022, 21:55
by AlphaBravo
flyingDman wrote: ↑
28 Jun 2022, 21:06
 Ha Ha!. The idea was that the input does not come in the form of var1 " _ " var2 but as one string that has to be parsed. I was copying and pasting from 2 old scripts and the result is a bit strange...
I am glad you got a kick out of it :D

Re: Make some words in Excel cell bold

Posted: 29 Jun 2022, 04:36
by zvit
Thanks guys, very helpful. I'm only wondering why it seems to be working slower than I'd hope. Is there an alternative to using objects that might speed up the process?

Image

Re: Make some words in Excel cell bold

Posted: 29 Jun 2022, 12:11
by AlphaBravo
have this line SetBatchLines -1 in your "Auto-execute Section"
also

Code: Select all

xl.Application.DisplayAlerts := false
xl.Application.ScreenUpdating := false
xl.Application.Interactive := false

; do you excel automation stuff here

xl.Application.DisplayAlerts := true
xl.Application.ScreenUpdating := true
xl.Application.Interactive := true

Re: Make some words in Excel cell bold

Posted: 29 Jun 2022, 12:51
by zvit
AlphaBravo wrote: ↑
29 Jun 2022, 12:11
 have this line SetBatchLines -1 in your "Auto-execute Section"
also

Code: Select all

xl.Application.DisplayAlerts := false
xl.Application.ScreenUpdating := false
xl.Application.Interactive := false

; do you excel automation stuff here

xl.Application.DisplayAlerts := true
xl.Application.ScreenUpdating := true
xl.Application.Interactive := true
All of these are already included:

Code: Select all

;OPTIMIZATIONS START
#NoEnv
#MaxHotkeysPerInterval 99000000
#HotkeyInterval 99000000
#KeyHistory 0
#NoTrayIcon
#SingleInstance Force ;Skips the dialog box and replaces the old instance automatically.
ListLines Off
Process, Priority, , A
SetBatchLines, -1
SetKeyDelay, -1, -1
SetMouseDelay, -1
SetDefaultMouseSpeed, 0
SetWinDelay, -1
SetControlDelay, -1
SendMode Input
;OPTIMIZATIONS END
And I also have this function to speedup Excel:

Code: Select all

XL_Speedup(PXL,Status){ ;Helps COM functions work faster/prevent screen flickering, etc.
	if(!Status){
		PXL.application.displayalerts := 0
		PXL.application.EnableEvents := 0
		PXL.application.ScreenUpdating := 0
		PXL.Application.Interactive := 0
		;PXL.application.Calculation := -4135
	}else{
		PXL.application.displayalerts := 1
		PXL.application.EnableEvents := 1
		PXL.application.ScreenUpdating := 1
		PXL.Application.Interactive := 1
		;PXL.application.Calculation := -4105
	}
}
which I usually turn on with XL_Speedup(xlApp,0) but had forgotten to with this new label. After turning it on, all was good again with the world. Thanks!

Re: Make some words in Excel cell bold

Posted: 29 Jun 2022, 17:39
by flyingDman
Also, do not forget that having excel loop through the cells in a range is slow. If you append or prepend a string to the content of a cell, it is even slower. Now, this should not be a huge issue when your range is 50 cells, but it is very noticeable when it is several hundred and it is super slow when you have 10,000 cells. Using a safearray is very significantly faster. Compare:

Code: Select all

st := A_TickCount
xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.cells
	c.value := "aaaaaa" c.value
msgbox % A_TickCount - st         ;11172 ms on a 10,000 cell spreadsheet
;or
st := A_TickCount
xl := ComObjActive("excel.application")
sarr := xl.activesheet.usedrange.value
loop, % cnt := sarr.maxindex(1)
	sarr[a_index,1] := "bbbbb" . sarr[a_index,1]
xl.range("A1:A" cnt) := sarr
msgbox % A_TickCount - st         ;31 ms on a 10,000 cell spreadsheet
The bad news is, I do not have a similar solution when it come to changing the text attributes of an existing string in a cell. This took over 18s:

Code: Select all

st := A_TickCount
xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.cells
	c.Characters(1, 5).Font.fontstyle := "Bold"
msgbox % A_TickCount - st

Re: Make some words in Excel cell bold

Posted: 29 Jun 2022, 19:20
by zvit
Thanks, I'll use the safearray on other projects. This one only has about 1800 rows and I only need to run it once a month on the entire sheet, so it's not a big deal. However, I run 20-50 rows at a time all day, so that's why I wanted it to be a bit faster and the previous solution was great for that.
All times are UTC-05:00
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/