Make some words in Excel cell bold Topic is solved
Make some words in Excel cell bold
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?
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?
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: Make some words in Excel cell bold Topic is solved
Try:
Assuming you want all before " _ " in bold. To include " _ " add 3 to len1.
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"
}
14.3 & 1.3.7
Re: Make some words in Excel cell bold
Thanks, it worked!!
Probably won't help anyone, but here's the full code:
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
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.flyingDman wrote: ↑28 Jun 2022, 14:41Try:Assuming you want all before " _ " in bold. To include " _ " add 3 to len1.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" }
Re: Make some words in Excel cell bold
The function StrSplit returns an array object. How to use objects is documented centrally on the Objects page:
This means, you are retrieving element 1 from that returned array...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.
- AlphaBravo
- Posts: 586
- Joined: 29 Sep 2013, 22:59
Re: Make some words in Excel cell bold
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)
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: Make some words in Excel cell bold
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:
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"
}
14.3 & 1.3.7
- AlphaBravo
- Posts: 586
- Joined: 29 Sep 2013, 22:59
Re: Make some words in Excel cell bold
I am glad you got a kick out of itflyingDman wrote: ↑28 Jun 2022, 21:06Ha 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...
Re: Make some words in Excel cell bold
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?
- AlphaBravo
- Posts: 586
- Joined: 29 Sep 2013, 22:59
Re: Make some words in Excel cell bold
have this line SetBatchLines -1 in your "Auto-execute Section"
also
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
All of these are already included:AlphaBravo wrote: ↑29 Jun 2022, 12:11have this line SetBatchLines -1 in your "Auto-execute Section"
alsoCode: 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
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
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
}
}
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: Make some words in Excel cell bold
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:
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.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
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
Last edited by flyingDman on 29 Jun 2022, 21:52, edited 3 times in total.
14.3 & 1.3.7
Re: Make some words in Excel cell bold
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.