Great video. The video is demoing what I am trying to do. however, I can not get your logic to work for my needs. I need a way of exporting three columns of data from my website to Excel (list view: Header and rows of data). I need to code to be modular so that I can reuse the code in many Microsoft office tools (Excel, Word, PowerPoint). Can you help me with this request? When I try the approach in your video - I get the error message "run-time error 91: Object variable or with block variable not set"
Hi can you do a video to pull API data from a website database into Excel (via VBA). if not, can you create a video of a generic VBA tool to be used? I would like to retrieve data from my website DB (into my Excel tools).
What about pushing data out of Excel to a ASP via an API. Beside the obvious "What's the URL" and credentials and whatnot, what about the process for reading a table, assigning the variable names with the data read, and then calling an API to load it to an external server? Is this something you can cover?
how do i hit the API if a certain level of authorization is required. I mean if the content requires a special permission, i.e, only a person with an ID in that specific application can extract data out of it ?
@Bane Data Solution Ltd : For me Its not loading the XML as my API URL is loading with User ID and Password. How to pass the USer ID and Password. If Not xmlDoc.LoadXML(strResp) Then MsgBox "NOt Loaded" End If Always its returning NOt Loaded. I am following the below code but no luck. strURL = ws.[APIUrl] Dim hReq As New WinHttpRequest hReq.Open "GET", strURL, False, "userid", "password" hReq.Send
Hi ! Thank you for this video. I am trying to replicate the code but I keep receiving the following error: Run-time error 91, Object variable or With block variable not set, at the following code: For Each xChild In xnode.ChildNodes . Can you please help ? Thank you ! Marian
Is there any way that i can harvest data such as dealers name, number, area, address, and the category in which they deals. I have to collect data into excel sheet into a thousand number at a single time. If there is such a tools or any vba code kindly update me on my mail id.
Hi everyone, I have office 2013 and 2016 and windows 10 and when i use this code i get Run-time error"91" Object variable or with block variable not set. VBA colored me this line of code For Each xChild In xnode.ChildNodes. Have you got any idea, how can i solve this problem.
I got it working. Sub PullAPIdata() Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("API") Dim strURL As String strURL = ws.[apiURL] Dim hReq As New winhttprequest 'acts as a browser hReq.Open "GET", strURL, False hReq.send Dim strResp As String strResp = hReq.ResponseText Dim xmlDoc As New MSXML2.DOMDocument60 If Not xmlDoc.LoadXML(strResp) Then MsgBox "Load error" End If Dim xnodelist As IXMLDOMNodeList Set xnodelist = xmlDoc.getElementsByTagName("observation") Dim xchild As MSXML2.IXMLDOMNode Dim xnode As MSXML2.IXMLDOMNode Set xnode = xnodelist.Item(0) Dim obAtt1 As MSXML2.IXMLDOMAttribute Dim obAtt2 As MSXML2.IXMLDOMAttribute Dim intRow As Integer intRow = 2 Dim strCol1 As String strCol1 = "A" Dim strCol2 As String strCol2 = "B" Dim dtVal As Date Dim dblRate As Double Dim strVal As String Dim strVal2 As String For Each xnode In xnodelist Set obAtt1 = xnode.Attributes.getnameditem("date") Set obAtt2 = xnode.Attributes.getnameditem("value") ws.Cells(intRow, 1) = obAtt1.Text If obAtt2.Text = "." Then ws.Cells(intRow, 2) = "" Else ws.Cells(intRow, 2) = obAtt2.Text End If intRow = intRow + 1 Next xnode Set hReq = Nothing Set xmlDoc = Nothing End Sub
After spending hours trying to figure out how to do something like this, this video put me straight on the right lines! Great job, thanks :)
You choose to use FRED data ... the best I could find ... Thank you so much
In FREAKING credible demonstration! Thank you very much!!!
Awesome tutorial - simple and detailed at the same time. Thank you very much.
Canon!
Terrible!
Très for!
C'est juste Bomboclat! Merci beaucoup...
Thank you so much for this tutorial. I found it extremely helpful.
i need an advice.. can we get job if we have some handy tools of VBA coding.. or company prefer to use proper software instead of MS Excel VBA.
@@sohailimran42 Lots of companies use VBA,, its integral to many City firms. I really can't be doing with snobs who think VBA isn't a proper tool.
Great work, thank you for sharing this!
This is some good stuff! Thanks for the tutorial!
Great tutorial. I am having issues with last steps of API data into excel . Can you share sample file? Thanks
Great video. The video is demoing what I am trying to do. however, I can not get your logic to work for my needs. I need a way of exporting three columns of data from my website to Excel (list view: Header and rows of data). I need to code to be modular so that I can reuse the code in many Microsoft office tools (Excel, Word, PowerPoint). Can you help me with this request? When I try the approach in your video - I get the error message "run-time error 91: Object variable or with block variable not set"
Thank you so much. It's very helpful for me.
cheers do you by any chance know how to pull the wallet information so you can autoupdate the amount of coins you have on the used exchanges
very very good
Excellent tutorial. Can you prepare a video on parsing json with Excel vba?
Raman Mahapatra I will challenge myself to do that!
Hi can you do a video to pull API data from a website database into Excel (via VBA). if not, can you create a video of a generic VBA tool to be used? I would like to retrieve data from my website DB (into my Excel tools).
Awesome and thank you
What about pushing data out of Excel to a ASP via an API. Beside the obvious "What's the URL" and credentials and whatnot, what about the process for reading a table, assigning the variable names with the data read, and then calling an API to load it to an external server? Is this something you can cover?
Very useful. Thank you
Good example
how do i hit the API if a certain level of authorization is required. I mean if the content requires a special permission, i.e, only a person with an ID in that specific application can extract data out of it ?
Hi. I need a way for Google Maps to capture my location automatically (without my having to enter my address). Is this possible through VBA?
Having the same issue that Jaka is having with the Runtime Error 61, running Windows Pro E3 Office 365 type layout.
Very useful
Any tutorial for JSON?
what was the answer about DOMDocument60 please
Do you mean how to get DOMDocument available? Tools -> References -> Microsoft XML vX.X - choose highest value of X...
how use api google sheet in vba excel, same use in source code in video
Does not work for osx, nor does MacScript()
I'd go insane from writing all the DIMs
Thanks in a million.
I am tryingto get Data from Ebay like my order list, units, price, date etc but I have to login, is there a way to do that?
It looks as though that is possible...
developer.ebay.com/devzone/xml/docs/Concepts/MakingACall.html
Nice, thanks.
can I please get the code and URL?
I can't get DOMDocument on my VBA, only DOMDocument60, which doesn't work... any suggestions?
Never mind, I figured it out
What did you do to fix this and make it work?
I believe the solution was clicking Microsoft XML v3.0 instead of v6.0. For some reason that works for Windows 8.1
Thank you!
With some missing libraries have to install a .dll file if a library doesn't exist.
@Bane Data Solution Ltd : For me Its not loading the XML as my API URL is loading with User ID and Password. How to pass the USer ID and Password.
If Not xmlDoc.LoadXML(strResp) Then
MsgBox "NOt Loaded"
End If
Always its returning NOt Loaded.
I am following the below code but no luck.
strURL = ws.[APIUrl]
Dim hReq As New WinHttpRequest
hReq.Open "GET", strURL, False, "userid", "password"
hReq.Send
Hi ! Thank you for this video. I am trying to replicate the code but I keep receiving the following error: Run-time error 91, Object variable or With block variable not set, at the following code: For Each xChild In xnode.ChildNodes . Can you please help ? Thank you ! Marian
Marian I've the same problem, did you solved it already?
api_key don't work. Now, you have to register and change "observations" in "seriess", ecc. ecc.
Is there any way that i can harvest data such as dealers name, number, area, address, and the category in which they deals.
I have to collect data into excel sheet into a thousand number at a single time.
If there is such a tools or any vba code kindly update me on my mail id.
Hi everyone,
I have office 2013 and 2016 and windows 10 and when i use this code i get Run-time error"91" Object variable or with block variable not set.
VBA colored me this line of code For Each xChild In xnode.ChildNodes.
Have you got any idea, how can i solve this problem.
Did you get an answer to your question, I am having the same issue?
I'm getting the same error.
I've changed that line to For Each xchild In xnodelist and the script runs through the end, but no data is pulled.
I got it working.
Sub PullAPIdata()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("API")
Dim strURL As String
strURL = ws.[apiURL]
Dim hReq As New winhttprequest 'acts as a browser
hReq.Open "GET", strURL, False
hReq.send
Dim strResp As String
strResp = hReq.ResponseText
Dim xmlDoc As New MSXML2.DOMDocument60
If Not xmlDoc.LoadXML(strResp) Then
MsgBox "Load error"
End If
Dim xnodelist As IXMLDOMNodeList
Set xnodelist = xmlDoc.getElementsByTagName("observation")
Dim xchild As MSXML2.IXMLDOMNode
Dim xnode As MSXML2.IXMLDOMNode
Set xnode = xnodelist.Item(0)
Dim obAtt1 As MSXML2.IXMLDOMAttribute
Dim obAtt2 As MSXML2.IXMLDOMAttribute
Dim intRow As Integer
intRow = 2
Dim strCol1 As String
strCol1 = "A"
Dim strCol2 As String
strCol2 = "B"
Dim dtVal As Date
Dim dblRate As Double
Dim strVal As String
Dim strVal2 As String
For Each xnode In xnodelist
Set obAtt1 = xnode.Attributes.getnameditem("date")
Set obAtt2 = xnode.Attributes.getnameditem("value")
ws.Cells(intRow, 1) = obAtt1.Text
If obAtt2.Text = "." Then
ws.Cells(intRow, 2) = ""
Else
ws.Cells(intRow, 2) = obAtt2.Text
End If
intRow = intRow + 1
Next xnode
Set hReq = Nothing
Set xmlDoc = Nothing
End Sub
Thanks for the help kris !
Genius
thank you:)
good tutorial but very bad naming conventions
Where is the json references huh? Tell me that!
Sorry, I'm not really a JSON, man. Make a vid and post it here!
All this work to get data from website ... Not simple at all
Nothing difficult is ever easy