cancel
Showing results for 
Search instead for 
Did you mean: 

Reading Excel Cell values using VB script

GirieshM
Active Contributor
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

thomas_brutigam2
Active Participant
0 Kudos

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
Active Contributor
0 Kudos

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

thomas_brutigam2
Active Participant
0 Kudos

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
Active Contributor
0 Kudos

Hi Thomas,

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

With Regards,

Giriesh M

Answers (2)

Answers (2)

holger_khn
Contributor
0 Kudos

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.

former_member213011
Participant
0 Kudos

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

michael_hobbins
Active Participant
0 Kudos

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

GirieshM
Active Contributor
0 Kudos

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

michael_hobbins
Active Participant
0 Kudos

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

michael_hobbins
Active Participant
0 Kudos

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

GirieshM
Active Contributor
0 Kudos

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

michael_hobbins
Active Participant
0 Kudos

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

GirieshM
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

GirieshM
Active Contributor
0 Kudos


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

former_member213011
Participant
0 Kudos

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..

GirieshM
Active Contributor
0 Kudos

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

former_member213011
Participant
0 Kudos

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