Ad
Ad
Ad
Pages: [1]   Bottom of Page
Print
Author Topic: Epson 3880 Job information  (Read 3843 times)
Onslow
Jr. Member
**
Offline Offline

Posts: 75


« on: September 04, 2013, 08:23:24 AM »
ReplyReply

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.... Sad
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
Logged

Cheers

Onslow
Luigi Bozianu
Newbie
*
Offline Offline

Posts: 1


« Reply #1 on: September 05, 2013, 01:09:30 AM »
ReplyReply

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.
Logged
Onslow
Jr. Member
**
Offline Offline

Posts: 75


« Reply #2 on: September 05, 2013, 07:12:27 AM »
ReplyReply

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... Smiley
Logged

Cheers

Onslow
Steve House
Full Member
***
Offline Offline

Posts: 202


« Reply #3 on: September 06, 2013, 09:41:18 AM »
ReplyReply

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.
« Last Edit: September 06, 2013, 09:56:02 AM by Steve House » Logged
Onslow
Jr. Member
**
Offline Offline

Posts: 75


« Reply #4 on: September 06, 2013, 05:50:29 PM »
ReplyReply

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.

Logged

Cheers

Onslow
Steve House
Full Member
***
Offline Offline

Posts: 202


« Reply #5 on: September 07, 2013, 07:01:45 AM »
ReplyReply

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))
« Last Edit: September 07, 2013, 07:30:34 AM by Steve House » Logged
Onslow
Jr. Member
**
Offline Offline

Posts: 75


« Reply #6 on: September 08, 2013, 05:43:53 PM »
ReplyReply

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... Smiley
Logged

Cheers

Onslow
Pages: [1]   Top of Page
Print
Jump to:  

Ad
Ad
Ad