none
Outlook Userform-Code works correctly the first time, not the second time. RRS feed

  • Question

  • I have a UserForm in Outlook 2010, with two textboxes and a command button.

    The code in the command button, opens excel and places the textbox text in the 1st empty row of the sheet.

    Private Sub CommandButton1_Click()
        Dim xlApp As Object, Sht As Object
        Dim LastRow As Long
    
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Application.Visible = True
        xlApp.Workbooks.Open "C:\Rename Folders\Jobs\Jobs.xlsx"
        Set Sht = xlApp.Worksheets(1)
    
        With Sht
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
            .Range("A" & LastRow) = TextBox1
            .Range("B" & LastRow) = TextBox2
        End With
    
        xlApp.ActiveWorkbook.Close SaveChanges:=True   'save excel file
        xlApp.Quit                                  'quit excel
        Set xlApp = Nothing
        Unload Me
    
    End Sub


    The code works well when first run, the second time run it opens the workbook, then the code errors, stopping at the

    LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1

    When I close the workbook, and run the code again, it works fine again.

    So basically it works correctly every other time.

    What needs to be added to the code to ensure the workbook is really closed?

    Thanks,


    Dave



    • Edited by davesexcel Saturday, June 8, 2013 6:07 PM
    Saturday, June 8, 2013 2:12 PM

Answers

  • Hi Dave,

    Thank you for posting in the MSDN Forum.

    Not sure why your code could not work. What if you define a variable for the workbook?

    Dim xlWbk as Object
    ....
    ....
    xlWbk = xlApp.Workbooks.Open "C:\Rename Folders\Jobs\Jobs.xlsx"
    ....
    ....
    xlWbk.Close SaveChanges:=True
    Set xlWbk = Nothing
    xlApp.Quit
    Set xlApp = Nothing

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by davesexcel Wednesday, June 12, 2013 12:29 PM
    Monday, June 10, 2013 11:50 AM
    Moderator
  • Thanks Quist,

    That worked.


    Dave

    • Marked as answer by davesexcel Wednesday, June 12, 2013 12:29 PM
    Wednesday, June 12, 2013 12:29 PM

All replies

  • Hi Dave,

    Thank you for posting in the MSDN Forum.

    Not sure why your code could not work. What if you define a variable for the workbook?

    Dim xlWbk as Object
    ....
    ....
    xlWbk = xlApp.Workbooks.Open "C:\Rename Folders\Jobs\Jobs.xlsx"
    ....
    ....
    xlWbk.Close SaveChanges:=True
    Set xlWbk = Nothing
    xlApp.Quit
    Set xlApp = Nothing

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by davesexcel Wednesday, June 12, 2013 12:29 PM
    Monday, June 10, 2013 11:50 AM
    Moderator
  • Thanks Quist,

    That worked.


    Dave

    • Marked as answer by davesexcel Wednesday, June 12, 2013 12:29 PM
    Wednesday, June 12, 2013 12:29 PM