Loading an .xlsx file in Spread 8 COM

Posted by: bkh_vb on 18 February 2018, 11:26 am EST

  • Posted 18 February 2018, 11:26 am EST

    I am converting from Spread7.com to Spread8.com. I loaded the Trial version and my application was automatically upgrated to Spread 8. However, I could not load an Excel 2007 (.xlsx) file. named "Test File - 02-18-18.xlsx" To see if it was a problem with my application, I created another test application containing only 2 controls -- fpSpread1 and Command1 with the following code within the Command1 button necessary to load a file named "Test File - 02-18-18.xlsx" with the same results I received in my application.


    Private Sub Command1_Click()

    Dim ExcelTest As IsExcel2007FileConstants
    Dim F As Boolean

    ExcelTest = fpSpread1.IsExcel2007File("C:\Test File - 02-18-18.xlsx")

    MsgBox Str(ExcelTest)

    If ExcelTest <> 1 Then
    MsgBox "Warning - C:\Test File - 02-18-18.xlsx may not be in a valid Excel format"
    End If

    F = fpSpread1.OpenExcel2007File("C:\Test File - 02-18-18.xlsx", "", -1, "", "")

    End Sub


    The variable ExcelTest returned a 0 which means that the file was not in a valid Excel format when in fact, it was created by Excel 2007 and therefore a valid .xlsx file. I also tried to dimension the variable ExcelTest as an integer and got the same results.

    The code preceded to load the file using the OpenExcel2007File method, and I received another error message -- Run-time error '13' Type mismatch which usually means an undimensioned variable, yet F was dimensioned as Boolean in accordance with Spread8 documentation.

    Perhaps you can guide me as to where I am going wrong.
  • Replied 18 February 2018, 1:03 pm EST

    Forgot to mention. I am using VB6 running in XP Mode under Windows Virtual PC on a Window 7 Pro system.
  • Replied 19 February 2018, 1:39 am EST

    Hello,

    Please test the attached sample application with your Excel file. In case the issue continues, you can create a Log file using the code as follows
    fpSpread1.OpenExcel2007File "C:\Myxls.xlsx", "", -1, -1, App.Path & "\ImportExport\log.txt"

    and provide me your Excel file along the log file to so that I can debug it further.


    Thanks,
    Deepak SharmaSpreadCOM_ImportExport.zip
  • Replied 19 February 2018, 3:22 am EST

    Can't seem to load the attached file as there is no a .vbp file included in the ZIP so I tried loading just a form and received an error.
  • Replied 19 February 2018, 3:56 am EST

    Attached is the sample app that I developed. Put the sample Test File - 02-18-18.xlsx in the C:\ directory or put it where you want it and change the statement in the attached application.LoadTest.zip
  • Replied 19 February 2018, 10:18 pm EST

    Hello,

    Thank you for providing me the sample application, I was able to load the Excel file by just correcting the OpenExcel2007File code. You have given " " for sheet index to open which is an invalid parameter.
    Please correct the code as follows:
    F = fpSpread1.OpenExcel2007File("C:\Documents and Settings\STDUser\My Documents\ExcelLoadTest\Load Test\Test File - 02-18-18.xlsx", "", -1, -1, "")

    Thanks,
    Deepak Sharma
  • Replied 20 February 2018, 1:15 am EST

    Deepak, I will give it a try and let you know. Thanks!
  • Replied 20 February 2018, 5:43 am EST

    Hi Brian,

    The XLSX support in Spread COM 8 is a bit finicky and you must ensure that it is set up correctly on the target machine when you deploy your application, or else it will fail. We cover the redistribution requirements here, but the parts about the Excel 2007 support dlls is somewhat confusing:
    http://help.grapecity.com/spread/Spread_COM_ReadMe/webframe.html#redistribution.html

    The reason is the way we needed to implement that support, since the code was implemented in c# managed code for the .NET Windows Forms version and then ported to work for the COM version. So, that feature was designed to work using dynamic calls using COM Interop to call into the Excel 2007 module, which then uses P/Invoke to call back into the Spread COM module to load the content. That means in order to work, you must ensure that (1) the Spread OCX you are using is correctly installed and registered for COM using regsvr32.exe, and (2) the corresponding Excel 2007 DLL for that Spread OCX (see the table in the readme topic) is correctly installed and registered for COM interop using regasm.exe with the /codebase option and from the same folder location on the system (the OCX and DLL must be registered in the same location in order for them to find each other correctly).

    Assuming that you do have the .NET 2.0 framework installed on your developer machine, the setup installer for Spread COM 8 should ensure that the Excel 2007 DLLs are correctly installed and registered for the ANSI and Unicode OCX controls (FPSPR80.OCX and FPSPRU80.OCX) and for the ANSI and Unicode DLL controls (SPR32D80.DLL and SPR32DU80.DLL) which are used by the ANSI and Unicode Spread Designer executables. You should be able to use those APIs for loading, saving, and identifying Excel 2007 files on the development machine immediately after installing, and they should work inside the Spread Designer tools as well.

    Hope that helps!

    Regards,
    -Sean
  • Replied 20 February 2018, 11:10 am EST

    I tried Deepak's code he sent over earlier today at 7:18am and the revised code did not error, but still would not load the .xlxs spreadsheet -- the value of F was false.

    I also want to mention that I develop my application using VB6 in the XPMode under Windows Virtual PC on the developer machine. I do not compile the sample application, but run it in the IDE and it did not work. Although have experienced no problems with any other application that I use in XPMode, I do not know if it contains .NET or not -- how can I determine if .NET is available in XPMode?

    Sean, also, I am not a professional programmer as I simply created the application for my own use in my consulting practice and leave a copy behind with my clients and much of what you stated in your response I do not understand. Although my application has over 500K lines of code and is quite complex, I use a lot of standard basic statements, supplemented by 3rd-party tools. I compile my application creating an MSI file which takes care of all the necessary included files. In the past, to install I simply execute the MSI and the installation routine takes care of all the registration -- never had any problems when installing on XP, Win7, Win8, or Win10.

    It might appear that I am out of luck with regards to upgrading my application to read .xlsx files as even reference to the redistribution information is confusing.

  • Replied 20 February 2018, 11:23 am EST

    Apparently, XPMode does have .NET frameworks installed, it has Version 2, 3, and 3.5. Sorry for my ignorance on this topic.
  • Marked as Answer

    Replied 21 February 2018, 11:04 pm EST

    Hello,

    Could you please test the application I provided in my earlier post and let me know if that works fine for export to excel?
    I would also suggest you to please be sure that the 'FARPOINT.SPREAD8.EXCEL2007.DLL' is registered on your machine where you are facing this issue.
    In order to use the Excel 2007 (xlsx) import and export methods, you need this file along .NET framework.
    Use the Microsoft Assembly Registration tool (regasm.exe) to register this assembly. The Excel 2007 methods require the Microsoft .NET 2.0 Framework, must be registered with regasm.exe using the /codebase option. Please make sure that the Spread 8 module is in the same folder as the corresponding 'FARPOINT.SPREAD8.EXCEL2007.DLL' or can be found in the system path.


    Thanks,
    Deepak Sharma
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels