Creating Named Ranges in Excel using Vb Script

Question

I created a code for creating Named Ranges in Excel using Range object but didn't work as it works in VBA. Error comes in the statement where I try to create a Range object, not sure how this could be done.

If anyone could suggest me an idea it would great.

Set Exobj = CreateObject("Excel.Application")
Set Newbook = Exobj.Workbooks.Add()
Newbook.SaveAs("C:\Users\ACER\Desktop\Project Folder\Test17.xlsx")

Exobj.Workbooks.Open("C:\Users\ACER\Desktop\Project Folder\Test17.xlsx")
Exobj.Visible = True

Set Myrange = Exobj.Worksheets(sheets1).Range("A1:H11") ' statement where Error comes

For each C in Myrange
If c.Value = "" Then 
C.Value ="Blank"
End if
Next

Exobj.Workbooks.Save()
Exobj.Activeworkbooks.Close()

I think you need to surround sheets1 with quotes, i.e. Set Myrange = Exobj.Worksheets("sheets1").Range("A1:H11") — Panayot Karabakalov
– Panayot Karabakalov, Commented Feb 26, 2013 at 0:43
:) similar problem i faced when i used Next C it did not work, but when i made it Next only it worked — shareef
– shareef, Commented Apr 15, 2016 at 13:16

Panayot Karabakalov · Accepted Answer · 2013-02-26 17:07:25Z

1

I think you need to surround sheets1 with quotes, i.e.

Set Myrange = Exobj.Worksheets("sheets1").Range("A1:H11")

answered Feb 26, 2013 at 17:07

Panayot Karabakalov

3,1993 gold badges22 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Creating Named Ranges in Excel using Vb Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related