Please could you confirm how I can reference a specific QuickFile url? i.e. I want to open the estimates page when a macro has run, I currently have the following url request
However when the above link is followed it takes me to the dashboard?
Many thanks
Rob
The url is as follows
/sales?t=est&o=0
What kind of “macro” are you referring to here? Is this some custom javascript you’ve added in your account or is it something in another piece of software that you want to redirect you to QuickFile?
I am trying to open the estimates page automatically once some code is run, the following is written at the end of the code
'Dim URL As String
'URL = “Quickfile address/sales?t=est&o=0”
'ActiveWorkbook.FollowHyperlink URL
Apologies, this is a macro run in excel, the intention is to redirect to the estimates webpage, I would already be logged in as administrator.
The documentation for FollowHyperlink suggests that rather than simply handing off the URL to your browser for it to load, the function actually downloads the data within Excel and then hands the downloaded data off to the browser instead. You might have more luck with
Shell("rundll32 url.dll,FileProtocolHandler " & URL)Thanks Ian, this appears to be a better solution, is there an easy way to direct to the last estimate created?
Many thanks
Rob
If you know its ID you can presumably link directly to it, but otherwise I’m not sure how you could do it. I can picture how you could use a bit of custom JavaScript in QuickFile to redirect to whichever one is at the top of the list on the index page, but that won’t necessarily be the one that was created most recently.
The ID is returned in the response body on the API call, could I reference this?
Rob
You should be able to, yes. I don’t have any estimates in my QuickFile but I can see when I click through to a sales invoice the URL looks like ..../sales/preview?invoiceID=12345678, if you can find the equivalent URL pattern for estimates you should be able to plug the InvoiceID from the API response into that pattern to generate the right URL.
Yes I have the same pattern for the estimates, what I am unsure of is how to plug the invoice ID from the API response and include it in the URL
You should be able to get away with something like
Shell("rundll32 url.dll,FileProtocolHandler https://mybusiness.quickfile.co.uk/sales/preview?invoiceID=" & IdFromAPI)
or you might need to quote the URL:
Shell("rundll32 url.dll,FileProtocolHandler ""https://mybusiness.quickfile.co.uk/sales/preview?invoiceID=" & IdFromAPI & """")
either way, you can get away without having to worry about URL-encoding the invoiceID parameter as it’s guaranteed to be just digits.
Edit: or do you mean you don’t know how to get the ID out of the response body in the first place? Can you share the code you’re using to call the API (with API key and app ID suitably redacted of course)?
Hi Ian
The API call is using the “Invoice_Create” method, this is the code used for the post
Set oXML = CreateObject("Microsoft.XMLDOM")
oXML.async = False
oXML.Load ("QuickfileUpload.xml")
XMLHttp.Open "POST", "https://api.quickfile.co.uk/xml", False
XMLHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
XMLHttp.send (oXML.XML)
If (XMLHttp.readyState = 4) Then
XMLResponse = XMLHttp.responseText
End If
Dim xmldoc As Object
Dim xmlnode As Object
Set xmldoc = CreateObject("MSXML2.DOMDocument")
xmldoc.setProperty "SelectionLanguage", "XPath"
xmldoc.LoadXML XMLHttp.responseText
For Each xmlnode In xmldoc.SelectNodes("//*[contains(name(),'LogFileName')]")
Debug.Print xmlnode.Text
Next
xmldoc.Save ThisWorkbook.Path & "\WinCalc_response.xml"
If (XMLHttp.readyState = 4) Then
XMLResponse = XMLHttp.responseText
MsgBox XMLResponse
End If
Set XMLHttp = Nothing```
I get the following response back which I could import into excel and maybe reference the cell where the invoide ID is located?
```<?xml version="1.0" encoding="UTF-8"?>
-<Invoice_Create>
-<Header>
<MessageType>Response</MessageType>
<SubmissionNumber>b9159a892f6cff4edb56b67e906eb5d2</SubmissionNumber>
</Header>
-<Body>
<InvoiceID>12579244</InvoiceID>
<InvoiceNumber>Q7669</InvoiceNumber>
<LineItemsCreated>1</LineItemsCreated>
<InvoiceTotal>234.00</InvoiceTotal>
</Body>
</Invoice_Create>```
Thanks
RobYou can actually simplify things quite a bit here - the trouble with coding by example, particularly for things like VBA where things have evolved so much over the years, is that many of the highest ranked examples on Google are well out of date (they’re high ranked simply because they’ve been around for longer and therefore have more people linking to them).
XMLHTTPRequest has a responseXML property that gives you the parsed XML response as a DOM document, then you can use SelectSingleNode or SelectNodes to find the bits you’re interested in with XPath:
XMLHttp.Open "POST", "https://api.quickfile.co.uk/xml", False
XMLHttp.setRequestHeader "Content-Type", "application/xml"
XMLHttp.send (oXML.XML)
If (XMLHttp.readyState = 4) Then
InvoiceID = XMLHttp.responseXML.SelectSingleNode("/Invoice_Create/Body/InvoiceID").Text
InvoiceNum = XMLHttp.responseXML.SelectSingleNode("/Invoice_Create/Body/InvoiceNumber").Text
SelectedButton = MsgBox "Created estimate " & InvoiceNum & ", open in QuickFile?", vbYesNo
If (SelectedButton = vbYes) Then
Shell("rundll32 url.dll,FileProtocolHandler ""https://mybusiness.quickfile.co.uk/sales/preview?invoiceID=" & InvoiceID & """")
End If
End If
(NB I’ve also corrected your initial POST, which should be application/xml rather than application/x-www-form-urlencoded)
Many thanks Ian, I really appreciate your help here, with my limited knowledge of coding I find it quite amazing that I have managed to write the program in the first place, I knew my coding was raw but it did work.
I have updated the code with your help and it works a treat, once again many thanks.
Kindest regards
Rob
This topic was automatically closed after 7 days. New replies are no longer allowed.