none
VB.Net App for automating XL fails on Win 10 machine RRS feed

  • Question

  • Hi,

    I have a VB.Net app that takes a txt list of data and creates an XL file to open it.

    It has been working quite well but testing it on a Win 10 machine it keeps generating an error.

    Here is the function

    Function SaveAsXL(ByVal stFileXML As String, ByVal stFileXL As String) As Boolean
            Const WM_QUIT = &H12
            Try
                Dim fSuccess As Boolean = True
                Dim stErr As String = ""
                'Dim objExcel As Excel.Application
                'Dim objWorkbook As Excel.Workbook
                Dim objExcel As Object 'Excel.Application
                Dim objWorkbook As Object 'Excel.Workbook
    
                ' Start Excel and get Application object.
                '            objExcel = New Excel.Application
                objExcel = CreateObject("Excel.Application") 'New Excel.Application
                'objWorkbook = objExcel.Workbooks.Add()
    
                Try
                    'Hide the App
                    objExcel.Visible = False
                    Try
                        objExcel.Workbooks.OpenXML(stFileXML)
    
                    Catch ex As Exception
                        MsgBox("Error: " & ex.ToString)
    
                    End Try
                    'Workbooks.OpenXML(stFileXML)
                    objWorkbook = objExcel.Workbooks(objExcel.Workbooks.Count)
                    objWorkbook.Activate()
                    System.Threading.Thread.Sleep(500)
                    objExcel.ActiveWorkbook.SaveAs(Filename:=stFileXL, FileFormat:=objExcel.XlFileFormat.xlWorkbookNormal, _
                                                    AccessMode:=objExcel.XlSaveAsAccessMode.xlNoChange)
                    objWorkbook.Close()
                    objWorkbook = Nothing
                    objExcel.Quit()
                    PostMessage(objExcel.Hwnd, WM_QUIT, 0, 0)
                    objExcel = Nothing
                    Return True
                Catch ex As Exception
                    objExcel.Visible = True
                    Return False
                End Try
            Catch
                MsgBox("Error: declaring XL")
                Return False
            End Try
    
        End Function

    It errors at this line:

    objExcel.Workbooks.OpenXML(stFileXML)

    But actually completes the task.

    I thought about just handling the error and moving on but it would be better to find out why it errors.

    The Error was to do with Interop but was not specific.

    It was a VS 2005 development but I just tried an upgrade to VS 2017 Comunity on a Win 8.1 machine (and after a few minor changes) and it runs fine.

    Late binding is used because different versions of Excel use the App.

    Appreciate any assistance,

    Thanks


    Regards Doug


    • Edited by Dactech5 Wednesday, July 19, 2017 4:54 AM
    Wednesday, July 19, 2017 4:51 AM

All replies

  • That does not make sense to me. You catch an exception which then Returns False, yet completes the task??? Impossible. You may need another debug session.

    -Tom. Microsoft Access MVP

    Wednesday, July 19, 2017 6:13 AM
  • Hi Dactech5,

    I try to run the code on my side.

    I am using Windows 10, VS 2015 , Office 2016.

    when I run the code it run without any error and generate new Excel file.

    I suggest you to again try to make a test with other Excel file or for testing purpose try to create a new file and use that file for testing.

    it is possible that when you try to make a test with Excel file multiple times at that time file get corrupted.

    and that cause the error.

    so try to make a test with new file and let us know about the result.

    we will try to provide further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 19, 2017 9:11 AM
    Moderator
  • What is the exception message (error)? You should be able to list the trace to see what occurred leading up to the exception.

    FYI, Visual Basic .NET forum is here:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vbgeneral&forum=vbgeneral


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 19, 2017 2:13 PM
  • Tom,I can assure you that it is happening.

    Stepping through the code it errors and is trapped and the XL file is generated.

    Sorry for the late replies I did not get notified and have been busy selling my house.

    Doug


    Regards Doug

    Saturday, July 22, 2017 10:33 PM
  • Hi Doug,
    So you have a breakpoint on the line:
    objExcel.Workbooks.OpenXML(stFileXML)
    You step.
    It jumps to the Catch block.
    From there you exit the function.

    At no time did the line:
    objExcel.ActiveWorkbook.SaveAs ...
    get executed, which presumably is the line that creates the new workbook.
    So, something else must be going on. More debugging is needed.

    -Tom. Microsoft Access MVP

    Saturday, July 22, 2017 10:38 PM
  • Hi Depak,

    Thanks,

    I ran the code at home and with the same file.

    I used VS Community 2017 and Win 8.1 and it ran without any problem.

    So the file is not corrupted.

    I am back out at that site later next week.

    Doug


    Regards Doug

    Saturday, July 22, 2017 10:39 PM
  • Thanks Paul,

    I looked the error up at the time and it was not specific. Stupidly, I didn't note it down as it was late and the factory was closing down. I intended to investigate further at home. And found it runs at home without any problem.

    I will note the error next week when I am out there and run and note a trace. I assume you are suggesting 'ex.stacktrace'

    Doug


    Regards Doug

    Saturday, July 22, 2017 10:47 PM
  • Hi,

    As I stated the error occurs at:

     objExcel.Workbooks.OpenXML(stFileXML)

    and the exception is:

    Stack trace is:

    > ImportQAT.dll!ImportQAFiles.ImportTxtData.SaveAsXL(String stFileXML = "C:\Users\dbell\Documents\QATest\QATestOP\QA Excel File 7251120.xml", String stFileXL = "C:\Users\dbell\Documents\QATest\QATestOP\QA Excel File 7251120.xls") Line 490 Basic
      ImportQAT.dll!ImportQAFiles.ImportTxtData.TranslateData() Line 116 + 0x4a bytes Basic
      QATSystem.exe!QATControl.ModuleGeneralFunctions.DacProcess() Line 105 + 0xa bytes Basic
      QATSystem.exe!QATControl.FormMain.RunNow(Object sender = {System.Windows.Forms.MenuItem}, System.EventArgs e = {System.EventArgs}) Line 215 + 0x5 bytes Basic
      [External Code] 
      QATSystem.exe!QATControl.FormMain.Main() Line 104 + 0x8 bytes Basic
      [External Code] 

    The code runs and the XL file imports the data correctly.

    Thanks for any help on why this occurs.

    Doug


    Regards Doug

    Tuesday, July 25, 2017 1:43 AM
  • Hi Dactech5,

    is necessary for you to use Workbooks.OpenXML Method ?

    I can see that you are just saving the workbook.

    so if it is possible for you then try to use other method to open the file and then make a test to check whether error occurs or not.

    you can try to use Workbooks.open()

    Workbooks.Open Method (Excel)

    so that we can know the problem is in the file or in the method or at any other place.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 25, 2017 7:06 AM
    Moderator
  • Hi Depak,

    If I was opening a Workbook I would not bother using OpenXML but I am opening a text file.

    I could open it, parse and read each value but that is very slow.

    The OpenXML has been providing really good performance for over 5 years.

    I guess if there is no solution I will just handle the exception and let it continue (As it does open correctly).

    Thanks

    Doug


    Regards Doug

    Tuesday, July 25, 2017 11:23 PM
  • Hi Dactech5,

    I again try to make a test with Excel 2016 and Windows10.

    before I was using Excel file as an input.

    this time I try to use txt file as an input.

    Excel file get created successfully , without any error.

    I suggest you to check for any pending updates for Windows 10 or Office.

    it is possible that updating with latest version may solve your issue.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 26, 2017 6:19 AM
    Moderator
  • Hi Dactech5,

    is your issue solved now?

    if yes, I suggest you to post the solution and mark it as an answer.

    if your issue is still exist then let us know about that.

    we will try to provide you further suggestions to solve the issue.

    if you have any further questions then also you can ask.

    we will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 3, 2017 8:04 AM
    Moderator
  • Hi All,

    I did not resolve this issue.

    It seemed to be associated with the PC, maybe permissions or something to do with the network security??

    It did run on Win 7 so we decided to use it on a Win 7 PC and retire the application.

    Thanks for your assistance,

    Doug


    Regards Doug

    Tuesday, November 7, 2017 11:24 PM
  • Hi Dactech5,

    if it is possible for you then you can try to share your project here.

    we will try to test that project to verify that there is no issue with the code and there is something on that particular machine which generates an error.

    as the code is running successfully, I also believe that the code is fine and it is possible that the reason for the issue is something else.

    other things comes in my mind that you had mentioned that you are talking about network security and permissions.

    so are you running the code on server?

    if yes then I want to inform you that Office automation will not work correctly on server.

    it will only work correctly on client side.

    in that case you need to use Open XML as an alternative.

    Reference:

    Considerations for server-side Automation of Office

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 8, 2017 1:07 AM
    Moderator