[VBA] Outlook form to open Excel file, find last unused row and fill it with data

[pitaszek]

Hi Guys,

I've got an Outlook form that opens Excel and puts details of selected email there. I need to figure out how to find last unused row in this Excel file.

Normally the procedure goes more or less as show below but this work as long as we are within Excel application:

Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For Outoolok <-> Excel relation it returns 'Object Required' error. The code goes as follows:

Private Sub CommandButton1_Click()
Dim xlApp As Object
Dim xlWB As Object
Dim Submitter As String
Dim From As String
Dim DateStamp As Date
Dim TaskName As String
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("[URL="file://\\networkdrive\something\ekselek1.xlsx"]\\networkdrive\something\ekselek1.xlsx[/URL]")
    Submitter = SubmitterBox.Value
    From = FromBox.Value
    DateStamp = DateStampBox.Value
    TaskName = TaskNameBox.Value
    
    Select Case True
        Case OptionButton1
            Region = "Americas"
        Case OptionButton2
            Region = "Europe"
        Case OptionButton3
            Region = "Asia"
    End Select
    
       Select Case True
        Case OptionButton4
            MainCat = "Data_Request_New_Report"
        Case OptionButton5
            MainCat = "Data_Request_ExistingReport"
        Case OptionButton6
            MainCat = "Changes_to_report"
        Case OptionButton7
            MainCat = "Manual_upload"
        Case OptionButton8
            MainCat = "Investigation"
        Case OptionButton9
            MainCat = "Static_maintenance"
    End Select
    
    'With xlWB.Sheets("Sheet1")
        NextRow = xlWB.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row '<------------------ DOES NOT WORK - returns 'object required'
        xlWB.Sheets("Sheet1").Range("A" & NextRow).Value = Submitter
        xlWB.Sheets("Sheet1").Range("B" & NextRow).Value = From
        xlWB.Sheets("Sheet1").Range("C" & NextRow).Value = DateStamp
        xlWB.Sheets("Sheet1").Range("D" & NextRow).Value = TaskName
    'End With
    
    Unload Me
    xlWB.Close SaveChanges:=True

End Sub

Cheers,
Witek

 

[pitaszek]

No success. I just don't get it.

 

[dangelor]

Private Sub CommandButton1_Click()
    Dim xlApp            As Object
    Dim xlWB             As Object
    Dim Submitter        As String
    Dim From             As String
    Dim DateStamp        As Date
    Dim TaskName         As String

    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("\\networkdrive\something\ekselek1.xlsx")
    
    Submitter = SubmitterBox.Value
    From = FromBox.Value
    DateStamp = DateStampBox.Value
    TaskName = TaskNameBox.Value

    Select Case True
        Case OptionButton1
            Region = "Americas"
        Case OptionButton2
            Region = "Europe"
        Case OptionButton3
            Region = "Asia"
    End Select

    Select Case True
        Case OptionButton4
            MainCat = "Data_Request_New_Report"
        Case OptionButton5
            MainCat = "Data_Request_ExistingReport"
        Case OptionButton6
            MainCat = "Changes_to_report"
        Case OptionButton7
            MainCat = "Manual_upload"
        Case OptionButton8
            MainCat = "Investigation"
        Case OptionButton9
            MainCat = "Static_maintenance"
    End Select

    With xlWB.Sheets("Sheet1")
        .Activate
        NextRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A" & NextRow).Value = Submitter
        .Range("B" & NextRow).Value = From
        .Range("C" & NextRow).Value = DateStamp
        .Range("D" & NextRow).Value = TaskName
    End With

    Unload Me
    xlWB.Close SaveChanges:=True

End Sub

 

[pitaszek]

I can see you have added .Activate part to the code. This step is performed, the file is also opened however it opens invisible. How do I make it visible? Maybe I will be able to figure out what is wrong looking at it.

Cheers,
Witek

 

[dangelor]

Kinda hard to look at a closed workbook (the last line of your code closes the workbook).

 

[pitaszek]

Kinda hard to look at a closed workbook (the last line of your code closes the workbook).

It doesn't reach this part of the code. It crashes on NextRow so stays open.

 

[dangelor]

Let's try this...

Private Sub CommandButton1_Click()
    Dim xlApp            As Excel.Application
    Dim xlWB             As Workbook
    Dim xlWS             As Worksheet
    Dim Submitter        As String
    Dim From             As String
    Dim DateStamp        As Date
    Dim TaskName         As String

    Set xlApp = New Excel.Application
    xlApp.Visible = True

    Set xlWB = xlApp.Workbooks.Open("\\networkdrive\something\ekselek1.xlsx")
    Set xlWS = xlWB.Worksheets("Sheet1")

    Submitter = SubmitterBox.Value
    From = FromBox.Value
    DateStamp = DateStampBox.Value
    TaskName = TaskNameBox.Value

    Select Case True
        Case OptionButton1
            Region = "Americas"
        Case OptionButton2
            Region = "Europe"
        Case OptionButton3
            Region = "Asia"
    End Select

    Select Case True
        Case OptionButton4
            MainCat = "Data_Request_New_Report"
        Case OptionButton5
            MainCat = "Data_Request_ExistingReport"
        Case OptionButton6
            MainCat = "Changes_to_report"
        Case OptionButton7
            MainCat = "Manual_upload"
        Case OptionButton8
            MainCat = "Investigation"
        Case OptionButton9
            MainCat = "Static_maintenance"
    End Select

    With xlWS
        .Activate
        NextRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A" & NextRow).Value = Submitter
        .Range("B" & NextRow).Value = From
        .Range("C" & NextRow).Value = DateStamp
        .Range("D" & NextRow).Value = TaskName
    End With

    Unload Me
    xlWB.Close SaveChanges:=True

End Sub

 

[pitaszek]

It works dangelor. Thanks for your time and engagement. Besides using your latest code I also added MS Excel 14.0 Object Library which I hadn't before.

 

[dangelor]

I learned something new in the process. Glad to help.