OpenOffice AND Programming

[tylersong55]

Hello All!
I am new here to this forum but I was directed here by the Microsoft Forums for this question that I have. So here it goes:
I am currently working on a program through Visual Studio 2013 Express. This program, as it stands, is a display board that I will use in my workplace. For ease of access I created a way to load a Microsoft Excel document to a datagridview. When a button is clicked this operation loads the designated spreadsheet from the C drive of the subject computer and displays it in a similar fashion. The way it currently works is fine...BUT I want to be able to enable the user to load that file whether it is in the format for Microsoft excel or Apache OpenOffice. I have the code setup to where it will "Try" the operation. I want to see if I can get the "If...Then" Operation to execute whichever format that it has to use. Here is the code so far:

Code: Select all

Private Sub LoadXMLButton2_Click(sender As Object, e As EventArgs) Handles LoadXMLButton2.Click

        Try

            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim dataset As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            Dim path As String = "C:\Numbers.xlsx"

            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
                            + path + ";Extended Properties=""Excel 12.0"";")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

            dataset = New System.Data.DataSet
            MyCommand.Fill(dataset)
            NumBox1.DataSource = dataset.Tables(0)


            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub

In this code the SchBox1 is the datagridview. Now I want to be able to have it try to access either this format or if this one brings back an error then to try to open the designated folder in OpenOffice format. So the code will have to be changed to have it
Try to open when button is clicked,
If the document is in microsoft.office.excel... format Then open in the corresponding format.
ElseIf the document is in OpenOffice format Then open the document in that corresponding format.

I don't know if this is too much to be done but if anyone has any suggestions or examples then please let me know! All input is appreciated!
Thank you!

[kingfisher]

The first idea which sprang to mind was simply to identify the file extension, unsatisfactory possibly because the extension may be incorrect.

Code: Select all

Sub fileExtension

Dim sString : sString = RIGHT (ThisComponent.Title, 3)
IF sString = "ods" Then
Msgbox ( sString )
Else Msgbox ( "File is not in open document format" )
End IF

End Sub

The following code was generated by the MRI extension :

Code: Select all

Sub Snippet
  Dim oDocumentProperties As Variant
  Dim sGenerator As String

  oDocumentProperties = ThisComponent.getDocumentProperties()
  sGenerator = oDocumentProperties.Generator
End Sub

For an .ods file that yielded the string OpenOffice/4.1.1$Linux OpenOffice.org_project/411m6$Build-9775. For an .xls document there was an empty string. Both documents were read as ThisExcelDoc using the code :

Code: Select all

Sub Snippet
  Dim sVBAGlobalConstantName As String

  sVBAGlobalConstantName = ThisComponent.VBAGlobalConstantName
End Sub

[tylersong55]

Sorry if this is frustrating but I'm not grasping what you said. I am very new to programming so this isn't very easy for me to understand. Can you explain further about where these things go and what they perform?

[kingfisher]

Sorry. Because you posted code I jumped to the conclusion that you were familiar with coding.

You wanted a way of separating the treatment of each document depending on whether it is in an Excel format or the open document format. Because there is more than one Excel file extension I used the open document file extension for spreadsheet documents as the test. There may be a better way to test the format but I couldn't find it. Someone else will probably know.

You could look at these two threads : Basic Documentation | How To Install A Code Snippet.

[tylersong55]

So do u think there is a way to enable the "saving and loading" / "reading and writing" task without a specific format? I just need an easy way to save and load the data grid view whether or not it is OpenOffice or excel or something completely different. There aren't enough sources online about this topic which I figured would have been done 10 times over but foe whatever reason that's not the case.

If anyone can help me then I would greatly appreciate it!

[RoryOF]

If the users do not need editing access to the datagrid, why not use a format like PDF, which can be read in any computer? The person who generates the datagrid will need to export it as PDF, but all users will then be able to read it.

[tylersong55]

Well the user needs to be able to edit it for easy access. I want it to be the interface where they can edit it. But also so they can open the document separately and edit it. The spreadsheet format is the most convenient for the user since they need it for schedules and numbers reports tod be displayed.