Open Excel spreadsheet and copy the Data into VBS

Question

Hello, I have a simple spreadsheet (3 cols and 2 Rows) and am trying to create a script to open it, copy your data and insert into another.

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 executed, the spreadsheet opens and the following error is returned: Objeto necessário: 'Selection', and the error occurs on line 7. Someone could give me a light?

Answer 1

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 is unable to identify properly which is its "Selection" however much Voce has defined above. I don’t know how to detail this problem, but the solution is to avoid using Excel interaction commands like "SELECTION" and use the range directly, as mentioned in the above code.