Adding new values to the closed excel

Question

Tried to add value to closed excel, but I got Run Time Error '91' : Object Variable or With Block not Set.

Code:

Dim xl0 As New Excel.Application

  Dim xlw As New Excel.Workbook

  Dim xlsh As Worksheet



  Set xlsh = Sheet3

  Set xlw = xl0.Workbooks.Open("\\Addition\RCA.xlsx")

  xl0.Worksheets.Add

  xl0.xlsh.Cells(1, 1) = RANGECheck

  xl0.xlsh.Cells(1, 2) = RCAInput

  xlw.save

  xlw.Close

  Set xl0 = Nothing

  Set xlw = Nothing

Also, the above code enters value in first row, what can we do to add the value after the last row having value?

`Set xlsh = Sheet3`... that's in this workbook, not the one you open. — BigBen, Apr 30 '20 at 18:43
Ohh, so I should write that after `Set xlw = xl0.Workbooks.Open("\\Addition\RCA.xlsx")` — paran, Apr 30 '20 at 18:57
I'm guessing you're going for `Set xlsh = xlw.Worksheets("Sheet 3")`? — BigBen, Apr 30 '20 at 18:58
Yes, and if I go that way I should not use `xl0.Worksheets.Add` — paran, Apr 30 '20 at 19:00
No need for the last 2 lines in your code where you set your objects to nothing. (`Set xl0 = Nothing`). A reason why from someone much smarter than me can be found [here](https://stackoverflow.com/questions/51065566/what-are-the-benefits-of-setting-objects-to-nothing) :) — urdearboy, Apr 30 '20 at 19:00
Could you please help me what wrong I did: `Dim ToUser As String ToUser = MailSheet.Columns("A:A").Selection.Find(What:=BreaksSheet.Cells(i, colS).Value, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Offset(0, 1).Value`, @BigBen — paran, May 01 '20 at 17:04
That seems like a completely new question, but drop that `Selection`... (and you'll also have to test if the `Find` succeeded). — BigBen, May 01 '20 at 17:05

Adding new values to the closed excel

0 Answers0