Paste columns to form

[Dan113]

I am copying data from a table on a website that if I paste into excel splits into the required cells. I have tried creating a form that will accept this type of pasted input but only managed to get it to paste as a single string. Is there a way to get it to paste into a form as formatted columns?

 

[Ranman256]

you should paste into a single column, then run a macro to chop it up like:

Public Sub ParseName()
Dim vNam, vFirst, vLast
Dim I As Integer

Range("B2").Select
While ActiveCell.Value <> ""
   vNam = ActiveCell.Value
   I = InStr(vNam, ",")
   If I > 0 Then
     vLast = Left(vNam, I - 1)
     vFirst = Mid(vNam, I + 1)
   Else
     vFirst = vNam
   End If
   
   ActiveCell.Offset(0, 4) = vFirst
   ActiveCell.Offset(0, 5) = vLast
   
   ActiveCell.Offset(1, 0)    'next row
Wend
End Sub

 

[isladogs]

I am copying data from a table on a website that if I paste into excel splits into the required cells. I have tried creating a form that will accept this type of pasted input but only managed to get it to paste as a single string. Is there a way to get it to paste into a form as formatted columns?

If you are using Excel 2010 or later, you can also use Excel Power Query which will parse it for you

NOTE:
This is an add-in for 2010/2013 from the MS site
Its built in to Excel 2016 - Data ... Get Data

 

[Dan113]

I am trying to avoid using excel if possible and go from webpage straight to access form.

Sent from my Pixel XL using Tapatalk

 

[isladogs]

you need to give use more info about the data.

What formats are available for exports from the website?
For example CSV, xml, Json?
Can you use http GET to automate the output?
Suggest you upload a data sample

 

[Dan113]

Thanks for the help, I have now grabbed the html (attached) i have created a loop through to get dig down to each TD within each transaction in the first STD table but this loops through and gets all data from all the tables.

What is the best way to grab the data from the first stdtable and put it into an array? I'm new to working with html and objects as I come from a lisp background.View attachment html.txt

Sent from my Pixel XL using Tapatalk

 

[isladogs]

You can use the HTML import wizard
I've done that quickly as attached.
It took less time than typing this

Some errors but you should be able to modify & fix

NOTE the import file will need to have .html as the suffix