I get payment updates in an Excel sheet. Its quite a task to capture them one by one manually.
Since the organisation is not going to automate very soon, I need to copy a column/s from that Excel sheet and paste t into a listview.
The rest I can do, but how to copy From Excel in a format I can read in the Listview I don't yet understand.
Unless one can open the xmls in my program, but I have no idea how to read from such a file.
Any advice will be very welcome!
Petrus,
At the risk of being told I am doing it all wrong, you can try this (I assume you can handle the opening of the XL workbook):
Ian.
Function GetXLCellText(cellref As String) As String
Local vCellAddr,vText As Variant
vCellAddr =Trim$(cellref)
Object Get oExcelWorkSheet.Range(vCellAddr).Text To vText
Function= Variant$(vText)
End Function
Thanks Ian, will take a look at this also.
I was also looking at opening the excel sheet manually, highlighting your respective column and drag it over to my app.
Any idea if one can do that? One can COPY from Excel, but i am not sure how it copies the data to the Clipboard because not all applications would accept the paste.
I appreciate any assistance very much!
PS. This is probably the most tolerant forum in the entire web. Nobody here will tell you its wrong, they will help you get the best possible solution. :)
OK, now this is interesting.
When you copy a SINGLE column in EXCEL and paste it to a Multiline Textbox it just writes the text in lines below each other.
If you copy and paste adjacent columns it simply creates spaces between the different columns and makes them one line.
Same happens from Libre Office, thus making it indistinguishable where the columns starts and ends as the words may have spaces in the columns too.
This must mean that there must be formatting characters that cannot be read by the receiving textbox or control.
The reason I say so, is that if i paste it into Wordpad, I get Column lines drawn also.
It would suggest that i have to read the data from the "copy" in some other way other than just a copy and paste.
Any ideas?
You need to parse each line.
The delimiter between entries on a line is tab -->> chr$(9)
And each line is ended with $crlf
So if you have copied a block of data from Excel on to the clipboard, you can separate each line by using something like this:
Clipboard Get Text To cliptext,ClipResult
If ClipResult=%false Then Exit Function
'first remove any trailing crlf
cliptext=RTrim$(cliptext,Any $CrLf)
Dim b$(1 To ParseCount(cliptext,$CrLf))
Parse cliptext, b$(),$CrLf
This will give you each line in b$()
Then you can do a similar thing on each element of b$() using chr$(9) to get individual entries on a line.
Ian.
;D
Thanks a million! Just what I was looking for!
After much time, i revisited this topic.
The code works great, now the challenging part is how to filter out garbage that you dont want?
This will be quite interesting as you dont have control over what the person copies and paste!!!
Especially if someone copies across Excell columns and so forth.
LOL, with every solution comes a new problem it seems!! :)