• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Facing problem while importing data by using web query

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,


I am importing data by using web query from the following link.


http://www2.isda.org/functional-areas/protocol-management/protocol-adherence/8/A


The link has a table, this table has 8 different columns.

I am importing the entire table.


There are 4 columns named Method in the table .

The problem is, there is a "tick mark" in these columns. (like the nike logo).


These particular tick marks are not captured while importing data.

The entire purpose of the macro is to view data which has tick marks.


I am not able to understand how I will get data with these tick marks.

Without these tick marks the macro is of no use.


When I copy this manually, I get the entire data.

The tick marks are "Picture" if check on excel.


Can we import these pictures using web query, or is there any other option to import pictures.


Can anyone help me in this please.
 
Sachinbizboy


The Excel Web query is only useful on HTML Tables and it appears that that table at your link is an adobe or java script or other format of data table

As such you can't access that data using the web query


You could try contacting the web sites authors and asking do they have it available in some other format like an externally linkable DB or basic HTML formatted Table
 
Hui Sir, thanks a lot for the help.


So I think I can not use the table with current format.


I will try to contact the Web site as abvised by you.


Sir is there any way to check if the site has an externally linkable DB or basic HTML formatted Table without asking them, I mean can we do this check on our own.


Have a nice day.
 
Hi Sachin ,


If you don't mind a workaround , do the following :


Open the webpage in your browser , and save it.


Now open this saved webpage in Excel ; you will see the participants , and against each participant , if the tick mark was present in the webpage , you will see a picture placeholder ; you can now select all the pictures , and replace them with any tickmark picture of your choice.


Narayan
 
Narayan Sir thanks a lot for the help.


Please pardon my ignorance, could you please tell me how to save a webpage.


I opened the webpage, but could not find save option.


If I press Control S, then also I am not able save the webpage.

I get a warning about restrictions on my PC.


I press F12 but another window opened which has a save option.


P.S. - Sir I think this is problem of my PC.

I do not have option for saving in the "Page".


I tried saving webpage on anohter PC.

It is getting saved, I think the problem is with my PC.


I have to get it resolved.


Have a nice day.
 
Hi,


can anyone please guide me on how to activate "save as" option for webpage.


I can not see "Save as" option on the "Page" control.


I checked on other pc it is working on that particular PC.


I tried searching in Internet Options on my PC but could not find.
 
Hi Sachin ,


I think it may be a problem of the software i.e. the browser itself ; there are different configurations possible when software such as Internet Explorer is installed ; any home PC user will install the full version of IE ; however , in a corporate setup , it is possible that the IT department may have a customised configuration of IE , which does not have certain features , which is installed on all user PCs.


Why don't you check up with your IT department ? Or try using a different browser ?


Narayan
 
Sir,


Thanks a lot for the help


I checked with my team, but no success.

Infact from last 2 hours we are working on this but still could not find out reason.


I think the problem must be " it is possible that the IT department may have a customised configuration of IE".


I will check with them again.
 
Sir, thanks a lot.


I think this must be the reason.

I am forwarding this link to my team.


I will work with them to get this option activated.


P.S. - I can hardly see or click any option for IE, so will work with them & confirm once done.
 
You could try this to save the webpage

[pre]
Code: 
Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Sub DownloadFile(ByVal URL As String, ByVal Filename As String)

Dim lRet As Long
Call URLDownloadToFile(0, URL, Filename, 0, 0)

End Sub

Sub Test()
DownloadFile "http://chandoo.org/wp/", "C:chandoo.htm" 'enter the website address and the output file name
End Sub
[/pre]
 
Narayan Sir, you are great, this is working.


The tick marks are geting imported by using this method.


Now I will start working on it, thanks a lot for the help.


Have a nice weekend.


Thanks everyone for the help.
 
Sir,


I am able to import the data with Pictures.

But the problem is when I import the data, I get old data.


How can I get refreshed data every time.


For example I have saved the web pages on 9 April & running macro today.

Ideally I should get data for 12 April but I am getting data for 9.


Can anyone help me in this please.
 
Hi Sachin ,


I copied Dave's macro , and ran it , and I get the updated data.


#115 , AGC Life Insurance Company has been updated on 11-Apr-2013.


Narayan
 
Sir I will check again, I think I am making some mistake.


Thanks a lot for the help.


Sir I tried by using Dave's method a while ago, but it was not working due to my misake.


But the code I am running is different and this particular code is not giving me refreshed data. But I will try with Dave's method now.

PFB the code I am using.

[pre]
Code: 
Sub B()
Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("B").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Sheets.Add
ActiveSheet.Name = "B"

Application.DisplayAlerts = False
Workbooks.Open Filename:= _
"aaaaaaaaaa.aaaDesktopaab.htm"
Application.DisplayAlerts = True

Rows("183:683").Copy

Workbooks("ISDA CP Adhered List Macro.xlsb").Activate

Sheets("b").Select
Range("a1").Select
Sheets("b").Paste

Columns("A:H").WrapText = False

Columns("A:H").ColumnWidth = 15
Columns("b:b").ColumnWidth = 50
Range("A1").Select

Application.ScreenUpdating = True
Workbooks("b.htm").Close
Workbooks("ISDA CP Adhered List Macro.xlsb").Activate
Call C
[/pre]
 
Sir I am using the following code (as suggested by Dave).


I have made once change in the code, I am saving the file in xls format.

The original code has htm format


But if I try to save in htm format I am not able to copy the table.


When I save this file in xls format, I am able to copy the data I want.

The problem is saving the file takes too much time & thought the pictures are imported they are not populated properly.


I am not able to see the picture.


Can anyonce help in this please.


Option Explicit


Private Declare Function URLDownloadToFile Lib "urlmon" _

Alias "URLDownloadToFileA" _

(ByVal pCaller As Long, _

ByVal szURL As String, _

ByVal szFileName As String, _

ByVal dwReserved As Long, _

ByVal lpfnCB As Long) As Long


Private Sub DownloadFile(ByVal URL As String, ByVal Filename As String)


Dim lRet As Long

Call URLDownloadToFile(0, URL, Filename, 0, 0)


End Sub


Sub Test()

DownloadFile "http://www2.isda.org/functional-areas/protocol-management/protocol-adherence/8/A", _

"pdddd01sss_Datasachin.sonawaneDesktopChandanNewMethodA.xls" 'enter the website address and the output file name

End Sub
 
You must log in or register to reply here.
Back
Top