Can't trim the invisible CR/LF when copy excel to clipboard

[myer75]

I been tried to trim the leading and trailing space from the content that i copy into the clipboard through a cell of excels application. I got compilation error 1033. Is any way I can take out the invisible CR/LF via MS or VBScipt?
The code below won't let me use the VBScript function to trim leading and trailing space of the string since the string actually has invisible CR/LF
I would like to pass the content of excel cell from the clipboard to MS or VBScript so bad in order to automate shipping the products in my company.
I open a lot of windows so I need to preserve system memory then.
I have tried to put the content into the notepad.exe and use back space to trim it and recopy to the clipboard. It works with this way but I do not want to open another window application to do this since I am afraid I will consume PC memory.

Any helps will much be appreciatee. Thank you so much in advance.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Suppose I already launched C:\0Temp\Test.csv.
' I then try to copy the 1st cell(1,1) into the clipboard.
' I look at the message box, I see the invisible carrier return or line feed that why I think that
' is problem.
'
Setfocus>Microsoft Excel - TestPaypal.csv
Wait>0.5
Press CTRL
Press Home
Release CTRL
Wait>0.5
Press Down
Press CTRL
Send>c
Release CTRL
Wait>0.5
GetClipBoard>Date
MessageModal>Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Pass parameter out the Function
VBSTART
On Error Resume Next
Err.Raise 1033
Err.Clear
Function StringTrim(String)
StringTrim = Trim(String)
End Function
VBEND
VBEval>StringTrim("%Date%"),Result
Message>String passed out to the MS it's must in double quotes: %Result%

[support]

Hi,

How about this:

VBEval>Replace("%val%",chr(13),""),val
VBEval>Replace("%val%",chr(10),""),val
VBEval>Trim("%val%"),val

First line replaces carriage returns with nothing. Next line replaces linefeeds with nothing. Final line removes trailing and leading spaces.

[myer75]

Hi Support,

I thank you a lot for quick reponse and appreciated.
However, I still can't trim the CR or LF.
This is the actual code I run on my computer so you can evaluate it to see if I did something wrong or compile incorrectly

VBSTART
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True
VBEND
Wait>1
Press CTRL
Press Home
Release CTRL
Wait>0.5
Press Down
' Enter the val " ABC". The leading space is 2 spaces.
Send> ABC
Press Enter
' Move up to cell(2,1)
Press Up
Wait>0.5
Press CTRL
send>c
Release CTRL
Wait>0.5
GetClipBoard>val
Wait>0.5
MessageModal>val
VBEval>Replace("%val%",chr(13),""),val
VBEval>Replace("%val%",chr(10),""),val
VBEval>Trim("%val%"),val
' The result should be ABC without leading space
MessageModal>val

[support]

Interesting. I get "unterminated string constant" error when I try to use clipboard data copied from Excel with that script. It doesn't like it. I can see that the Excel data has a line feed and carriage return on the end. If we can assume that this is always the case then one workaround is simply to always chop off the last two characters. This is simple enough without using VBscript:

GetClipBoard>val
Length>val,lv
Let>lv=lv-2
MidStr>val,1,lv,val
VBEval>Trim("%val%"),val

This gets the length and then takes all the characters minus the last two and then uses the VBScript trim function. Which now works now that the CRLF has been lopped off.

There may be a better way, but this just occured to me and a quick test based on data grabbed from Excel seems to work ok.

Bear in mind there are other (better) ways to get data from Excel - you can do it with DDE or with VBScript direct from the spreadsheet without having to use the clipboard. See Scripts & Tips for examples. I wonder if these methods include the terminating CRLF or if this is just a result of copying the data to the clipboard.

[myer75]

Hi Support,

First I thank you so much for your quick help. That is all I need to trim the string so I can paste into window box of any application software for my shipping deparment.
Secondly, I like to use clicpboard better than Poke DDE or VBScript because we can see what the MS doing on the opened excel worksheet.
We have a lot of different shipping company software and they are in differrent structure. But all have common approach is to open the window to let poeple enter data and excecute it so we like to store data in clipboard then assign to variables than in the array, individual array won't tell us what the field is.
I think MS is the best way for our business to deploy and modify the code in just a minute since our client excel fields change all the time.

I just recoded the MS Script to your guidance and it work fine.
Thank you so much.
Hope one can see the benefit of MS.

VBSTART
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True
VBEND
Wait>1
Press CTRL
Press Home
Release CTRL
Wait>0.5
Press Down
' Enter the val " ABC". The leading space is 2 spaces.
Send> ABC
Press Enter
' Move up to cell(2,1)
Press Up
Wait>0.5
Press CTRL
send>c
Release CTRL
Wait>0.5
GetClipBoard>val
Wait>0.5
MessageModal>val
' The result should be ABC without leading space
GetClipBoard>val
Length>val,lv
Let>lv=lv-2
MidStr>val,1,lv,val
VBEval>Trim("%val%"),val
MessageModal>val

[pgriffin]

Here is an example to trim trailing CR/LF. Should work the same with DDERequest or using your copy method.

DDERequest>Excel,FileName,R1C1,Variable,time_out
Position>%CRLF%,Variable,1,CRLF-Flag
Length>Variable,LengthOfRecord
if>%CRLF-Flag%>1
LET>LengthOfRecord=%CRLF-Flag%-1
Else>
MidStr>%Variable%,1,%LengthOfRecord%,CleanVariable
Endif>