How to Fetch Web Data into Excle

[boilingrony]

Hi There

Can Anyone tell me how to Fetch record from Website database into Excel Through Excel macro code.

[Jan Karel Pieterse]

You will need a page on the site that returns a html table with the record information.

[boilingrony]

There is a database page to Access the data. but there is a limitation of no. i can only track few NO.

[Jan Karel Pieterse]

What do you mean a limitation of no?
Does the page only show a part of the records that are in the database?
In that case, you'll have to talk to the guy who manages the website and ask him to have the page return more.

[boilingrony]

Well Yes..

Actually the database is In SQL which is Accessed by the we people at different Locations.Noe the problem is that We have to search it from the web..it takes time to get the details From The Web.Yes i have talked with the developer of the site They are not authorized to do that.

[boilingrony]

Still Wating For Your Reply..

[Jan Karel Pieterse]

I'm still not sure what your problem is exactly.
Could you perhaps provide an example url where I can see the data and try to explain what it is that's missing?
Can you use a web query from within Excel to pull in the dat directly? (Data, Get eternal data, new web query)

[boilingrony]

http://59.163.53.210/n_ctrack_dom.asp is the url from where i have to fetch the data into the excel sheet.

There is C/no say b123456..
the details of that should come automatically into the excel file

[Jan Karel Pieterse]

OK, got it now. Unfortunately, this requires VBA programming and takes much more time than I have available at the moment.
I can write the code for you commercially, but I doubt if that is what you want, correct?

[boilingrony]

I Know that That is Why i Joined this form.
I have Developed a code earlier But it is not working with this link.
Here find that code.
[vba]Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim ShNew As Worksheet
Dim r As Integer
Dim Cell As Range
Dim URL As String
Set Sh = Worksheets("Sheet1")
With Sh
Set Rng = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
Set ShNew = Worksheets.Add
r = 1
For Each Cell In Rng
URL = "URL;http://www.firstflight.net/n_contrac_new.asp?tracking1=" & Cell.Value
With ShNew.QueryTables.Add(Connection:=URL, Destination:=ShNew.Cells(r, 1))
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "21"
.Refresh BackgroundQuery:=False
End With
With ActiveSheet
.QueryTables(1).Delete
.Cells(r, 1).Resize(3).EntireRow.Delete
End With
r = r + 1
Next Cell
End Sub[/vba]
Now the Problem is that it is not working with http://59.163.53.210/n_ctrack_dom.asp this link

That is why i Required & want You Help..

[Jan Karel Pieterse]

Could you give me a valid tracking number?

[boilingrony]

Here Find the Few..

ZB6424134
ZB6430739
ZD6419468
ZB6423408

[Jan Karel Pieterse]

I tried to adopt this code to extract the results to a file, but it fails.

Problem is I don't know how to pas the track number to the website:

[vba]Sub DoIt()
Dim sURL As String
Dim sData As String
Dim xmlhtp As MSXML2.XMLHTTP40
Dim xmlDoc As DOMDocument

sURL = "http://59.163.53.210/n_ctrack_dom.asp"
Set xmlhtp = New MSXML2.XMLHTTP40
sData = "ZB6424134"
With xmlhtp
.Open "post", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.send sData
Set xmlDoc = New DOMDocument
xmlDoc.loadXML .responseText
MsgBox .responseText
Open ThisWorkbook.Path & "\WebQueryResult.html" For Output As #1
Write #1, .responseText
Close #1
End With
End Sub
[/vba](needs a reference to the Microsoft XML library)

[boilingrony]

Im sorry Ididnt Get U..

[boilingrony]

SIr im still waiting to get a solution.

I have downloaded & installed Microsoft XML library But still it is not working Properly.

[boilingrony]

Still waiting for nay Reply.

[mancubus]

that site requires login to related page.
this may help with this.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=399

then you must google stg like "excel vba pull data from web"
here is a sample:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=576

[Jan Karel Pieterse]

Sorry, I was too busy!
mancubus seems to have given you some useful pointers though.

[boilingrony]

mancubus Thanks Fro Your Reply ..but it not working..


Can you please help me with the Link which i have provided.

[mancubus]

perhaps recording a web query macro with real data and modifiying the code in post#10 will help.

if i were assigned such a project, keeping in mind that i'm not experienced, i would contact the web designers from it department -or my customer representative, if any, who may built connection with them- of the forwarder company. to get the table, form, element id's etc.

since these are one word parameters, companies may help on this as part of customer satisfaction policies.

for experts, it may be easy to extract related info from html document.
below may give an idea...
http://www.tushar-mehta.com/publish_...ages_services/