VBS eqivalent of a working vba code

Question

I'm new to VB scripting though I have a little familiarity in VBA.

The VBA code which I have written is working fine but when I tried to convert it into VBS file it throws Run time error "Expected End of Statement- 800A0401" in the following line

wb.Worksheets(1).Cells(1, f2) = get_split_strings(0) & "pass %"

get_split_strings(0) is an array and what I want to do is append the string "pass %" to it.What I am actually doing is opening a HTML file as a excel file for processing and saving it back as a HTML file.

Set wb1 = CreateObject("Excel.Application")
Set wb = wb1.Workbooks.Open("file:///Pathname/filename.html")

If (f1 + 1) = lastrow And (f2) < lastcolumn Then
wb.Worksheets(1).Cells(f1 + 1, f2) = Round((Per_Sum / (lastrow - 2)), 0)
wb.Worksheets(1).Cells(1, f2) = get_split_strings(0) & "pass %" **Getting Error here**
f2 = f2 + 3
f1 = 1
Per_Sum = 0
End If

Added as per comment

        get_split_strings(0) = Split(wb.Worksheets(1).Cells(1, f2), "_", -1, vbBinaryCompare)

get_split_strings(0) contains the header contents. Basically a string data. In the above line the value of f2 is incremented to parse through the columns to find the header contents.

It worked with a small work around.Removed the usage of get_split_string(0).

wb.Worksheets(1).Cells(1,f2)= wb.Worksheets(1).Cells(1,f2) & " pass %"

Have you created excel.application objectin your VBS script? — Pankaj Jaju
– Pankaj Jaju, Commented Jun 30, 2014 at 13:06
Yes I have did that. I have edited the question for your reference. Thanks. — Code_Kid
– Code_Kid, Commented Jun 30, 2014 at 13:25
We cannot reproduce the issue with the information you gave. Please provide a minimal, complete, and verifiable example. What is the value of get_split_strings(0)? — Ansgar Wiechers
– Ansgar Wiechers, Commented Jul 1, 2014 at 7:59

Ansgar Wiechers · Accepted Answer · 2014-07-01 16:10:50Z

1

Split() produces an array, which you then assign to get_split_strings(0) (the first element of the array get_split_strings). Why are you doing that, BTW? What are the other elements of the array get_split_strings?

Anyway, you can't concatenate an array and a string, so the operation get_split_strings(0) & "pass %" fails.

It's not quite clear to me what you're trying to do here. Do you want to concatenate the first element of the array to the string "pass %"? In that case you should change

get_split_strings(0) = Split(...)

to

get_split_strings = Split(...)

Or do you want to concatenate all elements of the array with the string "pass %"? Then you should Join the array before the concatenation:

... = Join(get_split_strings(0)) & "pass %"

answered Jul 1, 2014 at 16:10

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Code_Kid Over a year ago

Sorry for the late reply.Was out of office for some days. As you mentioned I wanted to append the string 'pass %' to another string content. (get_split_strings(0) contains a string). It worked for me in vba. But in vbs it doesn't. Though i made it up by using "wb.Worksheets(1).Cells(1, f2)" in place of get_split_strings(0). (See the question section). Accepting your answer as it contains clear explanation.

Community · Accepted Answer · 2017-05-23 12:05:43Z

0

The "Expected End of Statement- 800A0401" error is a compilation error. It's easily provoked by messing quotes. For example

wb.Worksheets(1).Cells(1, f2) = get_split_strings(0) & ""pass %"

won't compile. Your cited line, however, is syntactically correct.

Because line counting is error prone, publish a few more lines of your code.

Sorry to say, the longer code you published (Thanks!) is still syntactically correct. If the error line is reliably identified, the quotes are the first suspects. Depending on your editor, mixing 'normal' and fancy quotes may go undetected. Does your script compile, if you (temporarily) change

wb.Worksheets(1).Cells(1, f2) = get_split_strings(0) & "pass %"

to

wb.Worksheets(1).Cells(1, f2) = get_split_strings(0) '  & ""pass %" disabled!

Update wrt comment:

As the nasty line 'works' without the & "pass %" tail, re-type it with special care/attention to the quotes.

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Jun 30, 2014 at 13:16

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

2 Comments

Code_Kid Over a year ago

I have edited the question for your reference. Thanks.

Code_Kid Over a year ago

Its really strange! When I re-typed it it shows 'Type Mismatch error 800A00D' in the same line but at a column position where nothing has been typed. i.e at line 111, column 18 where there is nothing. And no problems with the previous line(110) also.

Collectives™ on Stack Overflow

VBS eqivalent of a working vba code

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related