Export current view to VBA Excel

hcl-bot · February 8, 2008, 12:59pm

Is it possible to export the current view?

Now I’m expanding/collapsing all the view items to set it up the way I need it, then I export it in a csv format, then I run a VBA function in Excel to open the csv file and do the job.

I would like to skip the export step, either (1) by exporting to csv format from inside Excel, or (2) by reading the current ui view data from Excel.

I didn’t find any export to csv method.
In a post I found a mention to createviewnavigator, but it doesn’t show up neither in my object browser nor in any documentation.

Thanks,

Stefano

hcl-bot · February 8, 2008, 1:36pm

Subject: Export current view to VBA Excel

look in the sandbox, there are plenty of examples of exporting to excel, including my own.

John

hcl-bot · February 8, 2008, 3:23pm

Subject: RE: Export current view to VBA Excel

Thanks Jhon.

I didn’t know the Sandbox, I googled it and I found it. That’s great, I will find a lot of stuff there.

I checked some examples, but they do what I was trying to do. They do it this way:

Set vw = ses.currentview

Set coldocs = vw.documents

Set doc = coldocs.getfirstdocument

…

Set doc = coldocs.GetNextDocument(doc)

I already did try this, but it exports all the view documents, including the hidden ones.

Instead if I open the view, then expand/compress some items, then i export it in csv format, I only get the view as I see it, with the compressed headers and the expanded data, without the hidden data (compressed nodes).

Another example uses the CreateViewNav. If I understand how it works also this is not good for me, because it is a method of NotesView and not of NOTESUIVIEW, and if I understand the only way to access what the user is doing is to use the NOTESUIVIEW.

Correct?

Another point: I need this macro to run into Excel because this import is the first step of a much longer job. I know very well VBA and Excel, but this is the first time I use Lotus via COM.

I think the start point should be this:

Dim workspace As NOTESUIWORKSPACE

Dim uiview As NOTESUIVIEW

Set workspace = CreateObject(“Notes.NotesUIWorkspace”)

Set uiview = workspace.CURRENTVIEW

but the early binding doesn’t work. I added dombj.tlb and notes32.tlb as references, I see them in the object browser, but if I run I get a “Type mismatch” error.

If I declare the variables as variants or objects then it works.

Thanks,

Stefano

hcl-bot · February 8, 2008, 3:44pm

Subject: RE: Export current view to VBA Excel

Stefano,

Try looking at what I have out there. While it is an old version, it does allow the selection of documents, plus you can print other fields even if they are not in the view.

You could then even put in your marco with what I have, and then execute it.

cheers John

ps. I don’t believe I have ever seen anything where you select just the category and then be able to export thoese documents, although I guess it can be done.

hcl-bot · February 8, 2008, 7:30pm

Subject: RE: Export current view to VBA Excel

Thanks John, I found your example, but it’s useless to me, it is too complex.

I don’t know Lotus, it took 2 hours to me to be able to start it in debug mode with your sample database and see what was happening, only to get some error messages.

If there is a way from an Excel VBA function to tell Lotus to export the current view in CSV format I will do it, otherwise I give up and a manual export will do.

It is the first time I try to use the Lotus IDE and I look at the Lotus object model, but I had a really bad impression.

I’m used to work with Microsoft Office and other software VBAs, and compared to them the Lotus documentation is difficult to browse, and the IDE is primitive. It is the first time I have problem with exporting data from an application to another format. Usually after 30 minutes with a new application, a look at the object model it’s enough, with Lotus one day was enough only to get failures and find in the documentation again and again “the COM interface is not available for this method.”

Do I miss something?

Is the “IBM Lotus Domino Designer” the only/best way to customize Lotus?

Thanks,

Stefano

hcl-bot · February 11, 2008, 5:14am

Subject: RE: Export current view to VBA Excel

To complex? A product called Integra Quick Reports takes the end-user approach: It shows a 4 step wizard allowing you to export the current view (and more fields, and more options, etc…)

Have a look at http://www.integra4notes.com

hcl-bot · February 8, 2008, 9:33pm

Subject: RE: Export current view to VBA Excel

Domino Designer is the best way to create code that works from within Notes/Domino.

As for the COM interface, it only allows access to the back-end Domino objects. There is an older OLE interface (the reference name would be Lotus Notes Automation Objects) that allows access to the UI. It still won’t be of much help in your situation, since there are no hooks at that level to the UI view, even in native Lotusscript. To get where you want to go programmatically, you would need to use the C language API.

Putting abstract complexity aside for a moment, can you explain what you want the export operation to do? You said you are “setting up” the UI view, but not how you’re doing it. Do you want to export only certain categories? From one particular category? Categories with subcategories? Is there a pattern to what you are doing that can be expressed easily, or are you looking for something that’s so ad hoc that it would be difficult to express without the view open in the UI.

Selecting documents to export based on categories is not particularly difficult, and can as easily be done from a VBA form in an Excel macro as from a UI view in the Notes client. If you can explain clearly what you need, we can point you to a solution (and explain it in such a way as to make further work easier for you in the future).

hcl-bot · February 11, 2008, 6:00pm

Subject: RE: Export current view to VBA Excel

This is the description of the manual process:- I open the Material Inventory database (we are talking about colored aluminum sheets)

I select a few options on the left pane in order to access to the desired view with the list of colors
On the list of colors I click on the green arrow on the left to expand one or more colors, this will show a list of shades for each color
I expand one or more shades, this will show a list of purchase orders
I expand one or more purchase orders, this will show the list of sheets that I want to export
I click on File - Export, set the type to CSV, enter a name, check include view titles and save

Then I run the VBA function that processes this file.

I need to integrate the existing inventory database with my Excel add-ins. I’m new to Lotus, I don’t even know how to create a view in Lotus.

I tried to make my first VBA function in Excel to read from Lotus, and I was able to import data from the current view, but all I get is the whole content of the view (or of the database), not just the expanded parts.

I didn’t go into detail in my previous post because I thought there was a quick way for executing the “file - export” command.

Is that available with a C API?

I often use APIs in VBA, can I use it in this case?

If the best is to export from within Notes, can I make a function inside Notes and run that function from Excel, wait for that function to create the CSV file, and keep going from there?

I also tried to import the whole database inside Excel, then creating in Excel a new interface for filtering the right materials, but I can only read the whole view content, that is thousands of items, and that takes forever using COM.

I need either to be able to access only the expanded items of the view (then I can use COM to read all the values of the few items) or to save as a CSV.

Thanks,

Stefano

hcl-bot · February 13, 2008, 9:56am

Subject: RE: Export current view to VBA Excel

Anybody out there can help?(I don’t like to give up at the very first attempt.)

Thanks,

Stefano

hcl-bot · February 14, 2008, 11:26am

Subject: RE: Export current view to VBA Excel

Here is a sample using LotusScript. It would need to go into an Action Button on your view. Select the documents first, then click the action button. The domDBUnprocessedDocuments property is how you detemine which documents were selected. Good luck!

On Error Goto HandleError

Dim officeApp As Variant

Dim xlApp As Variant

Dim xlsheet As Variant

Dim array As Variant

Dim lname As String

Dim fname As String

Dim k As Integer

Dim IsFirstTry As Integer

Dim WindowsDirectory As String

WindowsDirectory = Environ("Windir")



Set domSes = New NotesSession

Set domDb = domSes.CurrentDatabase

'Unprocessed Documents is the key to finding selected documents

Set domColl = domDb.UnprocessedDocuments

Set domDoc = domColl.GetFirstDocument

If domColl.Count = 0 Then

	Messagebox " At least one document must be selected in the view.",16,"Application Error"

	Exit Sub

End If



Set xlApp = CreateObject("Excel.application")

xlApp.Workbooks.Add

Set xlsheet = xlApp.Workbooks(1).Worksheets(1)

'Write column titles

With xlsheet

	.range("A1") = "RowTitle1"

	.range("B1") = "RowTitle2"

	.range("C1") = "RowTitle3"

	.range("D1") = "RowTitle4"		

	'etc, etc, etc.

End With



k = 1      'k keeps track of the row number

While Not (domDoc Is Nothing)

	k = k + 1			

		'write dataset to Excel worksheet

	With xlsheet

		.range("A" & k) = domDoc.Field1(0)

		.range("B" & k) = domDoc.Field2(0)

		.range("C" & k) = domDoc.Field3(0)

		'etc, etc, etc.

	End With

End With

Set domDoc = domColl.GetNextDocument(domDoc)

Wend

'The next 2 lines sort the worksheet by Column H, then Column I. Delete if you don't need.

xlsheet.cells.select

xlapp.selection.sort xlsheet.range(“H1”) , 1 , xlsheet.range(“I1”) , , 1 , , , 1 , 1, False , 1, 0, 0, 0

'The “if” statement deletes the file if it already exists - I am overwriting the same file each time

If Dir$( WindowsDirectory + “\temp\envsource.xls”) <> “” Then

Kill Environ("Windir") + "\temp\envsource.xls"

End If

On Error Goto 0

xlapp.activeworkbook.saveas WindowsDirectory + “\temp\envsource.xls”

xlapp.activeworkbook.close

xlapp.quit

xlapp = “”

hcl-bot · February 11, 2008, 8:37am

Subject: RE: Export current view to VBA Excel

Stefano,

I’ll have to agree with Stan on this one. We need more information on exactly what youa re trying to do. Using the export that I had out there is used by lots of companies over the world. Most people use it as is, although people good with script have made changes. I’m not sure of why you had so many issues.

John

hcl-bot · February 11, 2008, 6:13pm

Subject: RE: Export current view to VBA Excel

John, I tried to use your tool as it is, not in my database.The documentation walks through an example with the Customer.nsf dB, so I tried to follow the steps with that example and I got this error:

"The Program Experiend an unexpected Error

Error Number: 4063

Description Database has not been opened yet

Program Aborting"

I also quickly went through the code, but I guess I need to learn something more about Lotus before being able to understand it.

You can find a more detailed description of my problem on the reply to Stan that I just posted.

Thanks,

Stefano