My problem is everyone is using other coding languages to construct their API calls ...and I'm not much good at picking the bones out of what people have implemented in other languages & translating them into what I need to do in VBA!
Once I have assembled call in the right structured format, I'm off to the races!
My problem is everyone is using other coding languages to construct their API calls ...and I'm not much good at picking the bones out of what people have implemented in other languages & translating them into what I need to do in VBA!
Once I have assembled call in the right structured format, I'm off to the races!
Yes, I got a load of verbose stuff back, but I don't think it's right (which suggests my API call was wrongly constructed) - what I would be expecting back (if the API call was constructed correctly) would be something like seen when manually typing this URL...
Yes, I got a load of verbose stuff back, but I don't think it's right (which suggests my API call was wrongly constructed) - what I would be expecting back (if the API call was constructed correctly) would be something like seen when manually typing this URL...
I've only ever used a simple url request with an api key on one site that downloads the info I need, and that is in Excel as well.
You do not say what you actually want to do with it? and that link you supplied appeared to use different api code for different purposes like Billing etc.
I've kludged together a lot of my own APIs (interfacing with Ebay etc), but they weren't REST type APIs (which is what the Shopify API here is)... additionally, I was able to source sample VBA code from all over the web towards getting an end result I needed ...but I've never managed to get off the ground with a REST API call, so stage one is just to get the basic API call working (structured in the correct format) ....parsing the data will be a learning curve but that's the next stage!
REST APIs are now the standard, I'd be surprised if nobody in the VBA world has managed to deploy a REST API using VBA.
Am trying to retrieve data as JSON with the following code, but am just getting an XML response: Public Sub vbajson() Dim http As Object Dim sht As Worksheet Dim authKey As String ...
I was trying to produce that manually, but couldn't find the right combination yet. So, I cheated and lifted that up from the test site you provided earlier.
Just in case anyone is trying to use the above code with the supplied key/secret I've now deleted on my test site keys as I've now imported some live/personal data
Just in case anyone is trying to use the above code with the supplied key/secret I've now deleted on my test site keys as I've now imported some live/personal data
What that bit is, is your 'API Key' & 'Secret' joined (with a ":" separating them) & hashed, there are a couple of ways to achieve this...
1. Use Firefox developer console & drop in your required URL (which is provided in your shopify dashboard), for example, if you API Key (username) is d6615cf12ea04c957a470c5d19d50e52& your password (Secret) is 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&your shopify address is my-store.myshopify.com then the full URL to request your Shopify products will look like this...
but for your VBA code, you need the hashed version ofd6615cf12ea04c957a470c5d19d50e52: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
Fire up a firefox browser, from the top right settings menu, select 'Web developer' then 'Network' ...now copy/paste the URL into the address bar (& hit enter), now select 'headers' tab at the top of screen ...scroll down & you will see authentication field - it's that string you need to put in your code above
Or...
2. add a module to access so you can hash your API Key & Secret yourself, I found this link which shows the code to do this...
the contents of variable hashed is the string you need when making an API call
Code:
Function Base64Encode(sText)
Dim oXML, oNode
Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
Set oNode = oXML.createElement("base64")
oNode.DataType = "bin.base64"
oNode.nodeTypedValue = Stream_StringToBinary(sText)
Base64Encode = oNode.Text
Set oNode = Nothing
Set oXML = Nothing
End Function
'Stream_StringToBinary Function
'2003 Antonin Foller, http://www.motobit.com
'Text - string parameter To convert To binary data
Function Stream_StringToBinary(Text)
Const adTypeText = 2
Const adTypeBinary = 1
'Create Stream object
Dim BinaryStream 'As New Stream
Set BinaryStream = CreateObject("ADODB.Stream")
'Specify stream type - we want To save text/string data.
BinaryStream.Type = adTypeText
'Specify charset For the source text (unicode) data.
BinaryStream.Charset = "us-ascii"
'Open the stream And write text/string data To the object
BinaryStream.Open
BinaryStream.WriteText Text
'Change stream type To binary
BinaryStream.Position = 0
BinaryStream.Type = adTypeBinary
'Ignore first two bytes - sign of
BinaryStream.Position = 0
'Open the stream And get binary data from the object
Stream_StringToBinary = BinaryStream.Read
Set BinaryStream = Nothing
End Function
'Stream_BinaryToString Function
'2003 Antonin Foller, http://www.motobit.com
'Binary - VT_UI1 | VT_ARRAY data To convert To a string
Function Stream_BinaryToString(Binary)
Const adTypeText = 2
Const adTypeBinary = 1
'Create Stream object
Dim BinaryStream 'As New Stream
Set BinaryStream = CreateObject("ADODB.Stream")
'Specify stream type - we want To save text/string data.
BinaryStream.Type = adTypeBinary
'Open the stream And write text/string data To the object
BinaryStream.Open
BinaryStream.Write Binary
'Change stream type To binary
BinaryStream.Position = 0
BinaryStream.Type = adTypeText
'Specify charset For the source text (unicode) data.
BinaryStream.Charset = "us-ascii"
'Open the stream And get binary data from the object
Stream_BinaryToString = BinaryStream.ReadText
Set BinaryStream = Nothing
End Function
The bit that caught me out (& thankfully the DBGuy addressed) is the bit in bold. What that bolded bit is, is your 'API Key' & 'Secret' hashed, there are a couple of ways to achieve this...
1. Use Firefox developer console & drop in your required URL (which is provided in your shopify dashboard), for example, if you API Key (username) is d6615cf12ea04c957a470c5d19d50e52& your password (Secret) is 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&your shopify address is my-store.myshopify.com then the full URL to request your Shopify products will look like this...
but you need the hashed version of d6615cf12ea04c957a470c5d19d50e52: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
Fire up a firefox browser, from the top right settings menu, select 'Web developer' then 'Network' ...now copy/paste the URL into the address bar (& & hit enter), now select 'headers' tab at the top of screen ...scroll down & you will see authentication field - it's that string you need to put in your code above
The other way is to add a module to access so you can hash it your self, I found this link which shows the code to do this...
the contents of variable hashed is the string you need when making an API call
Code:
Function Base64Encode(sText)
Dim oXML, oNode
Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
Set oNode = oXML.createElement("base64")
oNode.DataType = "bin.base64"
oNode.nodeTypedValue = Stream_StringToBinary(sText)
Base64Encode = oNode.Text
Set oNode = Nothing
Set oXML = Nothing
End Function
'Stream_StringToBinary Function
'2003 Antonin Foller, http://www.motobit.com
'Text - string parameter To convert To binary data
Function Stream_StringToBinary(Text)
Const adTypeText = 2
Const adTypeBinary = 1
'Create Stream object
Dim BinaryStream 'As New Stream
Set BinaryStream = CreateObject("ADODB.Stream")
'Specify stream type - we want To save text/string data.
BinaryStream.Type = adTypeText
'Specify charset For the source text (unicode) data.
BinaryStream.Charset = "us-ascii"
'Open the stream And write text/string data To the object
BinaryStream.Open
BinaryStream.WriteText Text
'Change stream type To binary
BinaryStream.Position = 0
BinaryStream.Type = adTypeBinary
'Ignore first two bytes - sign of
BinaryStream.Position = 0
'Open the stream And get binary data from the object
Stream_StringToBinary = BinaryStream.Read
Set BinaryStream = Nothing
End Function
'Stream_BinaryToString Function
'2003 Antonin Foller, http://www.motobit.com
'Binary - VT_UI1 | VT_ARRAY data To convert To a string
Function Stream_BinaryToString(Binary)
Const adTypeText = 2
Const adTypeBinary = 1
'Create Stream object
Dim BinaryStream 'As New Stream
Set BinaryStream = CreateObject("ADODB.Stream")
'Specify stream type - we want To save text/string data.
BinaryStream.Type = adTypeBinary
'Open the stream And write text/string data To the object
BinaryStream.Open
BinaryStream.Write Binary
'Change stream type To binary
BinaryStream.Position = 0
BinaryStream.Type = adTypeText
'Specify charset For the source text (unicode) data.
BinaryStream.Charset = "us-ascii"
'Open the stream And get binary data from the object
Stream_BinaryToString = BinaryStream.ReadText
Set BinaryStream = Nothing
End Function
and received an output in strReturn, but did not know what to do with it after that?
