Import Text from a Website

[P5C768]

A vendor refuses to give me the necessary codes for a data file in a nice version, instead only pointing me to their website. Is there a way using a macro to import the following data set into the rows of a table and uses the group name in the first column of the row, but each of the codes (starting in the row after the group name) in a single row?

My access knowledge isn't sophisticated enought to do this and my attempts at using test to columns in excel were futile.

Any help is appreciated!

1 Group A
01000 01001 01002 01003 01004 01005 01006 01010 01011 01012 01013 01014 01015 01016 01080 01081 01082 01083 01084 01085
01086 01090 01091 01092 01093 01094 01095 01096 01100 01101 01102 01103 01104 01105 01106 01110 01111 01112 01113 01114
01115 01116 01120 01121 01122 01123 01124 01125 01126 01130 01131 01132 01133 01134 01135 01136 01140 01141 01142 01143
01144 01145 01146 01150 01151 01152 01153 01154 01155 01156 01160 01161 01162 01163 01164 01165 01166 01170 01171 01172
01173 01174 01175 01176 01180 01181 01182 01183 01184 01185 01186 01190 01191 01192 01193 01194 01195 01196 01200 01201
01202 01203 01204 01205 01206 01210 01211 01212 01213 01214 01215 01216 01220 01221 01222 01223 01224 01225 01226 01230
01231 01232 01233 01234 01235 01236 01280 01281 01282 01283 01284 01285 01286 01300 01301 01302 01303 01304 01305 01306
01310 01311 01312 01313 01314 01315 01316 01320 01321 01322 01323 01324 01325 01326 01330 01331 01332 01333 01334 01335
01336 01340 01341 01342 01343 01344 01345 01346 01350 01351 01352 01353 01354 01355 01356 01360 01361 01362 01363 01364
01365 01366 01380 01381 01382 01383 01384 01385 01386 01390 01391 01392 01393 01394 01395 01396 01400 01401 01402 01403
01404 01405 01406 01480 01481 01482 01483 01484 01485 01486 01500 01501 01502 01503 01504 01505 01506 01510 01511 01512
01513 01514 01515 01516 01520 01521 01522 01523 01524 01525 01526 01550 01551 01552 01553 01554 01555 01556 01560 01561
01562 01563 01564 01565 01566 01570 01571 01572 01573 01574 01575 01576 01580 01581 01582 01583 01584 01585 01586 01590
01591 01592 01593 01594 01595 01596 01600 01601 01602 01603 01604 01605 01606 01610 01611 01612 01613 01614 01615 01616
01620 01621 01622 01623 01624 01625 01626 01630 01631 01632 01633 01634 01635 01636 01640 01641 01642 01643 01644 01645
01646 01650 01651 01652 01653 01654 01655 01656 01660 01661 01662 01663 01664 01665 01666 01670 01671 01672 01673 01674
01675 01676 01690 01691 01692 01693 01694 01695 01696 01700 01701 01702 01703 01704 01705 01706 01710 01711 01712 01713
01714 01715 01716 01720 01721 01722 01723 01724 01725 01726 01730 01731 01732 01733 01734 01735 01736 01740 01741 01742
01743 01744 01745 01746 01750 01751 01752 01753 01754 01755 01756 01760 01761 01762 01763 01764 01765 01766 01770 01771
01772 01773 01774 01775 01776 01780 01781 01782 01783 01784 01785 01786 01790 01791 01792 01793 01794 01795 01796 01800
01801 01802 01803 01804 01805 01806 01880 01881 01882 01883 01884 01885 01886 01890 01891 01892 01893 01894 01895 01896
1370 1371 1372 1373 1374 V1201

2 Group B 0031 0202 0223 0362 0380 0381 03810 03811 03812 03819 0382 0383 03840 03841 03842 03843 03844 03849 0388 0389
0545 449 7907

3 Group C
0200 0208 0209 0218 0219 0228 0229 0230 0231 0232 0233 0238 0239 024 025 0260 0269 0270 0271 0272


0278 0279 0300 0301 0302 0303 0308 0309 0312 0318 0319 03289 0329 0330 0331 0338 0339 0341 0363 03681
03689 0369 037 0392 0393 0394 0398 0399 0400 0401 0402 0403 04042 04081 04082 04089 0410 04100 04101 04102
04103 04104 04105 04109 0411 04110 04111 04112 04119 0412 0413 0414 0415 0416 0417 0418 04181 04182 04183 04184
04185 04186 04189 0419 390 3929 7953 79531 79539 V090 V091 V092 V093 V094 V0950 V0951 V096 V0970 V0971 V0980
V0981 V0990 V0991

[ajetrumpet]

so that is 3 different tables? that's what it looks like.

and you want the row with the 'group' string in it to be left out? you want all of the groups to be imported into one file too? I don't think that's really possible just using a built in method.

how do you get the data from the website? did you know that sometimes you can go to a site and copy and html table with the clipboard and get it right into an access table? Sometimes it doesn't even matter what the delimiter is. have you tried doing that?

[TheShabz]

1. paste into excel
2. find and replace all spaces " " with semicolons ";".
3. Data>text to columns, delimited, no text qualifier
4. adjust to make it into one column
5. import into access.

best i got off the top of my head. tested it and it works.

[weekend00]

If you meant you are getting data from web site, you need to know what format exactly in the website. If the format in the website changes, you need to modify you codes.

I don't think you can put one group in one row since there are too many fields, unless you just want everything in a memo field.

I found a sample code for get data from InternetExplorer, you take a look at it.

Sub test1()
  Dim wflag As Boolean
  Dim s2 As String
  Dim i1 As Long
  Dim LOIE As InternetExplorer
  Set LOIE = CreateObject("internetexplorer.application")
  With LOIE
    .Visible = True
    .Navigate "www.google.ca"
    wflag = lWait(LOIE)
    For i1 = 0 To LOIE.Document.all.tags("TD").Length - 1
      s2 = LOIE.Document.all.tags("TD").Item(i1).innertext
      If s2 = "NAME 1" Then
        MsgBox LOIE.Document.all.tags("TD").Item(i1 + 1).innertext
        Exit For
      End If
    Next i1
  End With
End Sub
Function lWait(LOIE As Object) As Boolean
  Dim t1 As Long
  Dim t2 As Long
  On Error Resume Next
  t1 = Timer
  t2 = t1 + 60
  Do
    If Timer > t2 Then lWait = False: Exit Function
    If VarType(LOIE.Document) = vbString Then Exit Do
  Loop
  Do
    If Timer > t2 Then lWait = False: Exit Function
    If LOIE.Busy = False Then Exit Do
  Loop
  Do
    If Timer > t2 Then lWait = False: Exit Function
    If VarType(LOIE.Document.ReadyState) = "C" Then Exit Do
  Loop
  Do
    If Timer > t2 Then lWait = False: Exit Function
    If LOIE.Document.ReadyState = "complete" Then Exit Do
  Loop
  lWait = True
End Function