sarvan
New Member.
2505 views

Issue with Excel while execution

Hi,

I have the below code written to import data from one excel file to a datatable while automation script execution in Silk Test Workbench 15.5

When this code is executed in my machine, I don't see the Driver file getting opened. But, when I executed in another machine, I could the Driver file is getting opened and then getting closed. Why I am seeing the file in other machine?

Both the machines have Silk Test 15.5 installed and has VS 2010 and VS 2012 respectively.

My machine have VS2010 and excel 15.0 and the other machine has VS2012 and excel 16.0. 

Imports SilkTest.Ntf.Wpf
Imports System.Diagnostics
Imports Microsoft.VisualBasic
Imports Excel = Microsoft.Office.Interop.Excel

Public Module Main

Dim _desktop As Desktop = Agent.Desktop

Public dDataTables As New Dictionary(Of String, List(Of List(Of String)))
Public tDataTable As New List(Of List(Of String))
Public tHeaderRow As New List(Of String)
Public tDataRow As New List(Of String)

Public Sub Main()

Dim sBookName = "C:\Users\ADONRS\Desktop\Driver.xlsx"
Dim sTableName = "RPack"
Dim sSheetName = "RPack"

ImportSheet(sBookName, sTableName , sSheetName)

End Sub

Public Sub ImportSheet(sBookName As String, sTableName As String, sSheetName As String)

Dim objExcel As Excel._Application
Dim objBook As Excel._Workbook
Dim objSheet As Excel.Worksheet
Dim rowsList As New List(Of List(Of String))

objExcel = CreateObject("Excel.Application")
objBook = objExcel.Workbooks.Open(sBookName)
objSheet = objBook.Sheets(sSheetName)

Dim objRange As Excel.Range = objSheet.UsedRange
Dim iRowCount As Integer = objRange.Rows.Count
Dim iColCount As Integer = objRange.Columns.Count

For iRowNo = 1 To iRowCount
Dim fieldList As New List(Of String)
If objSheet.Cells(iRowNo, 1).Value & "" = "" Then Continue For
For iColNo = 1 To iColCount
fieldList.Add(objSheet.Cells(iRowNo, iColNo).Value & "")
Next
rowsList.Add(fieldList)
Next
System.Threading.Thread.Sleep(5000)
objBook.Save()
objBook.Close()
objExcel.Quit()

ReleaseObject(objSheet)
ReleaseObject(objBook)
ReleaseObject(objExcel)

If dDataTables.ContainsKey(sTableName) Then dDataTables.Remove(sTableName)
dDataTables.Add(sTableName, rowsList)

End Sub



Public Sub ReleaseObject(ByVal obj As Object)

Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try

End Sub

End Module

0 Likes
4 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Issue with Excel while execution

Hi,

The problem is likely related to the Excel Interop dll.

Did you copy the interop dll from the machine where the script works to the failing machine? If so, you likely need to get the version of this dll specific to Excel 15.0. You can generally copy the required version of this dll via command prompt from the following location:

c:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\<version>\Microsoft.Office.Interop.Excel.dll

Where <version> is the version of Excel installed on your system. For example the full path on my system is:

C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\14.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

Regards

Robert

Tags (3)
0 Likes
sarvan
New Member.

RE: Issue with Excel while execution

Hi,

My Machine has VS2010 and Excel Version 15.0

I have added the assembly reference from the below path to the script:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Access.dll

The Microsoft.Office.Interop.Access.dll has the version 14.0.4756.1000

Another machine (Machine B) also has VS2010 and VS2012 and Excel Version is 16.0

I have added the assembly reference from the below path to the script:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15 \Microsoft.Office.Interop.Access.dll

This Microsoft.Office.Interop.Access.dll has the version 15.0.4420.1017

This is what we have done initially and the excel file gets opened during execution.

Later, we removed the above Interop dll reference and added the the reference from the mentioned below path to the script:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Access.dll

This  Microsoft.Office.Interop.Access.dll has the version 14.0.4756.1000. This is same as in my machine.

Now, also the same issue in Machine B. The excel file gets opened.

0 Likes
Micro Focus Contributor
Micro Focus Contributor

RE: Issue with Excel while execution

If you're certain that your interop versions are correct then you can also try this:

objExcel = CreateObject("Excel.Application")

objExcel.Visible=false    ' Don't display the spreadsheet

objBook = objExcel.Workbooks.Open(sBookName)

objSheet = objBook.Sheets(sSheetName)

0 Likes
sarvan
New Member.

RE: Issue with Excel while execution

Hi,

Even after setting objExcel.Visible = False, the excel file is displayed during execution.

The excel is 32-bit. Does that cause this issue?

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.