Open Excel worksheet and copy data into VBS

Question

Open Excel worksheet and copy data into VBS

Navigation

#1 by (1 votes)

0

Hello, I have a simple spreadsheet (3 cols and 2 rows) and I'm trying to create a script to open it, copy its data and insert it into another one.

Path = "C:\Users\user_name\Documents\excell\planilha1.xlsx"
Set objexl = CreateObject("Excel.application")
objexl.Visible = True
Set objwkb = objexl.Workbooks.Open(Path)
Set objsht = objwkb.Sheets(1)
objsht.Range("A2").Select
objsht.Range(Selection.End(xlToRight), Selection.End(xlDown)).Select
Selection.Copy

When I run, the worksheet opens and the following error is returned: Objeto necessário: 'Selection' , and the error occurs on line 7. Could someone give me a light?

excel vba vbs excel-vba vbscript

asked by anonymous 29.11.2018 / 18:17

1 answer

Return from a simple javascript function not working Variable error stmt of type Statement

score 1 · Accepted Answer

Try this:

Path = "C:\Users\user_name\Documents\excell\planilha1.xlsx"
Set objexl = CreateObject("Excel.application")
objexl.Visible = True
Set objwkb = objexl.Workbooks.Open(Path)
Set objsht = objwkb.Sheets(1)
set vRange = objsht.Range("A2")
objsht.Range(vRange.End(xlToRight), vRange.End(xlDown)).Copy

Normally this error occurs because Excel can not properly identify what your "selection" is for more than you have defined above. I do not know how to detail this problem, but the solution is to avoid using Excel interaction commands like "SELECTION" and use the range directly, as I mentioned in the code above.