Question Re: Reading Excel Cell values using VB script in Additional Q&A

Reading Excel Cell values using VB script

GirieshM — Fri, 20 Dec 2013 09:55:05 GMT

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

Re: Reading Excel Cell values using VB script

michael_hobbins — Fri, 20 Dec 2013 13:10:24 GMT

Hello Giriesh,

it seems you should change Cells(6, "B") for Cells(6, 2) on the line where the error is.

Try changing this.

Cheers,

Michael

Re: Reading Excel Cell values using VB script

GirieshM — Mon, 23 Dec 2013 06:10:39 GMT

Hi Michael,

Thank you for the prompt reply. Eventhough I change the entries as you mentioned as (6, 2) it is throwing the same error. Please see the below Screen Shot:

With Regards,

Giriesh M

Re: Reading Excel Cell values using VB script

michael_hobbins — Mon, 23 Dec 2013 11:50:43 GMT

Hello Giriesh, I tried to reproduce your error on that line and I couldn't. I misspelt ActiveSheet to see if I could force the error. What I got was the misspelt word hightlighted in blue, indicating there's something wrong there. On your printscreen there's no blue highlight. The red highlight you show is generated by adding an interruption point/breakpoint. Can you add one about 15 lines before the one you have now, run the sub/funtion and using F8 key advance line by line until you get to the line with the problem?

Michael

Re: Reading Excel Cell values using VB script

michael_hobbins — Mon, 23 Dec 2013 11:53:48 GMT

and pls delete the existing breakpoint (just incase the red highlight is overlapping the blue highlight)

Re: Reading Excel Cell values using VB script

GirieshM — Mon, 23 Dec 2013 14:17:35 GMT

Hi Michael,

As you mentioned the blue highlight is the compile error. But In my case I spelt it correct. Tried to debug it but getting the error only at the line "If ThisWorkbook.ActiveSheet.Cells(6, 2).Value <> " " Then".. Unable to proceed further. Please find the below image:

With Regards,

Giriesh M

Re: Reading Excel Cell values using VB script

michael_hobbins — Mon, 23 Dec 2013 14:37:19 GMT

When you get the error, can you click debug and see on what line it stops? You should a yellow highligthed line

Re: Reading Excel Cell values using VB script

GirieshM — Tue, 24 Dec 2013 11:59:15 GMT

Hi Michael,

Advance Christmas Wishes. Yup I checked by debugging and found the error in this line "If ThisWorkbook.ActiveSheet.Cells(6, 2).Value <> " " Then".

Please find the below screenshot:

With Regards,

Giriesh M

Re: Reading Excel Cell values using VB script

Former Member — Tue, 24 Dec 2013 12:58:05 GMT

It seems that there is a space between quotation marks. Case you want to ask for cell with no value replace the space.

Regards Michael

Re: Reading Excel Cell values using VB script

GirieshM — Tue, 24 Dec 2013 13:11:51 GMT

Hi Michael Friedrich,

I tried by removing the space within the quotes as If ThisWorkbook.ActiveSheet.Cells(6, 2).Value <> "" Then, eventhough it is showing the same error.

With Regards,

Giriesh M

Re: Reading Excel Cell values using VB script

Former Member — Tue, 24 Dec 2013 18:26:00 GMT

Dear Giriesh,

This is a long shot but try putting the following before the problematic line:

ThisWorkbook.Sheet1.Activate

Or one by one i.e.

ThisWorkbook.Activate

Sheet1.Activate

This way we can see where exactly the error come from the Excel object hierarchy.

You can also try assigning the cell to a range object i.e:

DIm rng As Range

Set rng = ThisWorkbook.ActiveSheet.Cells(6,2)

If rng.Value <> "" Then etc.

Let see what happen..

Thanks

Sayuti

p/s: Your username and password are visible in one of your screenshots. You may want to consider masking it next time..

Re: Reading Excel Cell values using VB script

holger_khn — Tue, 24 Dec 2013 22:23:05 GMT

Hello.

As far as I see you have your code in Sheet1.

I suggest to move this code into Module1. Then I guess you will not get any runtime error.

Re: Reading Excel Cell values using VB script

Former Member — Thu, 26 Dec 2013 05:58:53 GMT

Dear Holger,

From the 1st screenshot, it seems that the codes are already in Module1. Am referring to the VBE Titlebar and Watches Pane.

Thanks,

Sayuti

Re: Reading Excel Cell values using VB script

GirieshM — Fri, 27 Dec 2013 06:54:32 GMT

Hi Sayuti,

I assigned a variable RNG As range and assigned the workbook sheet value to it before to the check which done previously. Now the same error is throwed in the newly added line.

Note: Will mask the user id and pwd next time.

With Regards,

Giriesh M

Re: Reading Excel Cell values using VB script

Former Member — Fri, 27 Dec 2013 10:04:41 GMT

Dear Giriesh,

Is there any variables defined with reserve names e.g. Dim Application As Object or Dim Worksheet As Object.or something like that in your code?

Perhaps you can change ActiveSheet to the specific sheet e.g. Sheet1 and see what happens.

When I googled the error msg and number, most of the results suggest that there is a glitch in Excel itself or a corrupted database and recommended a re-installs.

Have you test your codes in a different PC?

Thanks,

Sayuti

Re: Reading Excel Cell values using VB script

Former Member — Fri, 27 Dec 2013 13:37:56 GMT

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

Re: Reading Excel Cell values using VB script

GirieshM — Mon, 30 Dec 2013 11:00:56 GMT

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

Re: Reading Excel Cell values using VB script

Former Member — Mon, 30 Dec 2013 13:49:56 GMT

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

Re: Reading Excel Cell values using VB script

GirieshM — Tue, 31 Dec 2013 07:11:03 GMT

Hi Thomas,

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

With Regards,

Giriesh M