Problem using VBA to download from Livelink 2

[mcliver]

Wondering if anyone can help with the problem I'm encountering. I've put togther a piece of code to fulfil a requirement to download files from Livelink using VBA in Excel. As a number of files are saved within a single node, the code must first check the files contained within that node, before determining the correct one and then downloading it. The process downloads the most appropriate file from each node and then processes the files on the hard drive.

The second part of this process works fine, in that I can stream the file to the local hard drive using an XMLHTTP request and then unzip it using the code. To accomplish the first part, I've been running a web query which outputs the specific table to an Excel sheet, which I then parse using an algorithm (the files are saved using a specific naming convention which makes identifying the most recent a simple process).

The problem I've encountered is that the web query returns no data the first time it's run after the application is opened, and has to be run manually once - after this, any subsequent web queries run with no issues. I've attempted to use SendKeys to run the query 'manually' on start up, but when this opens the web query page it sends all remaining keystrokes to the application instead of the web query window.

I've even tried to use WebDAV to access the directory structure, either by opening the WebDAV link directly in Excel or using ADO / XMLXTTP to stream it, but I always get the same message:

'This resource is a WebDAV collection, and must be accessed using a WebDAV client'

I'm now running out of ideas. I'm using Excel 2003 and am not sure about the version of Livelink. Would be happy to share any code with anyone willing to help me crack this one.

Mark L

 

[ggriffit]

can you provide the code that you have and some info on the setup within Livelink so we can look at replicating it e.g. are you looking for a document in a folder or something else ?

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[appnair]

I see no logic in why a web query runs blank and then results.Is the web query executed in excel?The blank screens are usually when some authentication headers get lost in the mictrosoft app/livelink http transfer protocol.Symptomatic of a double get.I have noticed problems with excel like this.If you have an unordinately lengthy file in livelink and if you take its URL ,IE gives you a really complicated URL with lots of escapes etc and what not see symptomatic typical copy URL

[URL unfurl="true"]http://mylivelink.com/mylivelink/llisapi.dll/13932888/cats_in_prodcats_in_prodcats_in_prodcats_in_prodcats_in_prodcats_in_prodcats_in_prodcats_in_prodcats_in_prodcats_in_prodcats_in_prodcats_in_prod.xls?func=Edit.Edit&nodeid=13932888&ReadOnly=True&viewType=1&nexturl=%2Fmylivelink%2Fllisapi%2Edll%3Ffunc%3Dll%26objtype%3D142%26objaction%3Dbrowse[/URL]

Depending on the excel version and its umpteen compatibility problems some may get blank on opening this link.But if you look at livelink the only thing that it cares about is the object id and the action to be performed
so when we make links we tweak it something like

[URL unfurl="true"]http://mylivelink.com/mylivelink/llisapi.dll?func=ll&objId=13932888&objAction=open[/URL]

This in my experience has been the most effective way of countering diffrent app versions and is almost trouble free when sending links.In fact it is even superior to the nickname links where I have seen problems with some excel stuff(Nicknames are awesome in some respects).Depending on your mime type tweak some companies use the llview process or some opne them up in the app mime type.The URL's are then different.

So try changing the livereport or whatever datasource
you are making sure that it has the objid and the objaction correctly and nothing else and see if this alleviates anything.

If this does not give you what you want,since I do not really know VBA that much,but since excel can handle XML,why not export the livelink object hirarchy as an xml export and do all your parsing on that.






Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

 

[mcliver]

Here's the code that runs the web query to get the directory listing (I've removed the name of our actual Livelink store as it's company specific):

Sub Get_Directory_Names_Using_Web_Query(dblLivelinkFolder As Double)

' Runs a web query to get the list of files from a Livelink folder, determined by the variable passed to the subroutine.

Dim qtFileList As QueryTable

With Worksheets("Web Query")
    ' Make sure any previous web query or its results is removed from the sheet, as this can cause strange effects.
    .Activate
    .Cells.Delete
    .Range("A1").Select
    If .QueryTables.Count > 0 Then
        For Each qtFileList In .QueryTables
            qtFileList.Delete
        Next
    End If
    ' Add the Livelink query to the hidden Web Query sheet.
    Set qtFileList = .QueryTables.Add(Connection:="URL;http://[local livelink store]/livelink/livelink.exe?func=ll&objId=" & dblLivelinkFolder & "&objAction=browse&sort=name", _
        Destination:=.Range("A1"))
End With

' Update the parameter list for the web query.
' The 19 figure on the .WebTables entry  refers to the number of tables on the web page - the list of files is the 19th table.
' This was established by recording a data import query and discovering quite how many tables exits on a Livelink web page.

With qtFileList
        .Name = "livelink.exe?func=ll&objId=" & dblLivelinkFolder & "&objAction=browse&sort=name"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "19"
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
End With

qtFileList.Refresh BackgroundQuery:=False

End Sub

Running the code generates an error message, 'The web query returned no data.' However, manually running any query to the same web page then allows any subsequent queries to be run. My initial suspicion was that it required one or more of the applets that seem to be run by the .exe to have run once before you can run web queries, but this is based on nothing more than observation.

Apologies, but I'm enough of a Livelink novice at this point that I'm not sure how I can establish which version we're using - any pointers to how and I'd be glad to return the results.

I'm interested in the idea of trying to get this via XML, but I'm afraid I don't know enough about the interation of XML and Livelink at this point to pull it off (I'm more of a VBA expert by trade, this is admittedly new ground for me). Would be happy at this point to go down any route that works, simply need to be able to get a file listing from Livelink that I can parse and I should be able to do the rest.