[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]

Up. Anyone?

 

[dangelor]

With

    '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

try either removing the 'With Statement' or the preceding 'xlWB.Sheets("Sheet1") from the lines sandwiched between the With Statement.

 

[pitaszek]

Hello. Thanks for your reply however it is not the case. You can see this parts are commented.

 

[dangelor]

Does this line actually open the workbook?

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

If so, is there actually a worksheet named "Sheet1" in that open workbook?

 

[pitaszek]

Hello dangelor.

Yes, the Sheet is existing. I still cannot get it working.

Anyone?

Witek

 

[pitaszek]

Is this a right way to do it? I remind I am writing a macro in Outlook.

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("[URL="file://\\networklocation\file.xlsx"]\\networklocation\file.xlsx[/URL]")
last_row = xlWB.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Regards,
Witek

 

[dangelor]

Does this line of code actually open "file.xlsx"?

Set xlWB = xlApp.Workbooks.Open("\\networklocation\file.xlsx")

 

[pitaszek]

Yes. If I set for example last_row = 5 it will put the details I need into 5th row of Sheet1.

 

[dangelor]

Try this...

With xlWB.Sheets("Sheet1")
        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