Solved

Export To Excel

Posted on 2003-12-08
19
3,658 Views
Last Modified: 2013-12-18
Can someone look at the below code and maybe clean it up and fix it?  Apparently, the user tells me, that when it comes to opening the excel files after exporting they do not open. He wants the files to open up in one instance of excel being open. I am not sure why it is not working as I am not very familiar with script. I would also like to have some error handling built into it.  Thanks.

Code:

Option Explicit

Declare Function NEMGetFile Lib "nnotesws" Alias "NEMGetFile" ( wUnk As Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As String ) As Integer
Declare Function NEMProgressBegin Lib "nnotesws.dll" ( Byval wFlags As Integer ) As Long
Declare Sub NEMProgressEnd Lib "nnotesws.dll" ( Byval hwnd As Long )
Declare Sub NEMProgressSetBarPos Lib "nnotesws.dll" ( Byval hwnd As Long, Byval dwPos As Long)
Declare Sub NEMProgressSetBarRange Lib "nnotesws.dll" ( Byval hwnd As Long, Byval dwMax As Long )
Declare Sub NEMProgressSetText Lib "nnotesws.dll" ( Byval hwnd As Long, Byval pcszLine1 As String, Byval pcszLine2 As String )
Const NPB_TWOLINE% = 1 '1 is for the big "in its window" progress bar
Const NPB_NOTEXT%=32 'and 32 is for the small blue line at the bottom of the screen
Const xlAutomatic = -4105
Const xlBottom = -4107
Const xlCategory = 1
Const xlCenter = -4108
Const xlColumnClustered = 51
Const xlContinuous = 1
Const xlDataLabelsShowValue = 2
Const xlDataLabelsShowPercent = 3
Const xlEdgeBottom = 9
Const xlEdgeLeft = 7
Const xlEdgeRight = 10
Const xlEdgeTop = 8
Const xlHairline = 1
Const xlInsideHorizontal = 12
Const xlInsideVertical = 11
Const xlLandscape = 2
Const xlLeft = -4131
Const xlLine=4
Const xlLineMarkers = 65
Const xlLocationAsObject = 2
Const xlMedium = -4138
Const xlNone = -4142
Const xlPie=5
Const xlPortrait = 1
Const xlRows = 1
Const xlThick = 4
Const xlThin = 2
Const xlTop = -4160
Const xlValue = 2

'R5_Export_to_Excel:

'Export_R5_Only:







Sub Click(Source As Button)
      
'This button will generate an Excel spreadsheet using all data from a view.
      Dim szFilter As String      
      Dim startTime As Single
      Dim processingTime As Single
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim v As NotesView
      Dim ws As New notesuiworkspace
      Dim uiview As NotesUIView      
      Dim docX As NotesDocument
      Dim promptlist(4) As String, choice As String
      Dim FileClose As Variant
      Dim EndScript As Variant
      
      Dim datafilename As Variant, statsfilename As Variant
      
      Dim platform As String, view As String
      Dim xl As Variant, xlWbk As Variant, xlSheet As Variant, hwnd As Variant, xlsFileName As Variant
      Dim row As Integer, col As Integer, numdocs As Integer
      
      On Error Goto errorHandler3
'Check to see if the user is on a MacIntosh
'the "Create Object" function does not run on a Mac
      platform = session.Platform
      If Not Instr (platform, "MacIntosh") = 0 Then
            Messagebox ("This function cannot be run on a MacIntosh. Please use a PC to pull this data into a spreadsheet.")
            Exit Sub
      End If
      
      EndScript = Msgbox("This Macro Exports All Documents In This Database" & Chr(10) & _
      "To An Excel Spreadsheet To Allow Up To Date" & Chr(10) & _
      " Monthly Reporting." & Chr(10) & Chr(10) & _
      "Do You Want To Continue?",(4+32+0+0),"Abort Or Continue?")
      
      If EndScript = 7 Then Exit Sub
      
      'promptlist(0)="New Spreadsheet - Display When Completed"      
      'promptlist(1)="New Spreadsheet - Save Directly to Disk"
      'promptlist(2)="Open Existing Spreadsheet - Display When Completed"
      'promptlist(3)="Open Existing Spreadsheet - Save Directly to Disk"
      
      'choice=ws.prompt(PROMPT_OKCANCELLIST, "Select Action", "Please choose how you would like to perform the export.", promptlist(0), promptlist)
      'Msgbox "right before choice=pmptlist1"
      'choice=promptlist(1)
      'Msgbox "right after choice=pmptlist1"
      'Msgbox "choice is" & choice & "<"
      'If (choice="") Then
      '      Msgbox "right after choice= nothing and end sub"
      '      Exit Sub
      'End If
      
      'Get appropriate file names when required
      'szFilter = "Excel Spreadsheet|*.xls|All Files|*.*|"      
      'If choice = promptlist(0) Then
      'Else
      '      Msgbox "right before assigning xlsfilename to long pathname"
            'xlsFileName = ws.OpenFileDialog (False, "Select Spreadsheet", szFilter)
      
      xlsFileName = "F:\In-House\Prod-Dev\NPI_NotesDB_stats\npi_export_data.xls"
      '      If xlsFileName(0) ="" Then
      '            Exit Sub
      '      End If            
      'End If      
      Msgbox "excel filename is" & xlsfilename & "<"
'check to see if excel filename is open. if so close it
      FileClose = Msgbox("Is This File Closed? " & Chr(10) & Chr(10) & xlsFileName, (4+32+0+0),"NPI_Export_data.xls Open?")
      Msgbox "fileclose variable = " & FileClose
      If FileClose = 7 Then
            Msgbox "Close File And Rerun Export Procedure"
            Exit Sub
      End If
      
'Set the session variables
      startTime = Timer
      Set db = session.CurrentDatabase
      Set uiview = ws.currentview
      Set v = uiview.view
      numDocs=v.allentries.count      
      
      Msgbox "finished setting session varibles"
      
' Initialise Progress Bar
      hwnd = NEMProgressBegin( NPB_TWOLINE ) ' use window style progress bar
      NEMProgressSetBarRange hwnd, numDocs ' set range of bar to number of rows
      NEMProgressSetText hwnd, "Exporting view to Excel File Npi_export_data.xls" , "Starting Export to Excel..."
      
      Set xl = CreateObject("Excel.application")
      'If choice = promptlist(0) Or choice = promptlist(1) Then
      Set xlWbk = xl.Workbooks.Add
      'Else
      '      Set xlWbk = xl.Workbooks.Open(xlsFileName(0))
      'End If
      Set xlSheet = xlWbk.Worksheets(1)
      Call xlSheet.Activate
      On Error Goto errorHandler    
      xlSheet.Name = "Notes Exported Data"
      xl.Cells.select    
      xl.Selection.ClearContents
      
'Start filling in the header column.  You can get rid of this if you want to, but then get rid of the section lower that highlights it...
      col=1
      With xlSheet
            Forall vColumn In v.Columns              
                  If vColumn.IsHidden = True Then
                  Else                    
                        .Cells(1, col)=vColumn.Title
                        col=col+1
                  End If
            End Forall
      End With              
      
      
'Row by Row, Column by Column, fill in the values
      Set docX=v.GetFirstDocument
      row=2
      On Error Goto errorHandler
      With xlSheet          
            While Not docX Is Nothing
                  col=1
                  Forall cValue In docX.ColumnValues
                        .Cells(row, col)=implode(cValue, Chr(10))
continue:
                        col=col+1
                  End Forall
                  row=row+1
                  If row Mod 10 = 0 Then
                        processingTime = Timer - startTime              
                        NEMProgressSetBarPos hwnd,row
                        NemProgressSetText hwnd, "Exporting view to Excel File Npi_export_data.xls", "Exporting: "& Cstr(row) & " of " & Cstr(numDocs) & " documents exported in "  & Format$(processingTime, "0.00") & " seconds, AVG = " & Format$(row / processingTime , "0.000")
                  End If
                  Set docX=v.GetNextDocument(docX)
            Wend
      End With
      On Error Goto errorHandler2
      
'Set sizing, fonts, etc to make the spreadsheet readable.
      xl.Cells.select
      xl.selection.Font.Name = "Verdana"
      xl.selection.Font.Size = 9
      xl.Rows("1:1").Select
      xl.Selection.Font.Bold = True
      xl.selection.Font.size = 12
      xl.selection.RowHeight = 15            
      xl.Cells.select
      xl.selection.columnwidth = 100
      xl.selection.columns.Autofit      
      xl.selection.rows.Autofit
      xl.selection.VerticalAlignment = xlTop
      xl.ActiveSheet.Range("A1").Select
      
'Stop Progress Bar
      NEMProgressEnd hwnd
      
'Save and be gone!
      On Error Goto errorHandler3
      
      'If choice = promptlist(1) Or choice = promptlist(3) Then
      Msgbox "right before saving export file"
      Call xlWbk.SaveAs(xlsFileName)
      Call xlWbk.Close
      xl = ""
      
      Msgbox "done saving export file and closing it and seting xl to nothing"
      '-----------------------------------------------------------------------------------------------------------------
      Msgbox "now create new xl object, set visible to true"
      Set xl = CreateObject("Excel.application")
      xl.Visible=True
      datafilename = "F:\In-House\Prod-Dev\NPI_NotesDB_stats\npi_export_data.xls"
      statsfilename = "F:\In-House\Prod-Dev\NPI_NotesDB_stats\npi_monthly_stats_data.xls"
      Msgbox " now open two excel files"
      
      xl.Workbooks.Open datafilename
      'xl.Workbooks.Open statsfilename
      Msgbox "done opening files, now set xl to nothing"
      xl = ""
      
      
'Else
      Msgbox "finished set xl to nothing, done, now going to Print the processing time and finish"
'End If
' LotusScript code...
      processingTime = Timer - startTime
      Print "The script ran in " & Format$(processingTime, "0.00") & " seconds."    
      Exit Sub
      
errorHandler:
'This is called when there is bad data in a notes view, usually text in a date field, etc.
'Notes will show it, but it will fail to export correctly.  This replaces the Excel cell with a bad data text.
      Resume
      
errorHandler2:
      NEMProgressEnd hwnd      
      Messagebox "Bad Options setting Spreadsheet format"
      Call xl.Quit
      xl = ""
      Exit Sub
      
errorHandler3:
      NEMProgressEnd hwnd      
      Messagebox "Bad Filename Specified.  Please make sure that the directory name is correct."
      Call xl.Quit
      xl = ""
      Exit Sub
End Sub
Function Implode(Array As Variant,Separator As String) As String
      Dim text As String
      Dim i As Integer
      If Isarray(Array) Then
            For i=0 To Ubound(Array)
                  If i=Ubound(Array) Then
                        text=text & array(i)
                  Else
                        text=text & array(i) & separator
                  End If
            Next
            Implode=text
      Else
            Implode=Array
      End If
End Function
0
Comment
Question by:schmad01
19 Comments
 
LVL 31

Expert Comment

by:qwaletee
ID: 9897696
That's some messed up code there!
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9897705
schmad01,
>  Apparently, the user tells me, that when it comes to opening the excel files
> after exporting they do not open. He wants the files to open up in one instance
> of excel being open.
I'm not clear whatthis means, and if this is all you need fixed.  Please explain what it is that is wrong that you want changed.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9897886
To check this code, I need a copy of the db with minimal documents to make this work.

~Hemanth
0
 
LVL 4

Expert Comment

by:sreeser
ID: 9898119
Its a lot easier to create the view you want and just export it via the File Export.

Otherwise thats a lot to ask without any clear direction even for 500 pts.  Helping out with a chunk of script or a specific problem or approach to doing something is one thing but rewiting an app... maybe I just dont have the free time that others may.  (Could explain why I haven't been here in months.)  Not a criticism just a thought.

Good luck.
0
 

Author Comment

by:schmad01
ID: 9898269
Apparently, the user tells me, that when it comes to opening the excel files
> after exporting they do not open. He wants the files to open up in one instance
> of excel being open.
I'm not clear whatthis means, and if this is all you need fixed.  Please explain what it is that is wrong that you want changed.

Qwal,
We just wanted excel to open once, then open both files. Don't want excel to open twice- once for each file.  What is happening is that the code goes thru and exports fine, but when it is supposed to open with the export and open another non-related excel document, it opens and it is blank. That is how I understand it.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9898292
Could you ask he user to show it to you in action, and get some clarity?  I mean, it sounds like he's complaining about opening a file that has nothing to do with Notes, together with a file that Notes opens.  If we can get the sequence of what he is doing, and what he does not lke abot it.  Then, we can come up with a solution or a sorry Charlie."
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9898307
This seems to be the code in question:

     xl = ""
     
     Msgbox "done saving export file and closing it and seting xl to nothing"
     '-----------------------------------------------------------------------------------------------------------------
     Msgbox "now create new xl object, set visible to true"
     Set xl = CreateObject("Excel.application")
     xl.Visible=True
     datafilename = "F:\In-House\Prod-Dev\NPI_NotesDB_stats\npi_export_data.xls"
     statsfilename = "F:\In-House\Prod-Dev\NPI_NotesDB_stats\npi_monthly_stats_data.xls"
     Msgbox " now open two excel files"
     
     xl.Workbooks.Open datafilename
     'xl.Workbooks.Open statsfilename
     Msgbox "done opening files, now set xl to nothing"
     xl = ""

Is the user just complaining that these two files, npi_export_data.xls and npi_monthly_stats_data.xls, are opening in separate application windows ("SDI") instead of both opening as MDI windows within a single Excel application window?

This largely depends on your version of Excel.  newer versions prefer the SDI interface, while older versions preferred the MDI interface.
0
 

Author Comment

by:schmad01
ID: 9898849
Qwal, According to your last comment, you described the problem correctly. Ok, we are using Excel 2000. Now, if we have to have excel open twice, then so be it. That would leave us with the blank problem. When the export is complete excel starts and the page is blank.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9900967
The file it opens is blank?  Or it is open with a blank sheet?

I think I know the problem.  At the top you have:

     Set xl = CreateObject("Excel.application")
Then, you drop the xl variable...

     xl = ""
     
... without closing Excel.  Instead, you may want to re-use the existing Excel object.

REM     xl = "" 'commented out
     
     Msgbox "done saving export file and closing it and seting xl to nothing"
     '-----------------------------------------------------------------------------------------------------------------
     Msgbox "now create new xl object, set visible to true"
REM     Set xl = CreateObject("Excel.application") 'commented out, becasue we never dropped the old xl
     xl.Visible=True
     datafilename = "F:\In-House\Prod-Dev\NPI_NotesDB_stats\npi_export_data.xls"
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:schmad01
ID: 9904974
Ok, I only rem'd out the first instance of the xl = "" and then rem'd out the Set xl = ..... line.

Ran the script and this is the sequence of events.
Exports the view, then opens up excel it is a blank sheet so went back to notes and noticed we had to acknowledge a message box. Went back to excel and the first file was there, but not the second file.  Are we close?
0
 

Author Comment

by:schmad01
ID: 9904987
Oh, and if possible, I would rather not have to back to notes and acknowledge the message box. I would like it to export and then be able to go to excel and see the files without intervention. (If possible).
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9909874
Change those Msgbox statements to Print statements

REM     xl = "" 'commented out
     
     'Msgbox "done saving export file and closing it and seting xl to nothing"
     '-----------------------------------------------------------------------------------------------------------------
     'Msgbox "now create new xl object, set visible to true"
     Print "Export file(s) saved, loading document(s) into Excel"
REM     Set xl = CreateObject("Excel.application") 'commented out, becasue we never dropped the old xl
0
 

Author Comment

by:schmad01
ID: 9912310
Which Msgbox statements? All of them? Please clarify.  thanks.
0
 

Author Comment

by:schmad01
ID: 9920844
Ok, now after executing the script , it exports fine, then opens excel and the exported information is there, but still does not open the second document. Suggestions?
0
 

Author Comment

by:schmad01
ID: 9922389
I feel we are almost there.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9924716
Sorry, my mailbox got full, and I wasn't getting notices.  Save a copy of what yuo have now, because I'm not sure what it looks like anymore.  Then, in the original copy, drop evrything in your script from the commented-out 'xl="" to the end, and add this in instead:

     Print "Starting to display the running Excel window, and loading file(s0 into it"
     xl.Visible=True
     datafilename = "F:\In-House\Prod-Dev\NPI_NotesDB_stats\npi_export_data.xls"
     statsfilename = "F:\In-House\Prod-Dev\NPI_NotesDB_stats\npi_monthly_stats_data.xls"
     
     xl.Workbooks.Open datafilename
     xl.Workbooks.Open statsfilename
     Print "Done opening files, now set xl to nothing"
     Delete xl

I believe at one point you had the second xl.Workbooks.Open commented out.  That may have been the remaining problem.
0
 

Author Comment

by:schmad01
ID: 9944065
Ok, great! both files open, no problem. the only last thing which I'm sure is very minor because it's not affecting the operation at all is a popup in Notes which says Illegal delete. Not sure what is causing that.
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 500 total points
ID: 9954184
Probably, it is the:
   Delete xl

Try changing it to:
  Set xl = Nothing
0
 

Author Comment

by:schmad01
ID: 10860034
Sorry I took so long.  Thanks!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now