4

I am trying to read particular cell values of Excel and store in a table. But receiving an Invalid cast exception. Below is my code in VB Script:

Dim oExcel As Object = CreateObject("Excel.Application")
Dim FileName As String
FileName = Variables.FileName
Dim oBook As Object = oExcel.Workbooks.Open(FileName)
Dim oSheet As Object = oBook.Worksheets(1)
Output0Buffer.AddRow()
Output0Buffer.OrgName = oSheet.Range("A4").Value
Output0Buffer.UnitName = oSheet.Range("B6").Value
Output0Buffer.MonthName = oSheet.Range("A8:A19").Value
Output0Buffer.Goals = oSheet.Range("B8:B19").Value
Output0Buffer.Actual = oSheet.Range("C8:C19").Value

The Error Message is:

System.InvalidCastException: Conversion from type 'Object(,)' to type 'String' is not valid

Can someone please help me identify the issue and provide a resolution?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ranjan Allen
  • 41
  • 1

1 Answers1

1

The main problem is that you are trying to assign an Excel Range (More than one cell), into a single column:

  • oSheet.Range("A4") will return A4 entire column
  • oSheet.Range("A8:A19") will return all cells in this range A8:A19

Instead of using Range property:

Output0Buffer.OrgName = oSheet.Range("A4").Value

You should use Cells property to read a specific cell:

Output0Buffer.OrgName = oSheet.Cells(4,1).Value

To read more about Cells property you can refer to the following documentation:

Hadi
  • 36,233
  • 13
  • 65
  • 124