[Answered] Using VBA to pull data from a website fails on a lib "user32" statement

[jburgauer]

Rifat Hassan wrote a fantastic article which can be found about VBA about pulling data automatically from a website. His article can be read here: https://www.exceldemy.com/pull-data...491ac2937a6bea6506c49803c7e648#comment-329555

I realize that I am an old timer here, but I am still using Windows 7 Professional and Excel 2007 (release 12.0) which works perfectly in the examples Rifat gave in his article. Unfortunately the website that I need to pull data from is Google Maps and it requires a browser newer than Internet Explorer which is what Rifat used in his article.

I have installed Edge, which Google Maps claims to support.

I found code at: https://learn.microsoft.com/en-us/answers/questions/829365/vba-automation-with-edge-ie-mode under the subject heading "VBA automation with Edge IE mode" which suggests that this Edge in IE mode will solve the problem, but I do not know for certain if that is the case because I am unable to get past a declaration statement that reads:

Private Declare Ptrsafe Function EnumWindows Lib "user32" ( _
ByVal lpEnumFunc As LongPtr, _
ByVal lParam As Long) _
As Long

The VBA compiler has issue with this statement, turning it red, but if I remove the Ptrsafe, then that problem goes away. From what I read, that will only effect backward compatibility which is not an issue for me.

Then, when I run the code, I am confronted with the following message: Compile Error Message: "User-defined type not defined." I believe that this has to do with the 'user32' phrase -- presumably the compiler is looking to link in lib "user32" in which it expects to find the function EnumWindows -- and it cannot find the library. I have researched this heavily, and cannot with certainty determine if my version of VBA has "user32" library; whether this library exists but I don't have it linked in correctly; or some other issue is at play here. In the course I my reading it has been suggested in various forums (in answer to other questions) that the correct token is user32.lib, user32.dll, vba.user32, vba.user.lib, and vba.user32.dll and I have tried them all.

At this point I am open to any suggestions other than upgrading my entire environment which will cause greater headaches; that is to say, I am open to other methods to grab the data I need which I seems to only reliably exist on Google maps.

 

[Rubayed Razib]

Greetings jburgauer,

The issue that you are actually encountering right now is quite unique. Because the Edge IE can be solved very easily. But the point is, what type of data do you want to harness from Google Maps? The full code is not always accessible to normal users and the code structures deviate from time to time. That means whatever code we put in here, is not going to be 100 percent reliable all the time. For this, you need to use the official Google Map API. Using that API will cost you money, but it will guarantee you a reliable result(that's one of the ways Google map earns money).
However, the issue you are experiencing is that you can be able to use Edge in IE mode in the code. But the issue still persists, which is that you can't open google maps on IE mode either. IE mode in Edge will simulate the Internet Explorer experience. If the IE can't open the google maps, then IE mode of Edge won't able to do that either.
Now you have made a comment in the source article that you already installed Edge. So it's better to give you a solution based on Edge. This method will give you elements under specific tags. but make sure to cross-check them.It's pretty easy, just need to download the software and place the driver in a specific folder.
1. Download Selenium Basic from the GitHub link given here.
2. Then install the software in compact mode.

3. During the installation, note down the installation path.
4. after that download the Edge web driver from the Edge website matched with the Edge version installed in your computer(The version has to be matched the Edge browser version, you can find that in the settings of the Edge)
4. Then insert that driver in the installed directory of the Selenium Basic noted earlier.
5. Now our installation procedure is done.
6. Open the Excel vba editor, then go to the Tools > Reference

7. Then enable Selenium Type Library.

Now your set up complete.
We can use a new code in the worksheet to extract content under Head tags in Excel.

Sub PullDataFromWebsite()
    Dim WebsiteAddress As String
    Dim HTMLTag As String
    Dim Edge As Object
    Dim HTMLDoc As Object
    Dim Elements As Object
    Dim Data As Object
    WebsiteAddress = "http://exceldemy.com"
    HTMLTag = "head"
    Set Edge = CreateObject("Selenium.EdgeDriver")
    Edge.Get WebsiteAddress
    Edge.Wait 5000
    Dim HTMLSource As String
    HTMLSource = Edge.PageSource
    Set HTMLDoc = CreateObject("HTMLFile")
    HTMLDoc.Open
    HTMLDoc.Write HTMLSource
    HTMLDoc.Close
    Set Elements = HTMLDoc.getElementsByTagName(HTMLTag)
    If Elements.Length > 0 Then
        For Each Data In Elements
            MsgBox Data.innerHTML
        Next Data
    Else
        MsgBox "No elements found with tag '" & HTMLTag & "'."
    End If
    Edge.Quit
    Set Edge = Nothing
    Set HTMLDoc = Nothing
    Set Elements = Nothing
    Set Data = Nothing
End Sub

This code is heavily modified for the Excel 2007 edition. So this may not work in the Excel 2016 version.
After Running this code, we will see that the elements under the head tag are now present in the message box. Remember that the code will extract all the elements under a tag, not a specific parent element.

Please let me know whether the code successfully worked or not, and if you have more questions don't hesitate to inform me.

 

[jburgauer]

Once again, your explanation is concise, well written, and easy to follow. That said, your comment "The full code is not always accessible to normal users and the code structures deviate from time to time. That means whatever code we put in here, is not going to be 100 percent reliable all the time." is clearly cause for concern as it relates to my needs. I had already concluded that in my own way, but I thank you for confirming my suspicions.

Between the time of my post and your response, I had already reconsidered the methodology of using Edge and reverted back to your original IE code which works very well, with one minor exception which I will discuss later on in this missive. I redirected my query to bing which seems to give me fairly reliable and consistent answers.

To answer your basic question regarding the type of data that I seek, I'm merely looking for a phone number for a business name and address.

By virtue of an FOIA request, I acquired a list of information that was reported to have business phone numbers on it, and to my surprise, it did not. Of course, I am discussing this with the government agency, along with several other shortcomings in their data, but in the meantime I am in desperate need for the phone numbers that relate to the list.

As such, I took it upon myself to massage your code into a function which is then called repeatedly by a master program which assembles and passes that function based on your code with an appropriate bing query tag. The function based on your code grabs the data per your example, and the master program then invokes another program that searches the data for the phone numbers, grabs them, cleans them up and standardizes them, and then appends them to the FOIA database which is missing them. The master program also, for my purposes, then archives the bing query tag, the phone number I find (if any) and the results from the function based on your code, so that I end up with a storehouse of web page data that I can analyze for problems and code improvements.

Its kind of a neat package, and is slowly assembling the missing phone data that I seek.

I am happy to share the code with you and have done so, but I am not a brilliant programmer like yourself and am likely to embarrass myself to you and your readers.

Now, to the issue I spoke of earlier, after the function based on your code is called a number of times (maybe 50, maybe 100, it's sort of random) eventually your a line in the function based on your code [Browser.navigate Website_Address] fails with the following message:

Run-time error ' -2147467259 (80004005)':
Automation error
Unspecified error

Based on the way it behaves, I am GUESSING that the computer is running out of "resources or communication channels or something like that," as if the function based on your code is somehow not properly closing the connection completely, and after repeated use (that is, each time it seeks data from the web based on the repeated bing query tags, it seems like its using up a bit of that "??resource??" and eventually the browser fails to open.

Sometimes closing excel and the VBA session works; more often than not I have to reboot the machine.

The function based on your code is written as follows, and when it hangs, it always hangs on the that same line:
Browser.navigate Website_Address

Sub PullnParseDataFromWeb(Data2Find)


Website_Address = Data2Find
Worksheets("EXTRACTION WORKSHEET").Activate
Worksheets("EXTRACTION WORKSHEET").Range("a2:d3").ClearContents
Worksheets("EXTRACTION WORKSHEET").Range("a5:d2000").ClearContents
Worksheets("EXTRACTION WORKSHEET").Range("b3") = Website_Address



Dim Browser As New InternetExplorer
Dim Doc As New HTMLDocument
Dim Data As Object




Set HTML_Tags = Worksheets("EXTRACTION WORKSHEET").Range("B44")


For i = 1 To HTML_Tags.Columns.Count
HTML_Tag = HTML_Tags.Cells(1, i)
' Browser.Visible = True ' True - show browser; False = don't show browser
' On Error Resume Next
Browser.navigate Website_Address
Do
DoEvents
Loop Until Browser.readyState = READYSTATE_COMPLETE
Set Doc = Browser.document
Set Data = Doc.getElementsByTagName(HTML_Tag)
For j = 1 To Data.Length
HTML_Tags.Cells(j + 1, i) = Data(j - 1).innerHTML
Next j
Next i


End Sub


The master program which calls is is pretty basic stuff: it grabs the info, builds the bing query tag, hands the tag to the function based on your code, then hands the worksheet with the data from the function based on your code (which I store on extraction worksheet you gave along side your code), then hands that worksheet to a function to find the phone number, and then that worksheet to yet another function to move and archive the data.

Again, I am embarrassed by the simplicity of my code compared to yours, but here is the master program nonetheless.


Sub GetPhoneNumbersFromWeb_Click()

Dim Result As String
Dim OfficeName As String
Dim OfficeAddress1 As String
Dim OfficeAddress2 As String
Dim OfficeCity As String
Dim OfficeState As String
Dim Data2Find As String

Dim ReadLine As Long

Dim Display As Boolean



Display = Worksheets("EXTRACTION WORKSHEET").Range("h1")
' Mark the time the process starts
Worksheets("WORK HERE").Range("b1") = Now()

ReadLine = Worksheets("WORK HERE").Range("e1") + 1

' EXAMPLE: https://www.bing.com/search?q=Merrill+Lynch+400+Rampart+Las+VEgas+NV

' the program will start where it left off
For ReadLine = (Lastrow("j2") + 1) To Lastrow("b2")

Worksheets("WORK HERE").Activate

' get the data
OfficeName = Worksheets("WORK HERE").Cells(ReadLine, 2)
OfficeAddress1 = Worksheets("WORK HERE").Cells(ReadLine, 3)
OfficeAddress2 = Worksheets("WORK HERE").Cells(ReadLine, 4)
OfficeCity = Worksheets("WORK HERE").Cells(ReadLine, 5)
OfficeState = Worksheets("WORK HERE").Cells(ReadLine, 6)

'Prepare the data
OfficeName = Replace(Trim(OfficeName), " ", "+")
OfficeAddress1 = Replace(Trim(OfficeAddress1), " ", "+")
OfficeAddress2 = Replace(Trim(OfficeAddress2), " ", "+")
OfficeCity = Replace(Trim(OfficeCity), " ", "+")
OfficeState = Replace(Trim(OfficeState), " ", "+")

' assemble the data
Data2Find = "https://www.bing.com/search?q=" + OfficeName + "+" + OfficeAddress1 + "+" + OfficeCity + "+" + OfficeState
Worksheets("WORK HERE").Cells(ReadLine, 8) = Data2Find

If Display = True Then MsgBox "Sending PullnParse: " + Data2Find

PullnParseDataFromWeb (Data2Find) ' module 9

If Display = True Then MsgBox "PullnParseDataFromWeb COMPLETE"

Result = GrabPhoneNumber(ReadLine) ' module 8

If Display = True Then MsgBox "GrabPhoneNumber COMPLETE"

' this writes a duplicate copy of the phone numbers
Worksheets("WORK HERE").Cells(ReadLine, 12) = Results

MovenStoreData ' module 10

If Display = True Then MsgBox "MovenStore COMPLETE"


Next ReadLine

Worksheets("WORK HERE").Range("e1") = ReadLine

' Mark the time the process ends
Worksheets("WORK HERE").Range("c1") = Now()

End Sub


I certainly look forward to any suggestions that you may have.

Thank you again for your reply, and please accept my apologies for my general ignorance in this matter --- I never expected I would have to resort to this method to obtain the phone numbers because they were supposed to have been included.

Thank you also for your most excellent website/forum.

 

[Rubayed Razib]

Thanks a lot, jburgauer

You absolutely need not be embarrassed with your code, this code is OK to work and you are putting a lot of effort into this. Now the thing is, I need some information and confusion cleared before I can assist you again.
1. Does the code I provided earlier worked? Or you modified the code with IE again?
You may be right about the error diagnosis, is that the code is not been able to close the site properly. For that I provided a separated code below. Let me know if that code works.

Sub PullnParseDataFromWeb(Data2Find)
    Dim Website_Address As String
    Dim Browser As Object
    Dim Doc As Object 
    Dim Data As Object
    Dim HTML_Tags As Range
    Dim HTML_Tag As String
    Dim i As Long
    Dim j As Long
  
    Website_Address = Data2Find
    Worksheets("EXTRACTION WORKSHEET").Activate
    Worksheets("EXTRACTION WORKSHEET").Range("A2:D3").ClearContents
    Worksheets("EXTRACTION WORKSHEET").Range("A5:D2000").ClearContents
    Worksheets("EXTRACTION WORKSHEET").Range("B3") = Website_Address
  
    Set Browser = CreateObject("InternetExplorer.Application") 
  
    Set HTML_Tags = Worksheets("EXTRACTION WORKSHEET").Range("B4:D4")
  
    For i = 1 To HTML_Tags.Columns.Count
        HTML_Tag = HTML_Tags.Cells(1, i)
        ' Browser.Visible = True ' True - show browser; False = don't show browser
        ' On Error Resume Next
        Browser.navigate Website_Address
        Do
            DoEvents
        Loop Until Browser.readyState = 4 ' READYSTATE_COMPLETE constant replaced with its value 4
        Set Doc = Browser.document
        Set Data = Doc.getElementsByTagName(HTML_Tag)
        For j = 1 To Data.Length
            HTML_Tags.Cells(j + 1, i) = Data(j - 1).innerHTML
        Next j
        Set Doc = Nothing ' Release the document object
        Set Data = Nothing ' Release the data object
        Browser.Quit ' Close the browser
        Set Browser = Nothing ' Release the browser object
        Set Browser = CreateObject("InternetExplorer.Application") ' Create a new browser object
    Next i
  
    Set HTML_Tags = Nothing ' Release the range object
    Set Browser = Nothing ' Release the browser object
End Sub

Let me know this modified version of the code works. A sample file with desired sample output will be most benefit for me to assist.

 

[jburgauer]

OMG thank you.

Please give me a bit of time to study and implement and I will reply.

 

[jburgauer]

I have installed your code in place of the prior code, and after about 45 web results (records) I got the same interrupt, The debugger shows that the code failed on this line:

Set Browser = CreateObject("InternetExplorer.Application") ' Create a new browser object

 

[Rubayed Razib]

I have installed your code in place of the prior code, and after about 45 web results (records) I got the same interrupt, The debugger shows that the code failed on this line:

Set Browser = CreateObject("InternetExplorer.Application") ' Create a new browser object

Greetings Jbuger,
I mentioned that I have some confusion about this problem. And I need them to be cleared.
My issue here is I do not have any raw data or sheets right now in my hand. that actually made me difficult for me to troubleshoot the issue. That's why I am giving solutions, but those solutions can't be tested in your context. Those are working for my case. So that is why it is better to have some sample data in the sheet, with desired sample output.

the issue is happening after 45 iterations mainly because the resources are not being released for the next steps effectively. I am again providing another modified code.

Sub PullnParseDataFromWeb(Data2Find)
    Dim Website_Address As String
    Dim Browser As Object
    Dim Doc As Object
    Dim HTML_Tags As Range
    Dim HTML_Tag As String
    Dim Data As Object
    Dim i As Long
    Dim j As Long
    
    Website_Address = Data2Find
    Worksheets("EXTRACTION WORKSHEET").Range("a2:d3").ClearContents
    Worksheets("EXTRACTION WORKSHEET").Range("a5:d2000").ClearContents
    Worksheets("EXTRACTION WORKSHEET").Range("b3") = Website_Address
    
    On Error Resume Next
    Set Browser = GetObject(, "InternetExplorer.Application")
    If Err.Number <> 0 Then
        Set Browser = CreateObject("InternetExplorer.Application")
        Browser.Visible = True ' True - show browser; False = don't show browser
    End If
    
    On Error GoTo 0
    
    Set HTML_Tags = Worksheets("EXTRACTION WORKSHEET").Range("B4:D4")
    
    For i = 1 To HTML_Tags.Columns.Count
        HTML_Tag = HTML_Tags.Cells(1, i)
        
        Browser.navigate Website_Address
        Do While Browser.Busy Or Browser.readyState <> 4
            DoEvents
        Loop
        
        Set Doc = Browser.document
        Set Data = Doc.getElementsByTagName(HTML_Tag)
        
        For j = 1 To Data.Length
            HTML_Tags.Cells(j + 1, i) = Data(j - 1).innerHTML
        Next j
    Next i
    Browser.Quit
    Set Browser = Nothing
End Sub

please give me feedback on this. And please give us a sample file that actually contains more than 45 entries. So that I can code according to your need and debug more efficiently. Without your sample info, it is very difficult to provide a solution for your issues.

 

[jburgauer]

I would be happy to send you a file that you can run with actual live data in it. Please see the attached.

On the page WORK HERE is a click button. This will start the process that will grab data from the work here page, initiate the web activity, drop the data into "your" extraction page, grab the data from your extraction page and extract the components I am looking for, store that information on the work here page, archive the entire data set into the raw data storage page, grab the next item on the work here page, and start the process over.

Just to give you a point of reference, in a prior page starting at number 1, the process hung at 38, then at 37, then at 64 with a (non-reboot) restart somewhere around 32, 75 with a (non-reboot) restart somewhere around 37. I think the variance has to do with the success the program is achieving at find the requested website page and its size.

By way of reminder, I am using Windows 7 professional and Excel 2007.

And, again, I am embarrassed by my coding methodology. THANK YOU FOR YOUR HELP..

 

[jburgauer]

Sorry --- I had to redcue the size and reattach

 

[jburgauer]

I have some more information that I have learned that might be helpful to resolving this problem.

I mentioned before that I believe that this is caused by a resource that is being used and not properly released.

By virtue of some experimentation I did, as I improved the code, I noted that after a while the program completely hangs and closing excel and then reopening it does NOT seem resolve the issue; thereby forcing me to reboot the machine.

I use a diagnostic program called CCleaner (Crap cleaner) and have noted that if the process hangs, I can close excel and then open CCcleaner. When CCleaner does its PC health check, it identifies programs that are running in the background that need to be closed before it can perform its PC check task, and offers the option to FORCE them to close.

Lo and behold, one of those programs is INTERNET EXPLORER. And, when I force it to close (using the CCleaner software) I can then reopen Excel and it works.

I presume from that knowledge that there indeed is some "channel" that is not properly closing Internet Explorer. which is causing the program to fails. Ergo, maybe this knowledge will provide a further clue as to what needs to be added into the VBA code.

 

[jburgauer]

Further expirements suggest that I do not even need to close excel -- the CCleaner process to force Internet Explorer to shut down seems to be sufficient for me to restart the excel process without having to exit and re-enter excel.

Another change that I instituted in my code, because I occasionally lost progress by virtue of lost data due to the rebooting process, and which seems to also help extend the "life of the process" beyond the 43 or so record mark, is that after every fourth record I now force Excel to save the workbook (altering its name by incrementing a version number).

VERY WEIRD -- but potentially some clues to guys like you who are way smarter people than I am.

 

[jburgauer]

As I continue to study the problem (as the novice that I am) I have become more convinced that the issue is that IE does not play nicely and does not always release its "threads" (is that the right word?) as it should.

Apparently other folks have found the same issues with internet explorer, word, power point, excel, etc. and at the following link there seems to be a good discussion of the problem and some code that seems to have worked for many people....presuming someone is smart enough to know how and where to integrate it into their own code (or your code, as in my case). The link is as follows; and the code is below.

https://www.devhut.net/vba-forcing-applications-to-close/


The code solution offered for my problem is as follows:


Public Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal lHWnd As Long, _
ByRef lProcessId As Long) As Long

'---------------------------------------------------------------------------------------
' Procedure : KillHwndProcess
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Terminate a process based on its Windows Handle (Hwnd)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' lHWnd : Windows Handle number (Hwnd)
'
' Usage:
' ~~~~~~
' Call KillHwndProcess(Application.hWnd)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-09-09 Initial Website Release
'---------------------------------------------------------------------------------------


Public Function KillHwndProcess(lHWnd As Long)
' https://docs.microsoft.com/en-us/windows/desktop/cimwin32prov/win32-process
On Error GoTo Error_Handler
Dim oWMI As Object
Dim oProcesses As Object
Dim oProcess As Object
Dim lProcessId As Long
Dim sSQL As String
Const sComputer = "."

'Retrieve the ProcessId associated with the specified Hwnd
Call GetWindowThreadProcessId(lHWnd, lProcessId)

'Iterate through the matching ProcessId processes and terminate
' each one.
Set oWMI = GetObject("winmgmts:\\" & sComputer & "\root\cimv2")
sSQL = "SELECT * FROM Win32_Process WHERE ProcessId=" & lProcessId
Set oProcesses = oWMI.ExecQuery(sSQL)
For Each oProcess In oProcesses
oProcess.Terminate
Next

Error_Handler_Exit:
On Error Resume Next
If Not oProcess Is Nothing Then Set oProcess = Nothing
If Not oProcesses Is Nothing Then Set oProcesses = Nothing
If Not oWMI Is Nothing Then Set oWMI = Nothing
Exit Function

Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: KillHwndProcess" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function

Function RunIE()

Dim oIE As Object 'SHDocVw.InternetExplorer

On Error GoTo Error_Handler
Set oIE = CreateObject("InternetExplorer.Application")

With oIE
.Navigate "https://google.com"
.Visible = True 'True/False
Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
End With

' oIE.Quit ' Normal, polite way to close Internet Explorer, commented out for demonstrative purposes only

DoEvents
'Maybe throw in a wait period to give the PC a chance to close the App before forcing it
Call KillHwndProcess(oIE.Hwnd)

Error_Handler_Exit:
On Error Resume Next
Set oIE = Nothing
Exit Function

Error_Handler:
MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: RunIE" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit


End Function


So far I have inserted this code into a new module, and I guess that I should be adding the error handling routine into the code that your offered.

I know that this is not an elegant way to handle the problem, but the problem itself isn't elegant; and frankly, if it forces a close and the I can automatically restart my process, that is way better than having the system hang until I push a few buttons and restart it manually.

As such, I am hoping that, if nothing else, I can integrate this into what I already have, and when the system hangs, have this code execute, and then restart my process. Obviously I will have to get rid of the message box stuff, which is fine, because I will simply redirect that message activity into my log file so I will know when it happened along with all of the other activity I am now logging in the background.

Maybe you would be kind enough to help me integrate the call to this solution into your code??

 

[jburgauer]

I have taken the code above and added a new module to my workbook plus a click button that simply executes this code. I have had the need to execute it a few times, and lo and behold, it must do much the same thing as the force close code from CCLEANER does because after running this new code, I can then restart the web extraction and it seems to run. I have only executed this code tow or three times, but each time it seemed to succeed in closing the "unclosed threads/channels/whatever". After a few more times I will be convinced that it is a solution, and then I will try to figure out a way to integrate this into an on error handling routine. Of course, any suggestions on proper integration will be highly appreciated.

 

[shamimarita]

I have taken the code above and added a new module to my workbook plus a click button that simply executes this code. I have had the need to execute it a few times, and lo and behold, it must do much the same thing as the force close code from CCLEANER does because after running this new code, I can then restart the web extraction and it seems to run. I have only executed this code tow or three times, but each time it seemed to succeed in closing the "unclosed threads/channels/whatever". After a few more times I will be convinced that it is a solution, and then I will try to figure out a way to integrate this into an on error handling routine. Of course, any suggestions on proper integration will be highly appreciated.

Hello Jburgauer,

Your problem seems a bit difficult and time-consuming and to solve each and every requirement a specialized team is needed. If it's part of any project or you want further help you may contact us at [email protected]