PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  

Author Topic: Column Copy and paste from Excel to Listview  (Read 1194 times)

Petrus Vorster

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 356
Column Copy and paste from Excel to Listview
« on: May 20, 2016, 05:30:20 AM »

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!
Logged

Ian Vincent

  • FireFly3 Registered User
  • Little Newbie FireFly
  • *
  • Posts: 25
  • FF3 User
Re: Column Copy and paste from Excel to Listview
« Reply #1 on: May 20, 2016, 10:02:05 AM »

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
Logged

Petrus Vorster

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 356
Re: Column Copy and paste from Excel to Listview
« Reply #2 on: May 20, 2016, 12:50:23 PM »

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. :)
Logged

Petrus Vorster

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 356
Re: Column Copy and paste from Excel to Listview
« Reply #3 on: May 20, 2016, 01:08:58 PM »

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?
Logged

Ian Vincent

  • FireFly3 Registered User
  • Little Newbie FireFly
  • *
  • Posts: 25
  • FF3 User
Re: Column Copy and paste from Excel to Listview
« Reply #4 on: May 20, 2016, 10:25:10 PM »

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.

Logged

Petrus Vorster

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 356
Re: Column Copy and paste from Excel to Listview
« Reply #5 on: May 21, 2016, 10:54:18 AM »

 ;D
Thanks a million! Just what I was looking for!
Logged

Petrus Vorster

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 356
Re: Column Copy and paste from Excel to Listview
« Reply #6 on: May 25, 2017, 01:38:50 PM »

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!! :)
Logged