Hello,

im trying to make a visual basic scritp to read a full column of an Excel document where the name of the first sheet (HMI tags) is the one i want to read (and the first column)

I did this script but getting some errors (one of them about not accepting property or method 438), can help me to solve the script pls?

------------------------------------------------------------------

------------------------------------------------------------------------------

THANKS FOR THE HELP

Hello GusoXXL,

please be so kind and don't post twice for the same problem.

You also make second topic for your problem:

Reading Values with VBscript from Excel

GusoXXL

Hello,

im trying to make a visual basic scritp to read a full column of an Excel document where the name of the first sheet (HMI tags) is the one i want to read (and the first column)

I did this script but getting some errors (one of them about not accepting property or method 438), can help me to solve the script pls?

------------------------------------------------------------------

Sub Excel2()

'Var definition

Dim ExcelObject

Dim WorkSheet

Dim Workbook

Dim objTag1

Dim Rwcnt

Dim i

Dim value

Dim path

Dim ErrorDesc

'Errors

On Error Resume Next

'Path for the workbook

path = "C:\Users\VijeoSCADA\Desktop\Excel\Prueba.xlsx"

'Object for the process

Set ExcelObject = CreateObject("Excel.Application")

ExcelObject.Visible = False

ExcelObject.DisplayAlerts = True

'Error loop

If Err.Number <> 0 Then

ErrorDesc = Err.Description & "(" & Err.Number & ")"

SmartTags("ErrDesc") = ErrorDesc

Set ExcelObject = Nothing

Exit Sub

End If

'Setting workbook to open it with the path

Set Workbook = ExcelObject.Workbooks.Open (path)

'Openning first sheets

Set WorkSheet = ExcelObject.Activeworkbook.Worksheets(1)

'Error loop

If Err.Number <> 0 Then

ErrorDesc = Err.Description & "(" & Err.Number & ")"

SmartTags("ErrDesc") = ErrorDesc

Set ExcelObject = Nothing

Exit Sub

End If

'Number of rows in the porject

Rwcnt= ExcelObject.AtiveWorkbook.Sheets(1).UsedRange.Rows.count

'Error loop

If Err.Number <> 0 Then

ErrorDesc = Err.Description & "(" & Err.Number & ")"

SmartTags("ErrDesc") = ErrorDesc

Set ExcelObject = Nothing

Exit Sub

End If

'Loop

For i=2 To Rwcnt

objTag1= WorkSheet.Cells(i,1).Value

Next

'Relation between var and smarttags

Set objTag1= SmartTags("PruebaUA")

'Error loop

If Err.Number <> 0 Then

ErrorDesc = Err.Description & "(" & Err.Number & ")"

SmartTags("ErrDesc") = ErrorDesc

Set ExcelObject = Nothing

Exit Sub

End If

'Closing connections

ExcelObject.ActiveWorkbook.Save

ExcelObject.Workbooks.Close

ExcelObject.Quit

'Connections to zero

Set objTag1= Nothing

Set ExcelObject = Nothing

Set WorkSheet = Nothing

End Sub

------------------------------------------------------------------------------

THANKS FOR THE HELP

try this,

Rwcnt = ExcelObject.ActiveSheet.UsedRange.Rows.Count

another thing, make this last loop like this because if there is an error it will not close the connections with excel

In this piece of your vbs, if Rwcnt = # of rows in the active worksheet (in the active workbook), starting at row 2 means you need to stop at Rwcnt-1, otherwise, the last run through the loop will return a null value. 

rdoliveira

This is also wrong in your code.

It is assigning a tag value to an object.

'Relation between var and smarttags

''Set objTag1 = SmartTags("PruebaUA")

try this.

SmartTags("PruebaUA") = objTag1 ''will load the last value like this

and objTag1 would have to be an array to store more than one value that comes from excel.

if you want an example

ixo65

Hello GusoXXL,

please be so kind and don't post twice for the same problem.

You also make second topic for your problem:

Reading Values with VBscript from Excel

 Its true but idk why there are two post i just posted one 

Now i know, moderator change from one post to other not my foult

Dim arr(99)

'Loop

For i=2 To Rwcnt

arr(i) = WorkSheet.Cells(i,1).Value

SmartTags("PruebaUA") = SmartTags("PruebaUA") & " "& arr(i)

Next

 I try to change the code accordig withyour recomendations. I continue having same problem and i start think is because the first colum that i want to read its only letters and when i stablished the variable intern "PruebaUA" and i put array i cant put array of chars or strings maybe there is the problem?

By the way the variable SmartTags(PruebaUA) i just want it to show in the HMI all the cells from that column, is good configurated?

i send u again the code with the new modifications (change PruebaUA for Visualizar)

----------------------------------------