Use VBA to open Worksite files from Excel

I am banging my head off a brick wall here.

I would like to open a saved document from a specific database using VBA inside an Excel workbook.

Can anyone help / point me in the direction of sample code. As ever I can't find any coherent guides or documentation on how to do this...

Find more posts tagged with

Comments

jny

Unfortunately, there isn't a good way to run VBA code in Excel to set up a sink to the IManageExtensibility Object (of the WorkSite Office Intergration Object Model, iManO2K). This is the object you would use to query for the WorkSite document in a given event.

The Auto_Exec routine in an Excel macro executes before the OnConnection event of the iManO2K (which populates the IManExtensibility object into the .Object property of the COM Add-In), so it’s not available then. You could, however, use a COM Add-In instead. The .Object property should be populated by the time OnStartupComplete executes.

Migrateduser

Thanks, I've now managed get the following process working:

User opens a spreadsheet set as read only .

Spreadsheet saves a copy of itself locally and uses the iManage import command to pass the newly created file into a pre-set database.

However, where I have ground to a halt now is the syntax to reopen the newly created file / check this out of the database.

The code (which is quite rough at the moment) looks like this:

Sub ForceSaveAs()
'sub routine called on workbook open event

Application.Workbooks(1).SaveCopyAs "C:\Doc Temp\Temp.xls"

Call SilentCode.LoginiManage
'works fine

Call SilentCode.SetProfileInfo
'works fine

Call SilentCode.CreateNewDoc
'works up to a point (see below)

Call SilentCode.LogOutofiManage
'works fine

End Sub

Sub CreateNewDoc()
'strTempFile As Variant
'create a new iManage document

Dim ErrResults As Variant
Dim CurrACL As NRTGroupACL
Dim strAuthor
Dim strOperator
Dim strDept
Dim strDocType
Dim strClient
Dim strMatter
Dim strAppType
Dim strDesc
Dim CurrDoc As NRTDocument

On Error GoTo Creation_Error

Set NewDocument = pNRTDatabase.CreateDocument
'create a new imanage document on the server
strAuthor = "STU"
strOperator = "STU"
strDept = "CENTRAL"
strDocType = "MISC"
strClient = "NC"
strMatter = "NC"
strAppType = "EXCEL2000"
strDesc = "This is a test workbook (please ignore)"

NewDocument.SetAttributeValueByID nrAuthor, strAuthor
NewDocument.SetAttributeValueByID nrOperator, strOperator
NewDocument.SetAttributeValueByID nrClass, strDept
NewDocument.SetAttributeValueByID nrSubClass, strDocType
NewDocument.SetAttributeValueByID nrCustom1, strClient
NewDocument.SetAttributeValueByID nrCustom2, strMatter
NewDocument.SetAttributeValueByID nrType, strAppType
NewDocument.SetAttributeValueByID nrDescription, strDesc

'set its profile info

'**set security info...**

NewDocument.Security.DefaultSecurity = nrView
'default security is view

Set CheckedinDocument = NewDocument.CheckInEx("C:\Doc Temp\Temp.xls", nrReplaceOriginal, nrDontKeepCheckedOut, nrHistoryCheckin, "LAWSOFT", "Generated from client cheques", "", ErrResults)
'check into new document instance the locally stored document - this will overwrite the new document with the correct information

Dim oExtensibility As iManO2K.iManageExtensibility
' An object used for extending the functionality of iManage Office 2000
' integration.

' Get the iManage O2K extensibility object.
Set oExtensibility = _
Application.COMAddIns("iManO2K.AddinForExcel2000").Object

Dim oOpenCmd As Object ' IMANEXT.OpenCmd
Dim oContext As Object ' IMANEXT.ContextItems
Dim status As Integer
'I think these commands are used to create the tools to reopen a document
'However,I can't find any exact / explicit explainations as to the function of these commands.

Set oOpenCmd = CreateObject("IMANEXT.OpenCmd")
Set oContext = CreateObject("IMANEXT.ContextItems")

oContext.Add "ParentWindow", windowHandle
oContext.Add "iManExt.OpenCmd.NoCmdUI", True

oContext.Add "SelectedNRTDocuments", CheckedinDocument

With oOpenCmd
'
.Initialize (oContext)
'*******fails here with an wrong number of arguments or invalid property
'*******assignment error

.Update
.Execute
'
End With

exit_CreateNewDoc:
Exit Sub

Creation_Error:
' Call Error_Catch(Err.Number, Err.Description, "CreateNewDoc")
Resume exit_CreateNewDoc

End Sub

Any help would be greatly appreciated!

jny

The SelectedNRTDocuments ContextItem for the OpenCmd Object accepts a safearray of elements as NRTDocument Object data type, like so:

Dim saDoc(0) As NRTDocument
set saDoc(0) = CheckedinDocument

Also, you would need get the checkedout path from the returned ContextItem, "IManExt.OpenCmd.ING.FileLocation". Then use the Excel Object Model to open the file. The OpenCmd will not be able to open the file inside an instance of Office app. It will only be able to do so when there isn't an instance already opened, as it will try to open an instance but already in one thus causes a deadlock. Here's an example of how you can accomplish this in Word (sorry, I don't have any code for Excel but it's the same logic):

' Use bitwise operation: the example from SDK is incorrect.
If oOpenCmd.Status = (oOpenCmd.Status & nrActiveCommand) Then

oOpenCmd.Execute

Dim filLoc As String
filLoc = oContext("IManExt.OpenCmd.ING.FileLocation")
' Get the checkedout path
Set oDoc = Documents.Open(filLoc)
' Use WOM to open the WorkSite document from the checkedout path.
oDoc.ActiveWindow.Caption = "#" & oWorkSiteDoc.GetAttributeByID(imProfileDocNum) & _
"v" & oWorkSiteDoc.GetAttributeByID(imProfileVersion) & _
"<" & oWorkSiteDoc.Database.Name & ">" & _
" - " & Left(oWorkSiteDoc.GetAttributeByID(imProfileDescription), 50)

'Remove context items
oContext.Remove "SelectedNRTDocuments"
oContext.Remove "iManExt.OpenCmd.Integration"
End If

I think the best way to import a new document to WorkSite would be to use the IMANEXTLib.ImportCmd instead of going through the low-level API's (imanage.dll).