[Solved] Import data from website

[NickMMM]

I'm trying to import data(numbers) from a website where these numbers consistently change(3 different numbers). So I would like to tie a macro to a button that when pressed it grabs the current data from the website open in Internet Explorer. When you Ctrl-F(in IE) and type in "dB" the numbers are to the left of dB. The numbers I need are the 2nd, 3rd, & 4th result to the left of where dB comes up in the document search. Also these numbers may sometimes have 1 number after the decimal(so either 2 characters or 4 i.e. 34 or 34.5) and need to have each(3 values) put into their own cells. Thanks!!

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[Villeroy]

What is your relation to the website you copy from?

[NickMMM]

I view it. That's it not the owner if that's what ur asking

[Villeroy]

Can you ask them for the data you need?
What makes you think that a spreadsheet macro could be of any help getting data from a website?

[NickMMM]

I'm not sure. Maybe an API, some sort of java coding build into to a macro, some way for CALC to grab thE info into the spreadsheet when needed.

[Villeroy]

A few lines of code in any programming language might be able to pull the raw data out of the html table. A tiny application in Java, Ruby or Python can do this on your mobile phone and you can use the downloaded data with anything you like. When you ask any serious programmer he would code something that does not rely on the existence of one particular big fat office suite (350 Megabyte).
Receipts for the Python language:
http://www.dreamincode.net/code/snippet4613.htm
http://www.palewire.com/posts/2008/04/2 ... ad-a-file/

If you are in a commercial relationship with the site owners you can simply ask then for the raw data so you don't have to copy them from the web page. This is the easiest way.

BTW: What is wrong with the built-in Calc method to get table data from html? I mean Insert>Link to external data... works pretty well in most cases.
And why do you post your email (I removed it) but not any link to the data in question?

[NickMMM]

Okay well heres the thing. If you open the URL it sends you to a different(main) page its once you make a few clicks your at where you want to be with this information(but the URL stays the same). It's dependent on the info that is based on different customers your currently working with. So if you just grabbed data from HTML unless it was pulling from the open site in Internet explorer it wouldn't be able to see the information. Also I'm not saying that a few lines of code wouldN't do this because I'm sure it would. I'm just not that talented to actually write the code(I've tried and definately do not know or am missing something quite obvious) I wouldn't need this on my mobile phone though. I will be using a windows OS and OpenOffice Calc which I saw can somehow incorporate java and other coding so I figure it must be possible. The reason I use office is because The place im at wants it in open office due to the fact they think "other(custom programs are crashing open office" so if I build it within there can be no complaints .You seem really knowledgable so I'm hoping you or someone you know can assist. Also I didn't know Calc has a get table from HTML or where that's located. Not sure if that would work with changing data on a non-changing HTML where you don't start off with the info without clicking or inputting different info into that page. Let me know what you think. Thanks! You've been helpful

[NewEnglandErik]

Instead of using a control F to find the dB values and clicking a button, how about going through the web steps to get the dB values on screen and then typing a control-A and then a control-C to copy the IE page text? Try this and then open notepad or other text editor and do a Control-V to paste the text into the editor. If you can then search in that editor and find your dB values, then a calc macro is possible.

I managed to find a code snippet from Andrew on using the system clipboard. I wrote a solution that you Control-A Control-C the page and then run the macro. My test file was an html file with a table and other html artifacts that I added a couple of number values postfixed with 'dB, but I didn't have time to run any javascript against it. Works with whole values or with one decimal place and puts the values in consecutive columns.

Code: Select all

REM  *****  MODULE ClipboardWrk  *****
REM Purpose: Allows the user to Control-A, Control-C a browser page 
REM               and start the  macro called UpdateCalcFrmClip 
REM          this could installed as a menu item from the tool bar 
REM          or as a button
REM The Macro does the following:
REM          1. scan through the text found in the system clipboard 
REM              for numbers  postfixed with "dB"
REM          2. saves all possible representations 1, 1.x, 12,12.x, 
REM              132, 122.x  and saves these values in an array.
REM          3. It then scans the sheet where you want to post these 
REM              values  looking for the next available (empty) rows 
REM examples where the variable StartingCol = 3 (column E) then
REM dB        |A|B|C|D|E|
REM 12.5     |  |  |1|2|5|
REM 134       |  |1|3|4| |
REM 120.9    |  |1|2|0|9|
'
' To use this macro application go to subroutine UpdateCalcFrmClip  
'   and change the values in variables StartingCol, StartingRow and 
'   CollectionSheetName to reflect where you want to 
'   store dB values on your spreadsheet.
'
'  If you give the StartingRow value that is currently in use 
'  the macro will scan the column for unit values. The first after
'  blank cell encountered the StartingRow will be where the macro 
' will start recording the new dB values

Global convertedString$
Global ColVals(4,100) as string ' 135.4dB
Global StartingCol as integer
Global StartingRow as integer
Global CollectionSheetName as string

'SUBROUTINE UpdateCalcFrmClip
'PURPOSE: Main routine to scan the system clipboard and find dB values
sub UpdateCalcFrmClip 
  StartingCol=4 'Column E
  StartingRow=3 'Row 4
  CollectionSheetName="Collections"
  call ConvertClipToText 'Move clipboard text into convertedString$
  call FinddBs 'find dB numbers and add them to the ColVals array
  call UpdateCalc  'add dB numbers to spreadsheet cells
end sub

sub UpdateCalc
  unitArry=array("Hundrds","Tens","Units","Tenths")
  CurCol=StartingCol  'F = tenths
  CurRow=StartingRow
  oDoc=ThisComponent
  oSheets=oDoc.Sheets
  oSht=oSheets.getByName(CollectionSheetName)
  'find blank row to store dB's
  oCell=oSht.getcellbyposition(CurCol-1,CurRow)    
  TstUnits=oCell.getString
  while len(TstUnits)>0
     CurRow=CurRow+1
     'test for an empty units value
     oCell=oSht.getcellbyposition(CurCol-1,CurRow)    
     TstUnits=oCell.getString
  wend
  x=0
  Units=ColVals(x,1)
  While len(Units)>0 
    for i=0 to 3
       dbStr=ColVals(x,i)
       if len(dbStr>0) then
         'test for an empty units value
         oCell=oSht.getcellbyposition(CurCol-i,CurRow)    
         oCell.String=dbStr
       end if
    next i
    CurRow=CurRow+1
    x=x+1
    Units=ColVals(x,1)
  wend  
end sub

Sub ConvertClipToText
  'This clipboard routine was created by Andrew Pitonyak
  'my only adaptation was to make convertedString$ variable a global
  'to pass it on to the next subroutine for processing
  Dim oClip, oClipContents, oTypes
  Dim oConverter 
  ' convertedString$ is now a global
  Dim i%, iPlainLoc%
 
  iPlainLoc = -1
 
  oClip = createUnoService("com.sun.star.datatransfer.clipboard.SystemClipboard")
  oConverter = createUnoService("com.sun.star.script.Converter")
 
'  Print "Clipboard name = " & oClip.getName()
'  Print "Implemantation name = " & oClip.getImplementationName()
  oClipContents = oClip.getContents()
  oTypes = oClipContents.getTransferDataFlavors()
 
  Dim msg$, iLoc%, outS
  msg = ""
  iLoc = -1
  For i=LBound(oTypes) To UBound(oTypes)
    If oTypes(i).MimeType = "text/plain;charset=utf-16" Then
      iPlainLoc = i
      Exit For
    End If
     'msg = msg & "Mime type = " & x(ii).MimeType & " normal = " & x(ii).HumanPresentableName & Chr$(10)
  Next
  If (iPlainLoc >= 0) Then
    convertedString$ = oConverter.convertToSimpleType(oClipContents.getTransferData(oTypes(iPlainLoc)), com.sun.star.uno.TypeClass.STRING)
    MsgBox convertedString$
  End If
End Sub

'SUBROUTINE FinddBs
'PURPOSE: for each occurrence of "dB" in the clipboard
'         store the value in ColVals array
sub FinddBs
   x=0  'occurrence of each dB in clipboard 
   tmp=convertedString$
   j=inStr(1,tmp,"dB",1)
   while j>0
      if j>7 then
         valstr=mid(tmp,j-5,5)  '123.4dB'
         k=inStr(1,valStr,".",1)
         if k>0 then
            tenths=mid(valStr,k+1,1)
            hundrds=mid(valStr,1,1)
            tens=mid(valStr,2,1)
            units=mid(valStr,3,1)
         else
            'no decimal pt, assume 1 to 3 digit value
            r=len(ValStr)
            hundrds=mid(valStr,r-2,1)
            tens=mid(valStr,r-1,1)
            units=mid(valStr,r,1)
            tenths=""
         end if
         if numStr(hundrds)=false then
            hundrds=""
         end if
         if numStr(tens)=false then
            tens=""
         end if
         if numStr(units)=false then
            units=""
         end if
         ColVals(x,3)=hundrds
         ColVals(x,2)=tens
         ColVals(x,1)=units
         ColVals(x,0)=tenths
         x=x+1
         tmp=mid(tmp,j+2,len(tmp))
         j=inStr(1,tmp,"dB",1)
      end if
   wend
end sub

function numStr(numbrStr)
   'return true if string is numeric
   x=asc(numbrStr) ' "0"=48 "9"=57 in ascii
   if x>47 and x<58 then
      numStr=True
   else
      numStr=false
   end if
end function

[NickMMM]

Yea, I can find it in notepad but how do I create that macro?

[NewEnglandErik]

I put a coded solution in my last reply.

[NickMMM]

You are some type of god. Thank you!