automating X

Part 2 - Accessing the Libronix Resource Cache

Last week we looked at using VBA to control another application - specifically LDLS. This week we are going to look at accessing the Libronix Library Object to generate a list of the LDLS resources in your Library and write this to an Excel worksheet. This can be best handled by using a three step process:

a) Initialize Report - set up column headings

b) Write Report - write information about resources from Libronix Library to appropriate column/row location on worksheet

c) Finalize Report - Apply formatting to columns including adjusting the column widths to fit report data.

1. Open the excel file that you saved at the end of lesson 1.

2. Before opening the Visual Basic Editor(VBE) rename Sheet1 and call it Start and rename Sheet2 calling it Resources, and delete Sheet3

(To rename a sheet, From the Excel menu select FORMAT->SHEET->RENAME or right-click on the sheet tab and select Rename for the short-cut menu.

To delete a sheet, right-click on the sheet tab and choose Delete)

3. Press ALT-F11 to open the VBE.

4. From the Project Explorer (Top Left-hand side of VBE editor) right click on the module Main and choose view code form the short cut menu. (Screen Shot)

5. At the top of the code window, titled LDLSdatabase.xls - Main (code) you will see two drop down lists. The first on the left hand side should be currently displaying: (General), while the one on the right will be displaying: (Declarations). Left Click on the right hand side drop down and choose generateDatabase to bring our main control code into view. (Screen Shot)

6. Add the six comment lines as shown below to your code window.

Public Sub generateDatabase()

'start a conversation between Excel and LDLS
openLDLS

'setup report headings
'initializeReport
'extract and write resource information to excel
'writeReport
'applying final formatting to report
'finalizeReport

End Sub

The lines intializeReport, writeReport, and finalizeReport are actually the names of the modules we are going to create. We have commented them out at the moment because if we didn't and ran the code at this point we would get a compile error saying Sub or Function not defined.

7. From the Project Explorer (Top, Left Hand Side of VBE window) double click on the module Functions to bring it into focus.


Initialize Report - set up column headings
8. From the VBE main menu choose INSERT->PROCEDURE. Give it the name initializeReport, leave the type as Sub, and scope as Public.

9.We are going to use our initializeReport Module to setup our report headings. We do this by first telling excel which sheet we want to work with and then tell it what cells we want to work with and what we want to write to those cells.

10. To tell Excel which Sheet we want to work with we use the Sheets Collection which refers to all the worksheets and charts in an excel workbook. To refer to a specific sheet we use the statement: Sheets(index) where index can either be the sheets position in the workbook (i.e. first sheet in workbook has index of 1, etc.) or the sheets actual name. Since we know the sheets name we will refer to the sheets name. Hence the first line of code in our initializeReport procedure will be:

Public Sub initializeReport()

Sheets("Resources").Select

End Sub

11. Know that Excel knows what worksheet we want to work with we know need to tell it where to write each of the headings in our report. To do this we are going to use the Range Object: Range(arg) The arg (argument) can refer to a single cell, a whole column or row, or a group of cells. We are going to use it to refer to as single cell. If we want to refer to cell A1, we would write this as: Range("A1"). To tell excel what we want to write to a cell we use the value property of the cell. Our first heading will be: Resource Title, so we would write this as: Range("A1").Value = "Resource Title"

12. In this lesson, for each resource in your library we are going to extract the resource title and the resource id, and determine whether or not the resource is locked our unlocked, and the version (date) of the file. Hence our initializeReport procedure will look like:

Public Sub initializeReport()

Sheets("Resources").Select
Range("A1").Value = "Resource Title"
Range("B1").Value = "Resource ID"
Range("C1").Value = "License"
Range("D1").Value = "Version"

End Sub

In the next lesson we will look at how we can extra further information about a resource from LDLS so we will add further lines of code to this procedure at that point to include the appropriate headings.


Finalize Report - Apply formatting to columns including adjusting the column widths to fit report data.


13. From the VBE main menu choose INSERT->PROCEDURE. Give it the name finalizeReport, leave the type as Sub, and scope as Public.

14. In this module we will apply the final formatting to our report. Basically there are tow things we want to do here apply bold to our column headings and adjust the width the columns to best fit the data in the report.

15. First we want to apply Bold to both cells A1 & B1, so we will use the Range Object to refer to both of these cells at once and with one line of code apply bold formatting to both cells. We do this by setting the bold property of the cells font to True: Range("A1:B1").Font.Bold = True

16. We then want to adjust the width of the columns using the autofit method so they will adjust to the width of the largest entry in each column. This is done simply using the following: Columns("A:B").AutoFit.

17. Our completed finalizeReport procedure will look like:

Public Sub finializeReport()

Range("A1:D1").Font.Bold = True
Columns("A:D").AutoFit

End Sub

Both intializeReport and finalizeReport procedures are self-explanatory from their names and the code within them in what we are doing so we don't really need to add any extra comments to these particular procedures.

18. From the Project Explorer, double click on the module Main to bring it into focus. If the procedure generateDatabase is not visible, then bring it into focus as describe in step 5 above.

19. Since we have now written our initializeReport and finalizeReport procedures we can now remove the comment markers from the respective lines in our generateDatabase procedure so it now looks like:

Public Sub generateDatabase()

'start a conversation between Excel and LDLS
openLDLS

'setup report headings
initializeReport
'extract and write resource information to excel
'writeReport
'applying final formating to report
finalizeReport

End Sub

20. From the Debug menu choose: Compile VBAProject (Screen Shot)

21. At this point you may choose to run the code or even step through the code if you want, as we did in the first lesson, just make sure the procedure, generateDatablase in the main module has focus before running or stepping through the code.


Write Report - write information about resources from Libronix Library to appropriate column/row location on worksheet.

22. From the Project Explorer, double click on the module Functions to bring it into focus. From the VBE main menu choose INSERT->PROCEDURE. Give it the name writeReport, leave the type as Sub, and scope as Public.

23. In order to access the Resource Cache, first we need to define an object which will act as a pointer or reference to the resource cache in the same manner that we defined an object, objLDLS that we could use to refer to the LDLS application. We will call our object objResourceCache. Our declaration will be: Dim objResourceCache As LbxLibrarianResources. (For more information on LbxLibrarianResources: ) We will also want a variable which we can use to refer to the current resource we are processing. We can do this with the statement: Dim resource As LbxLibrarianResource. (For more information on LbxLibrarianResource: )

24. Next we want to tell objResourceCache where to find the resource cache, so we use a Set Statement:

Public Sub writeReport()

Dim objResourceCache As LbxLibrarianResources
Dim resource As LbxLibrarianResource

Set objResourceCache = objLDLS.Librarian.Information.Resources


End Sub

25. Lets test things out. First we will need to switch back to the module Main. Bring the procedure generateDatabase into focus. We first need to remove the comment marker from the line 'writeReport, so that our new procedure writeReport will be processed. The generateDatabase procedure should now look like:

Public Sub generateDatabase()

'start a conversation between Excel and LDLS
openLDLS

'setup report headings
initializeReport
'extract and write resource information to excel
writeReport
'applying final formating to report
finalizeReport

End Sub

26. From the Run menu of VBE choose Run.

27. What's that ? You got an error saying something about an object required. We have only created two objects, objLDLS and objResourceCache so let's find out with which one the problem lies. Hit Debug to find out where the error lies. The problem seems to be with the statement.

Set objResourceCache = objLDLS.Librarian.Information.Resources

So the problem occurs when we try to tell objResourceCache what it is to reference, it appears the problem is with objLDLS. At this point the code does not seem to be aware of this object. Why ?

28. Double Left Click on objLDLS in the line and hover your mouse over the term: Set objResourceCache = objLDLS.Librarian.Information.Resources (Screen Shot)

Notice the popup that appears showing the object is empty ie nothing is assigned to it. But didn't we just step through the module openLDLS and there was no problems in that procedure.

29. The problem lies with the way we declared objLDLS. We declared it in the general declarations section of the main module using the DIM statement. The scope of the Dim Statement is private. Therefore it only applies within the context of that module. When the procedure openLDLS runs in the functions module, it is not aware of this variable because its scope is limited to the main module and so it creates a separate variable which also happens to have the same name ie objLDLS and because this second variable has not scope defined it is also private, but in this instance it is private to the procedure openLDLS so that the procedure writeReport is not aware of it. Hence when writeReport runs it does not know about either of version of objLDLS and so you get the error message from it telling you, hey I can't find objLDLS.

30. To overcome this we need to change the scope of objLDLS in general delcarations section of the main module to Public so it will be visible to all modules rather than just the main module.

Change the declaration to: Public objLDLS As LbxApplication

31. From the VBE menu, DEBUG->RESET to clear the error and all objects and variables so we can start again with a new slate.

32. From the VBE menu, RUN-> RUN to test our code again. This time we should get no error message so we are back on track or are we ?

33. We have declared objLDLS as a Public Variable in the main module. This works but is not good program design. We should always define variables/objects on a needs to know basis. In this case is not necessary for the main module to know about objLDLS. We do not refer to it at all in this module. Delete Public objLDLS As LbxApplication from the general declarations section of the main module. In the general declarations section of the functions module add the following statement: Dim objLDLS As LbxApplication. objLDLS will now only have scope in the module.

34. We are now ready to continue with our writeReport procedure. First of all we will need to define a counter. This counter will have a two fold purpose. First to help us keep track of how resources in our collection have been processed at an given point in time, and secondly we will also use the counter to determine what row in our spreadsheet we are currently writing the resource information. Add a declaration to the define to the counter to the writeReport procedure as shown below:

Public Sub writeReport()

Dim objResourceCache As LbxLibrarianResources
Dim resource As LbxLibrarianResource
Dim counter As Integer

Set objResourceCache = objLDLS.Librarian.Information.Resources


End Sub

35. What we want to do now is cycle through every resource in objResourceCache. We do this by using a For Each loop:

For Each resource In objResourceCache

Next resource

This will execute every line of code we write between the For and the Next statement for every resource in our Resource Cache.

36. The first thing we need to do in our loop is increment the counter using the statement: counter = counter + 1

37. To write the actual data to our report we need to know which row is the next available empty row. If we are processing our first resource our counter will equal 1 and the row we want to write to will be 2, if we are processing our second resource, the counter will be 2, and the row we want to write to will be 3. Hence we can determine the row by the simple statement counter + 1. We will always put the title in column A, the id in column b and the license state in column c. Normally to refer to cell A2, the cell we want to put our first title, we would write Range("A2"), but since the row number will change everytime we loop through the code it will need to be determined dynamincally. Hence we will have to construct this cell reference. Notice that A2 is in quotes, making it a string, but counter + 1 would be an integer. So we will need to convert this row number to a string as a part of this construct. To do so we can simply use the Format function with no arguments. Putting this together our statement to determine what cell we will be writing to will look like: Range("A" & Format(counter + 1)), we just need to change the A to B an C respective when writing to these columns.

38. Each resource has a property resourceID which we can directly access to tell us the resource ID using the statement resource.ResourceID.

But what about the resource title ? How do we get that? And how do we determine if the resource is locked ?

39. The librarian object has two methods which we can use to get either the resource title (GetResourceTitle) or the sortable resource title (GetSortableResourceTitle).

We will use the second method since we want to be able to sort our list. Therefore in order to get the resource title we will use the statement: objLDLS.Librarian.GetSortableResourceTitle(resource.ResourceID).SortTitle

Note the method GetSortableResourceTitle is a method of the librarian object and not the resource cache. Also note that it takes a single argument the resource id which can refer to for the resource currently being processed as described in step 38.

40. At this point our code will look like:

Public Sub writeReport()

Dim objResourceCache As LbxLibrarianResources
Dim resource As LbxLibrarianResource
Dim counter As Integer

Set objResourceCache = objLDLS.Librarian.Information.Resources

For Each resource In objResourceCache

counter = counter + 1

Range("A" & Format(counter + 1)).Value = objLDLS.Librarian.GetSortableResourceTitle(resource.ResourceID).SortTitle
Range("B" & Format(counter + 1)).Value = resource.ResourceID

Next resource


End Sub



40. To determine the license state we examine: resource.LicenseState.State. A resource can have one of three states:

libLicenseStateUnlocked - There is a permanent license; libLicenseStateExpires - There is a temporary license; or libLicenseStateLocked - There are no licenses.

We will need to convert this information into a more user friendly format for our Report. In order to do this we will write a separate function to handle the processing of this to keep our module tidy. (We could do it all in the writeReport procedure, in fact we could write the whole code in one procedure, but we break it down to keep it tidy and easy to maintain and update.

From the VBE menu, INSERT->PROCEDURE. Call the procedure convertLicenseState, set the type to Function, and the scope to private. We want the function, convertLicenseState, to return a string, the license state, back to the calling procedure, writeReport. We will also need to pass to the license state to convertLicenseState. Passing it as String will meet our needs.

So change the description of the function to:

Private Function convertLicenseState(licenseState As String) As String

End Function

41. In getLicenseState we are going to use a Select Case statement to determine what value to return back to writeReport based on the value of resource.LicenseState.State. As we said there are three possible states so our select case statement will contain three possible cases. Once we have determined what we want to return we simply assign that value to the function name to return it i.e. convertLicenseState = "Return Value". Our completed function will look like:

Private Function convertLicenseState(licenseState As String) As String

Select Case licenseState

Case libLicenseStateUnlocked
convertLicenseState = "Unlocked"
Case libLicenseStateExpires
convertLicenseState = "Temporary"
Case libLicenseStateLocked
convertLicenseState = "Locked"

End Select


End Function

42. We are now ready to add the next line to our writeReport procedure in which we call the convertLicenseState function to determine what to write to column C:

Public Sub writeReport()

Dim objResourceCache As LbxLibrarianResources
Dim resource As LbxLibrarianResource
Dim counter As Integer

Set objResourceCache = objLDLS.Librarian.Information.Resources

For Each resource In objResourceCache

counter = counter + 1

Range("A" & Format(counter + 1)).Value = objLDLS.Librarian.GetSortableResourceTitle(resource.ResourceID).SortTitle
Range("B" & Format(counter + 1)).Value = resource.ResourceID
Range("C" & Format(counter + 1)).Value = convertLicenseState(resource.licenseState.State)


Next resource


End Sub

43. Next we need to add a line to writeReport to write the version to column D. This information can be accessed from the KeyMetadataVersion property of the resource so our code now becomes:

Public Sub writeReport()


Dim objResourceCache As LbxLibrarianResources
Dim resource As LbxLibrarianResource
Dim counter As Integer

Set objResourceCache = objLDLS.Librarian.Information.Resources

For Each resource In objResourceCache

counter = counter + 1

Range("A" & Format(counter + 1)).Value = objLDLS.Librarian.GetSortableResourceTitle(resource.ResourceID).SortTitle
Range("B" & Format(counter + 1)).Value = resource.ResourceID
Range("C" & Format(counter + 1)).Value = convertLicenseState(resource.licenseState.State)
Range("D" & Format(counter + 1)).Value = resource.KeyMetadataVersion


Next resource


End Sub

44. We have one more statement to add to our writeReport procedure. With the last statement we are going to display in the Excel statusbar the percentage of known resources processed. We can easily determine the total number of known resources from the count property of the objResourceCache. By dividing the counter / objResourceCache.count and using the FormatPercent function we can easily determine the percentage of resources processed. The function takes two arguments, the number we want to convert to a percentage, and the number of decimal places we want to display. The final format or our writeReport procedure is:

Public Sub writeReport()


Dim objResourceCache As LbxLibrarianResources
Dim resource As LbxLibrarianResource
Dim counter As Integer

Set objResourceCache = objLDLS.Librarian.Information.Resources

For Each resource In objResourceCache

counter = counter + 1

Range("A" & Format(counter + 1)).Value = objLDLS.Librarian.GetSortableResourceTitle(resource.ResourceID).SortTitle
Range("B" & Format(counter + 1)).Value = resource.ResourceID
Range("C" & Format(counter + 1)).Value = convertLicenseState(resource.licenseState.State)
Range("D" & Format(counter + 1)).Value = resource.KeyMetadataVersion

Application.StatusBar = FormatPercent(counter / objResourceCache.Count, 0) & " of known resources processed . . ."


Next resource


End Sub

45. We are now ready to run our code. First from the VBE menu, FILE->SAVE. Once you have saved you can then close the VBE, FILE->CLOSE, as we want to run our code from the Excel interface.

46. From the Excel menu, TOOLS->MACROS->MACROS. In the list of macros click on generateDatabase, then press Run. As the list of known resource in your library builds, note the message in the status bar showing you progress status. Obviously the larger the library the longer it will take to process.

Conclusion

In this lesson we have looked at how to write data to an excel cell, including how to control which cell we are writing to, and how to access the LDLS resource cache and extract basic information about a resource. In doing this we have also looked further at debugging code, program control structures for looping through a collection (For each . . . Next) and making a decision (Select Case . . . . End Select), and passing arguments to a function and returning values from a function.