davesexcel
Well-known Member
- Joined
- Feb 26, 2006
- Messages
- 1,530
I am using this code from an Outlook Userform.
It opens Excel workbook and places the textbox contents into the first empty cell, saves and closes the workbook.
Private Sub CommandButton1_Click()
Dim xlApp As Object, Sht As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True
xlApp.Workbooks.Open "C:\DownLoads\Cover Pages Test.xls"
Set Sht = xlApp.Worksheets(1)
Sht.Activate
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Sht.Range("A" & LastRow) = TextBox1
Sht.Range("B" & LastRow) = TextBox2
xlApp.ActiveWorkbook.Close SaveChanges:=True 'save excel file
xlApp.Quit 'quit excel
Set xlApp = Nothing
Unload Me
End Sub
The code runs fine, but when it is run a second time it opens the workbook then stops because it has errored at:
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
When in VBA editor I press reset, and then the code works fine again. After spending several minutes trying to figure out why it only works every other attempt, it dawned on me that the reason it works the next time is because when I am in debugger, I press reset.
So now when I run the code the 1st time, I reset it in VBA editor and it runs again with no problems.
What can I add to my code so it resets ?
It opens Excel workbook and places the textbox contents into the first empty cell, saves and closes the workbook.
Private Sub CommandButton1_Click()
Dim xlApp As Object, Sht As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True
xlApp.Workbooks.Open "C:\DownLoads\Cover Pages Test.xls"
Set Sht = xlApp.Worksheets(1)
Sht.Activate
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Sht.Range("A" & LastRow) = TextBox1
Sht.Range("B" & LastRow) = TextBox2
xlApp.ActiveWorkbook.Close SaveChanges:=True 'save excel file
xlApp.Quit 'quit excel
Set xlApp = Nothing
Unload Me
End Sub
The code runs fine, but when it is run a second time it opens the workbook then stops because it has errored at:
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
When in VBA editor I press reset, and then the code works fine again. After spending several minutes trying to figure out why it only works every other attempt, it dawned on me that the reason it works the next time is because when I am in debugger, I press reset.
So now when I run the code the 1st time, I reset it in VBA editor and it runs again with no problems.
What can I add to my code so it resets ?
Last edited: