Hi everyone! I'm pretty new to excel programming, so I apologize for any awkwardness in wording my problem.
Recently I'm trying to make a database of Bank Identification Number (BIN) from binlist.net using Web Query. It basically works like this : for example I want all the information for the bank identification number of 515874, so what would I do is use a web query using http://binlist.net/xml/[bank identification number] as data source, in this case it would be this : http://www.binlist.net/xml/515874.
Using a normal web query would land me to this :
<tbody>
</tbody>
all in separate cells, which is what I wanted. I want this type of query automated for the next 100k+ data - The automation that I would like to have is something like in this video : https://www.youtube.com/watch?v=Z9N60AIZQNE (sadly the video maker doesn't explain anything on how to achieve that result).
Since I have numerous data and I can't create a macro using a static web query for this type of automation, I tried using dynamic web query parameter (http://www.binlist.net/xml/"BIN", then in connection properties I change the parameter to 'Get parameter from the following cell' and select the cell containing the bank identification number). However, upon using the dynamic query parameter, it lands me different result than above, into this :
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>Which is problematic since the data would take 3 rows instead of just 1 row like above. Hence, I tried creating a macro to solve it, which is a macro that would create 2 blank rows, then run the query, rinse and repeat. Below is the macro :
Sub Macro6()
'
' Macro6 Macro
'
'
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 1).Range("A1").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Users\Melisa\Desktop\515734_3.iqy", Destination:=Range("$B$1"))
.Name = "515734_3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveCell.Offset(3, -1).Range("A1").Select
Loop
End Sub
However, I ran into several problems :
1. Since the dynamic parameter value is specified to be a locked cell rather than be a relative cell (for example, if I run the query and set the 'get parameter from following cell' to be Sheet1!$A$1 or Sheet1!A1, naturally the value returned in every query loop would always be from cell A1, instead of being relative (ex in the next loop I want the value to be gotten from A4, next is A7, etc). Is there a way to get around this?
2. I'm really really new into macro, so it's difficult to debug and find where did I made a mistake with the code. With the above code, not only I run into problem 1), the next looped query would return the value into the next of the original query result cell like this :
rather than to the cell adjacent to the next bank identification number cell.
This is pretty confusing to me. Is there any fix into the problem above, or is there's any alternative to achieve my original goal? (make an automated web query for numerous data to make it into a database).
Any help is very, very much appreciated!
Recently I'm trying to make a database of Bank Identification Number (BIN) from binlist.net using Web Query. It basically works like this : for example I want all the information for the bank identification number of 515874, so what would I do is use a web query using http://binlist.net/xml/[bank identification number] as data source, in this case it would be this : http://www.binlist.net/xml/515874.
Using a normal web query would land me to this :
| 515874 | 515874 | MASTERCARD | ID | Indonesia | PT BANK MEGA TBK. | CREDIT | PLATINUM | -5 | 120 | 270.67us |
<tbody>
</tbody>
all in separate cells, which is what I wanted. I want this type of query automated for the next 100k+ data - The automation that I would like to have is something like in this video : https://www.youtube.com/watch?v=Z9N60AIZQNE (sadly the video maker doesn't explain anything on how to achieve that result).
Since I have numerous data and I can't create a macro using a static web query for this type of automation, I tried using dynamic web query parameter (http://www.binlist.net/xml/"BIN", then in connection properties I change the parameter to 'Get parameter from the following cell' and select the cell containing the bank identification number). However, upon using the dynamic query parameter, it lands me different result than above, into this :
| 515735 | /Response | ||||||||||||||||||||||||||||||||||||||||||||||
| /Bank | /Bin | /Bin/#agg | /Brand | /CardCategory | /CardType | /CountryCode | /CountryName | /Latitude | /Latitude/#agg | /Longitude | |||||||||||||||||||||||||||||||||||||
| CITIBANK, N.A. | 515735 | 515735 | MASTERCARD | WORLD CARD | CREDIT | US | United States | 38 | 38 | -97
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody> </tbody> |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Sub Macro6()
'
' Macro6 Macro
'
'
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 1).Range("A1").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Users\Melisa\Desktop\515734_3.iqy", Destination:=Range("$B$1"))
.Name = "515734_3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveCell.Offset(3, -1).Range("A1").Select
Loop
End Sub
However, I ran into several problems :
1. Since the dynamic parameter value is specified to be a locked cell rather than be a relative cell (for example, if I run the query and set the 'get parameter from following cell' to be Sheet1!$A$1 or Sheet1!A1, naturally the value returned in every query loop would always be from cell A1, instead of being relative (ex in the next loop I want the value to be gotten from A4, next is A7, etc). Is there a way to get around this?
2. I'm really really new into macro, so it's difficult to debug and find where did I made a mistake with the code. With the above code, not only I run into problem 1), the next looped query would return the value into the next of the original query result cell like this :
rather than to the cell adjacent to the next bank identification number cell.
This is pretty confusing to me. Is there any fix into the problem above, or is there's any alternative to achieve my original goal? (make an automated web query for numerous data to make it into a database).
Any help is very, very much appreciated!
