Luminous Landscape Forum
Equipment & Techniques => Computers & Peripherals => Topic started by: Onslow on September 04, 2013, 09:23:24 am
-
Hi There,
I have just discovered the job information log that my Epson 3880 creates. I have a small problem now and I am embarrassed to ask it. Many years ago, I was quite fluent Excel and Access VBA. Now, not having used it for quite a while, I'm having trouble doing something quite basic.
In my example, I have the log file saved as an excel worksheet. Under the Ink used columns, is the amount used per colour with ml added. So, each cell is in the form of for example : 1.26ml.
Many years ago in my job, I used to manipulate strings constantly and would be able to extract the value without the ml tacked on and then rewrite the activecell contents in the form 1.26. I am using MS Excel 2007 on Win 7.
For the life of me now though, I am unable to do this. I have gone through the VBA references and googled this. I am so rusty.... :(
Could someone please assist me in this.
I would like to keep track of my ink usage per sheet etc, this would enable me to do this...
Cheers
-
I suggest selecting the cells and using Ctrl-f or find & replace.
You'll find "ml" or "ml." (without quotes) or whatever you want to get rid of, and replace with nothing "" (without quotes, again).
hope that works.
-
I suggest selecting the cells and using Ctrl-f or find & replace.
You'll find "ml" or "ml." (without quotes) or whatever you want to get rid of, and replace with nothing "" (without quotes, again).
hope that works.
I shall give that a try.... Thanks for that... :)
-
The expression LEFT("1.26ml", LEN("1.26ml")-2) extracts the string "1.26" from the string "1.26ml". Note that this is a text value, not a number. Also, data imprted from text files such as a data log may have leading and/or trailing blanks which you'd need to remove with TRIM(). The expression VALUE("1.26") returns the numerical value 1.26 from the string "1.26". So if " 1.26ml " is in cell B10, the formula =VALUE(LEFT(TRIM(B10),LEN(TRIM(B10))-2)) does what you want.
-
The expression LEFT("1.26ml", LEN("1.26ml")-2) extracts the string "1.26" from the string "1.26ml". Note that this is a text value, not a number. Also, data imprted from text files such as a data log may have leading and/or trailing blanks which you'd need to remove with TRIM(). The expression VALUE("1.26") returns the numerical value 1.26 from the string "1.26". So if " 1.26ml " is in cell B10, the formula =VALUE(LEFT(TRIM(B10),LEN(TRIM(B10))-2)) does what you want.
Steve,
Thank you very much. This makes sense to me now! I was so rusty, I'd forgotten syntax, commands etc and knew what I wanted, but not how to get there. With this now, I can rebuild my knowledge of VBA.
-
Steve,
Thank you very much. This makes sense to me now! I was so rusty, I'd forgotten syntax, commands etc and knew what I wanted, but not how to get there. With this now, I can rebuild my knowledge of VBA.
You're welcome. One note, if you're going to do this conversion in VBA, use either the CSng() or CDbl() conversion functions in place of the workbook VALUE() function, which doesn't exist in VBA. CSng() accepts a numeral string argument and returns a single-precision floating-point numeric while CDbl() returns a double-precision. I'd go with single in your application.
Dim sngInkUsed as Single
Dim strInkLogged as String
'Read log and set strInkLogged to the value reported, then
sngInkUsed = CSng(Left(Trim(strInkLogged), Len(Trim(strInkLogged))-2))
-
You're welcome. One note, if you're going to do this conversion in VBA, use either the CSng() or CDbl() conversion functions in place of the workbook VALUE() function, which doesn't exist in VBA. CSng() accepts a numeral string argument and returns a single-precision floating-point numeric while CDbl() returns a double-precision. I'd go with single in your application.
Dim sngInkUsed as Single
Dim strInkLogged as String
'Read log and set strInkLogged to the value reported, then
sngInkUsed = CSng(Left(Trim(strInkLogged), Len(Trim(strInkLogged))-2))
Steve,
That's exactly what I was after! Now, I can wrap some more code around it to process the entire sheet. That'll make it so much easier now that this part is done....Kudos to you for that... :)