VB6 and OpenOffice scalc Problem saving sheet

I have one remaing problem I can Open, Close and Write but cannot Save

Page 1 of 1

1 Replies - 6927 Views - Last Post: 20 February 2010 - 07:26 AM Rate Topic: -----

#1 paulmurley  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 17-February 10

VB6 and OpenOffice scalc Problem saving sheet

Posted 20 February 2010 - 06:40 AM

***********************SOLVED***************************

But you are welcome to use the code

Open a new vb6 project and paste the following code
Add Microsoft Scripting Runtime to the Project>References
Create an OpenOffice Calc Speadsheet(I save mine in MS Excel format) in the project folder with 3 sheets, if you save it in any other format change the .xls extensions below to the one you Use

***********************SOLVED***************************

:bananaman: Eternal Thanks To NoBrain :bananaman:

'****************************************************************************
'*                                                                          *
'* Open a new vb6 project and paste the following code                      *
'* Add Microsoft Scripting Runtime to the Project>References                *
'* Create an OpenOffice Calc Speadsheet(I save mine in MS Excel format)     *
'* in the project folder with 3 sheets, if you save it in any other format  *
'* change the .xls extensions below to the one you Use                      *
'*                                                                          *
'****************************************************************************
Option Explicit
    
    'used to get the reply to messagebox OK/CANCEL
    Public strReply As String
    
    'Used To Ceck if Error has been triggered
    Public blnError As Boolean

Private Sub Form_Load()
    
    'General Loop (For/Next) identifier
    Dim intLoop As Integer
    Dim intLoopSheet As Integer
    
    'Stores the new filename for the spreadsheet
    Dim strNewFilename As String
    
    'Declare variables for SCalc
    Dim oSM                   'Root object for accessing OpenOffice from VB
    Dim oDesk, oDoc As Object 'First objects from the API
    Dim arg()                 'Ignore it for the moment !
    Dim oDocument As Object
    Dim oSheet As Object
    Dim oCell As Object
    Dim lngRow As Long
    Dim lngCol As Long
    Dim strDisplay As String
    
    'Declare this for copying file
    Dim fso As New FileSystemObject

    On Error GoTo ErrorHandler
    
    'Make a name for the new sheet, format = "NewFile-YYYYMMDD-hhmmss"
    strNewFilename = "NewFile-" & Format(Now, "YYYYMMDD-hhmmss")

    'put path and filename into string
    'CHANGE THE FILE EXTENSION HERE if not .xls
    strNewFilename = App.Path & "\" & strNewFilename & ".xls"
    strDisplay = App.Path & "\Backup.xls"

    'finds out if the template file exists if you aren't using an extant
    'file comment out from next line up to and inclucing line starting fso.Copyfile
    'Dim mydir
    
    'mydir = Dir(strDisplay)
    
    'If mydir = "" Then
    '    strReply = MsgBox("Template File missing Phone Support", , "Export to Excel")
    '    Exit Sub
    'End If
            
    'copy the empty spreadsheet backup.xls to a new spreadsheet with a recognisable name
    'fso.CopyFile strDisplay, strNewFilename, True

    'Instanciate OOo : this line is mandatory with VB for OOo API
    Set oSM = CreateObject("com.sun.star.ServiceManager")

    'Create the first and most important service
    Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
      
    'Change the filename to URL format
    strNewFilename = ConvertToUrl(strNewFilename)
    
    'To Open an existing spreadsheet (pay attention to the syntax for first argument)
    'Set oDoc = oDesk.loadComponentFromURL(strNewFilename, "-Blank", 0, arg())
    'OR
    'To Create a new Spreadsheet
    Set oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, arg())

'****** Thanks to Ram11 for the oSheet stuff below & NoBrain for pointing
' out Ram11, both onDreamincode forums http://www.dreamincode.net/forums/ ******
    
    For intLoopSheet = 1 To 3
    
        'set the sheet to use
        Set oSheet = oDoc.getSheets().getByIndex(intLoopSheet - 1)
        
        'Change the Sheet Name
        strDisplay = "New Sheet" & Str(intLoopSheet)
        oDoc.getSheets().getByIndex(intLoopSheet - 1).Name = strDisplay
        
        'set the column
        lngCol = intLoopSheet - 1
        
        'Initialise the Starting row to add cells
        lngRow = 0


        For intLoop = 1 To 10
    
            lngCol = intLoop - 1
            'Stuff the cells with data
            Call oSheet.getCellbyposition(lngCol, lngRow).Setstring("Put Your String Here" & " * " & intLoop)
        
            Call oSheet.getCellbyposition(lngCol, lngRow + 1).SetValue(12345 + intLoop)
        
        Next
    
    Next
    
'****** Thanks to NoBrain Dreamincode forums - so simple I couldn't get it ******
    'To save Newly Created Document
    Call oDoc.storeToURL(strNewFilename, arg())
    'OR
    'To Save the doc if Extant
    'oDoc.store         ' save

    'Close the doc
    oDoc.Close (True)
    Set oDoc = Nothing

    Unload Form1 'close app
    Exit Sub
    
ErrorHandler:
    
    'This will tell the user where he is if he phones you with an error
    Trap_Error ("Export to SCalc")
    
    If blnError Then
        
        Unload Form1 'close app
        
    Else
    
        Resume Next
        
    End If


End Sub

Public Function ConvertToUrl(strFile) As String
'With thanks to didier.alain@kalitech.fr code used from http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html#replacement_functions

    strFile = Replace(strFile, "\", "/")
    strFile = Replace(strFile, ":", "|")
    strFile = Replace(strFile, " ", "%20")
    strFile = "file:///" + strFile
    ConvertToUrl = strFile

End Function

Public Sub Trap_Error(strTitle)

    blnError = False
    
    Select Case Err.Number
        
        'you can put specific error numbers in here with case nnnn (where nnnn is
        ' the error number) if you want to trap and display specific errors
        
        Case 53 'file doesn't exist
            strReply = MsgBox("File does not exist", , strTitle)
            blnError = True
        
        Case 3356 'Datbase open elsewhere
            strReply = MsgBox("The Database is open in another application - Please exit the other application and try again", , strTitle)
            blnError = True
            
        Case Else 'reset mouse pointer & exit this sub
            strReply = MsgBox(Err.Number & " : " & Err.Description, , strTitle)
            blnError = True
            
    End Select
    
End Sub




This post has been edited by paulmurley: 20 February 2010 - 09:53 AM


Is This A Good Question/Topic? 0
  • +

Replies To: VB6 and OpenOffice scalc Problem saving sheet

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: VB6 and OpenOffice scalc Problem saving sheet

Posted 20 February 2010 - 07:26 AM

take a look at this link
http://www.oooforum....c.phtml?t=73253

i hope it help you :P

it must be something like this
Set oCalcDoc = oDesktop.loadComponentFromURL(......) 
' --- make your changes here --- 
oCalcDoc.store         ' save 
oCalcDoc.close(True)  ' close


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1