• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Queries genereren op basis van xml hyperlink

Status
Niet open voor verdere reacties.
P

Pavelichenco

Gebruiker
Lid geworden
5 okt 2011
Berichten
14
Beste Excel Experts!,

Ik vraag mezelf af of ik mijn vraag op de juiste plaats stel of dat ik in een andere sectie moet zijn, maar ik ga het hier proberen. Kon namelijk via de zoekfunctie niet echt iets vinden wat lijkt op mijn vraag.

Case:
Ik heb een Excel bestand met 2 tabbladen. 1 tabblad bevat criteria en een knop om xml links te genereren. Op het andere tabblad komen de hyperlinks te staan.
De hyperlinks halen allemaal data op van een server (als je de hyperlink in de browser plakt wordt er een xml bestand gedownload).
Nu is het zo dat de database slechts 200 records per request kan ophalen, dus als ik 2.000 records wil ophalen moet ik 10 keer een request doen = 10 xml bestanden.
Deze wil ik allemaal in één query hebben. Als ik alles handmatig doe dan krijg ik het wel voor elkaar, maar ik zou graag vba code of iets dergelijks schrijven om dit voor elkaar te krijgen, zodat dit met 1 druk op de knop allemaal gebeurt.
Want wat ik vervolgens met de volledige query wil doen, is deze inladen in Power BI en nette analyses maken voor mijn bedrijf.

Wie o wie kan mij helpen?
 
Is er echt geen andere methode om gegevens uit die database te halen? Dit klinkt wel heel omslachtig. Welke database hebben we het hier over (softwarematig, dus Oracle, SQL Server, My-SQL, ...)?
 
We hebben het over ZoHo Desk. Wij gebruiken dit voor onder andere onze klantenservice. De enige ondersteuning die zij bieden is om door middel van een API data uit hun systeem te trekken. Maar tegelijk zitten daar beperkingen op, namelijk max. 200 records per keer en maximaal X aantal requests op een dag / per organisatie. Wij hebben een aardig uitgebreid abonnement en hebben meer dan genoeg aan het aantal requests. ZoHo maakt het hun gebruikers lastig om externe rapportage tools te gebruiken. Ze bieden zelf immers ook een uitgebreide rapportagetool aan, maar ons bedrijf werkt met Power BI dus willen we ook alles met Power BI.

Heb reeds al bij hen nagevraagd over andere mogelijkheden om met hun database te connecten, maar middels de API is het enige wat ik terugkrijg aan reactie...
 
Data uit een internet explorer instantie halen is niet echt ingewikkeld met VBA. Heb je misschien een werkende url die we kunnen gebruiken om te testen?
 
Ik werk persoonlijk liever niet met Internet Explorer, maar als dat noodzakelijk is dan moet dat maar.
Middels deze URL krijg wordt er een xml bestandje gedownload met informatie, wat in Excel in een Query gezet kan worden. Van dit soort URL's heb ik er tientallen in een sheet staan die dus uiteindelijk gecombineerd moeten worden in 1 query.

https://desk.zoho.com/api/xml/reque...it solutions&selectfields=requests(Ticket Id)

Ben inmiddels ook al wel weer een stukje verder, maar het resultaat is nog erg arbeidsintensief. Daarnaast moet het mogelijk worden dat Excel de Query eens in de zoveel tijd (2x per dag ofzo) vernieuwd, zodat we die data kunnen analyseren. Dit gaan we op een Azure server plaatsten en we kunnen vervolgens met Power BI dat Excelbestand benaderen en die specifieke Query. Het is dus niet noodzakelijk dat de losse queries helemaal verdwijnen, als er maar 1 tussen zit waar alles samengevoegd is.

Mijn dank is groot!
 
Oké, ik ben inmiddels aardig gevorderd. Excel maakt nu op basis van mijn XML hyperlinks allemaal losse queries aan. Wat nog rest is het samenvoegen van de queries. Hier heb ik ook een stukje werkende code voor, alleen is dit statisch. Hij voegt nu altijd query 1 tot en met 10 samen, terwijl dit slechts een voorbeeld is. Als er 6 queries zijn moet hij 6 queries samenvoegen, als er 20 zijn moet hij dat bij alle 20 doen. Hieronder de code:

Code: 
Sheets("Blad1").Select
    Range("Query_1[[#Headers],[Attribute:uri]]").Select
    ActiveWorkbook.Queries.Add Name:="ZoHo Database", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Bron = Table.Combine[B]({#""Query 1"", #""Query 2"", #""Query 3"", #""Query 4"", #""Query 5"", #""Query 6"", #""Query 7"", #""Query 8"", #""Query 9"", #""Query 10""})[/B]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Bron"
    Sheets.Add After:=ActiveSheet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""ZoHo Database""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [ZoHo Database]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "ZoHo_Database"
        .Refresh BackgroundQuery:=False
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

Het gaat met name om het stukje code wat dikgedrukt is. Dit is statisch en moet variabel worden. De queries zullen altijd Query heten en een opvolgend nummer hebben.
Hoe krijg ik dit werkend? Heb al geprobeerd wat hierboven staat in een string te zetten en die aan te roepen op de plek waar nu de queries apart opgesomd staan. Zonder succes. Ik krijg een error dat die string niet kan worden gevonden... Wat gaat er mis en hoe los ik dit op....
 
Waarom zet je geen 20 querytables in 1 werkblad ?

Met refreshall worden ze allemaal tegelijkertijd geactualiseerd.
 
Dat is prima zodra het project is afgerond en we een query hebben die aan de wensen voldoet. Voor nu verandert er nog dagelijks erg veel qua velden die we willen ophalen van de database en wat we uiteindelijk willen analyseren. Het gaat mij er nu dus om dat ik niet iedere dag handmatig iedere hyperlink hoef aan te passen en alles weer in een query te zetten enzovoort.

Edit: De vba code die de queries ophaalt zet alle queries op een apart blad. Weet niet hoe ik dat voor elkaar krijg dat hij alles op 1 blad gooit.
Deze code haalt de queries op:
Code: 
Dim strQuery As String
Sub Queries_ophalen()

Application.ScreenUpdating = False

Sheets("XML_LINKS").Select

AantalRijen = Range("A1048576").End(xlUp).Row

For i = 1 To AantalRijen
    
Sheets("XML_LINKS").Select
    
strQueryName = "Query " & i
strQueryName2 = "Query_" & i
strQueryID = "getrecords?authtoken=caf9fb3083a9bec2ccc237cf26630f32&portal=xperit&department=x"
strQueryID2 = "getrecords_authtoken_caf9fb3083a9bec2ccc237cf26630f32_portal_xperit_department_x"
    
Range("A" & i).Copy
        
        ActiveWorkbook.Queries.Add Name:= _
        strQueryName _
        , Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Bron = Xml.Tables(Web.Contents(""https://desk.zoho.com/api/xml/requests/getrecords?authtoken=caf9fb3083a9bec2ccc237cf26630f32&portal=xperit&department=xperit%20solutions&fromindex=1&toindex=200&selectfields=requests(Ticket Id, Status, Ticket Closed Time, Created By, Created Time, Category, Ticket Owner, Ticket Id, Status, Ticket Closed Time, Created By, Cre" & _
        "ated Time, Category, Ticket Owner)""))," & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd"" = Table.TransformColumnTypes(Bron,{{""Attribute:uri"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd"""
    Sheets.Add After:=Sheets("XML_LINKS")
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & strQueryName & ""), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [" & strQueryName & "]" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
       strQueryName2
        .Refresh BackgroundQuery:=False
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Next i

Application.ScreenUpdating = True

End Sub
 
Laatst bewerkt:
Bedankt voor je reactie jkpieterse.
Heb stukjes code gebruikt van de website die je aangeeft en een en ander werkt nu naar behoren.
De bedoeling is ook dat dit automatisch gerefresht wordt op bepaalde tijden (dit lukt met task scheduler).

Thanks voor alle hulp iedereen! Gaat een slotje op.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan