Reading Excel Cell values using VB script

[GirieshM]

Hi All,

I am currently try to work on a blog where its aim to input the values from excel and based on the values it has to get the values from DB in sap and display it in the Output portion of the excel as mentioned in the following blog http://scn.sap.com/docs/DOC-31015. I have created the FM and done the basic things mentioned in the blog but I am facing an error "Run Tme Error '40036' Application-defined or object-defined error". Please find my code below and help to resolve the issue.

With Regards,

Giriesh M

[Former Member · Accepted solution]

Hi there...

It is Simply not possible for Excel to Parse that Code because There is no Existing for

ThisWorkbook.ActiveSheet.Cells...

Try it via AutoCompletion (or press "F1" in Context help)

If you set the Worksheet  in a Variable like this:

[code]

dim sht as Worksheet

Set sht = "Tabelle1"

[/code]

you can use it like that:;

[code]

If sht.Cells(6,2).Value <> " "

[/code]

greetings

[GirieshM · Accepted solution]

Hi Thomas,

I have changed the code as below:

[Code]

Dim sht as Worsheet

sht = ThisWorkbook.Activesheet

If sht.cells(6,2).Value <> " " then

End If

[/CODE]

This code works for me as expected.

Instead of assigning the ThisWorkbook.Activesheet to sht if I enter it directly as

If ThisWorkbook.Activesheet.cells(6,2).Value <> " " then

End If

It is throwing the above error. What is the reason behind assigning it to the variable and implementing it directly.

With Regards,

Giriesh M

[Former Member · Accepted solution]

Hi Griesh,

Its not assigning to a Variable - its making an Instance you are Assigning to an Object-

So in this Case you make an Instance of the Sheet-

The Biggest difference is you can Handle it easier (as you can see)

and if there are more actions in the Sheet to perform you can also do like this:

Dim sht as Worksheet
Set sht = ThisWorkbook.ActiveSheet
With sht
     if .Cells(6,2).Value <> "" Then
          ...' Do Something
     else

     end if
End with

From Where are you running your code ?
From the VBA Editor per "F5" ?
This cant run---because the VBA Editor has no "ActiveSheet" from where it can Start

Add a Button to your Sheet and assign it to the Makro "GetAdress"

Press the button and than ActiveSheet will run also without Setting to an Instance.

Have a nice day (and a happy new year !)

Thomas Bräutigam

[GirieshM · Accepted solution]

Hi Thomas,

Thank you for the explanation and happy new year to all.

With Regards,

Giriesh M