soft4pedia

Conditional use of "_Excel_RangeRead"

6 posts in this topic

Hello,

I trying to copy text from a specific cell "C1" of an Excel sheet. Script successfully copy the content from excel sheet and show it in a message box but I want to use _Excel_RangeRead command on conditional basis such as I want copy whole cell just after skipping data available in brackets like i don't want to copy edition of book and want to copy rest of data behind and after those brackets for example

Value available in Cell "C1" is;

Fundamentals of Financial Management [13th Edition] by Van horne & John M Wachowicz

my requirements is like this:

Fundamentals of Financial Management by Van horne & John M Wachowicz

Please guide me how can it be possible

 

$File = _Excel_RangeRead ($oWorkbook1, Default, "C1")
ClipPut(FileRead($File))
Msgbox(0,"", $File)

 

Share this post


Link to post
Share on other sites



There's probably a very elegant RegEx way of doing that, but this works too...

$File = "Fundamentals of Financial Management [13th Edition] by Van horne & John M Wachowicz"
If StringInStr($File, "[") AND StringInStr($File, "]") Then
    $File = StringLeft($File,StringInStr($File, "[")-1) & StringRight($File,StringLen($File) - StringInStr($File, "]")-1)
EndIf
MsgBox(0,"",$File)

 

Share this post


Link to post
Share on other sites

Hello MuffinMan,

you did awesome job, but you totally missed my point I don't have values like you used basically I've values saved in an Excel file that's why I've to use _Excel_RangeRead command combination with StringRegExpReplace command to remove my desired content while copying from a desired cell.

Basically I've combination of characters to show versions of books like below

some books show differently like:

[12th edition]

[10th edition]

(13th edition)

{11th edition}

these 4 combinations are available through excel sheet from 11th to 14 edition only so please guide how can i use _Excel_RangeRead command with combination with StringRegExpReplace to fulfill my requirements...

Share this post


Link to post
Share on other sites

_Excel_RangeRead returns a String. So replace

$File = "Fundamentals of Financial Management [13th Edition] by Van horne & John M Wachowicz"

with your _Excel_RangeRead statement, then add additional checks for () and {} as suggested by MuffinMan.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Got it thanks it's working mate...

Share this post


Link to post
Share on other sites
$File = "Fundamentals of Financial Management [13th Edition] by Van horne & John M Wachowicz" & @LF & _
"Fundamentals of Financial Management {14th Edition} by Van horne & John M Wachowicz" & @LF & _
"Fundamentals of Financial Management (15th Edition) by Van horne & John M Wachowicz"

msgbox(0, '' , stringregexpreplace($File , "\[.*?\]|\{.*?\}|\(.*?\)" , ""))

 


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now