none
Trying to update existing excel file using VBA RRS feed

  • Question

  • I'm trying to update my existing excel file with email from outlook using VBA.

    When I try updating it, it will overwrite the first few lines.

    The question is: How to update the file from the last row and not overwrite my existing data?

    Derrick

    Sub ExportToExcel()
    On Error GoTo ErrHandler
    Dim appExcel As Object
    Dim wkb As Object
    Dim wks As Object
    Dim rng As Object
    Dim strSheet As String
    Dim strPath As String
                                                    
    
                
    Dim intRowCounter As Integer
    Dim intColumnCounter As Integer
    Dim msg As Outlook.MailItem
            
        
    Dim nms As Outlook.NameSpace
    Dim fld As Outlook.MAPIFolder
    Dim itm As Object
    strSheet = "mother.xlsx"
    strPath = "D:\"
    strSheet = strPath & strSheet
    Debug.Print strSheet   'Select export folder
    Set nms = Application.GetNamespace("MAPI")
    Set fld = nms.PickFolder   'Handle potential errors with Select Folder dialog box.
    If fld Is Nothing Then
    MsgBox "There are no mail messages to export", vbOKOnly, _
    "Error"
    Exit Sub
    ElseIf fld.DefaultItemType <> olMailItem Then
    MsgBox "There are no mail messages to export", vbOKOnly, _
    "Error"
    Exit Sub
    ElseIf fld.Items.Count = 0 Then
    MsgBox "There are no mail messages to export", vbOKOnly, _
    "Error"
    Exit Sub
    End If   'Open and activate Excel workbook.
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Open (strSheet)
    Set wkb = appExcel.ActiveWorkbook
    Set wks = wkb.Sheets(1)
    wks.Activate
    appExcel.Application.Visible = True   'Copy field items in mail folder.
    For Each itm In fld.Items
    intColumnCounter = 1
    Set msg = itm
    intRowCounter = intRowCounter + 2
    intColumnCounter = intColumnCounter + 1
    Set rng = wks.Cells(intRowCounter, intColumnCounter)
    rng.Value = msg.Subject
    intColumnCounter = intColumnCounter + 1
    Set rng = wks.Cells(intRowCounter, intColumnCounter)
    rng.Value = msg.SentOn
    intColumnCounter = intColumnCounter + 1
    Set rng = wks.Cells(intRowCounter, intColumnCounter)
    rng.Value = msg.Body
    
    Next itm
    Set appExcel = Nothing
    Set wkb = Nothing
    Set wks = Nothing
    Set rng = Nothing
    Set msg = Nothing
    Set nms = Nothing
    Set fld = Nothing
    Set itm = Nothing
    Exit Sub
    ErrHandler:  If Err.Number = 1004 Then
    MsgBox strSheet & " doesn't exist", vbOKOnly, _
    "Error"
    Else
    MsgBox Err.Number & "; Description: ", vbOKOnly, _
    "Error"
    End If
    Set appExcel = Nothing
    Set wkb = Nothing
    Set wks = Nothing
    Set rng = Nothing
    Set msg = Nothing
    Set nms = Nothing
    Set fld = Nothing
    Set itm = Nothing
    End Sub
    

    Tuesday, July 14, 2015 7:05 AM

Answers

  • HI,

    >>I want to continue from where I left off in the excel file by inserting a new row ?

    Do you mean that you want to start the row from the existing rows ? For example, your Excel file has 10 rows, the export will start from 11, right ?If it is, you may write data from the 11th row, not from the beginning. try to set intRowCounter a value being the total  row number the sheet has used.

    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Open (strSheet)
    Set wkb = appExcel.ActiveWorkbook
    Set wks = wkb.Sheets(1)
    wks.Activate
    appExcel.Application.Visible = True
    
    'add this line
    intRowCounter = wks.UsedRange.Rows.Count
    'Copy field items in mail folder.
    
    For Each itm In fld.Items

    By the way ,if you want to write rows without empty rows,  you may modify this line.

    intRowCounter = intRowCounter + 2

    being

    intRowCounter = intRowCounter + 1

    Hope this could help you.

    Best Rehards,

    Lan

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Derrick319 Thursday, July 16, 2015 2:41 AM
    • Marked as answer by L.HlModerator Monday, July 20, 2015 3:53 PM
    Wednesday, July 15, 2015 9:29 AM
    Moderator

All replies

  • Below the line

    Set wks = wkb.Sheets(1)

    insert the following new line:

    intRowCounter = wks.Range("B:B").End(-4162).Row


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, July 14, 2015 7:54 AM
  • Thanks for the reply Hans Vogelaar

    but it didn't worked, when i run the code it overwrites the first line.

    Tuesday, July 14, 2015 8:32 AM
  • Hello Derrick,

    Set rng = wks.Cells(intRowCounter, intColumnCounter)
    rng.Value = msg.Subject
    


    It looks like you need to check out the range value first and fill only clean cells, i.e. append the table with new mail items.

    For Each itm In fld.Items
    

    Iterating through all items in the folder is not really a good idea. The folder may contains a lot of items, some of them can be exported earlier. I'd recommend using the Find/FindNext or Restrict methods of the Items class to get a subset of items that should be exported and work only with them in the code.

    Tuesday, July 14, 2015 9:26 AM
  • Thanks for the reply Eugene Astafiev, big thanks to you.

    Set rng = wks.Cells(intRowCounter, intColumnCounter)
    rng.Value = msg.Subject

    where do you insert the code?

    Tuesday, July 14, 2015 9:43 AM
  • Hi,

    >> How to update the file from the last row and not overwrite my existing data?

    What do you mean the last row ? I have tested the code you provided, it could export oldest mail being the forst row and  overwrite the existing data, and when you mentioned not overwrite the existing data , do you mean insert a new row ? if it is, you may try to insert a new row with the data. The following code could be referred as an example.

    Set msg = itm
    intRowCounter = intRowCounter + 2
    intColumnCounter = intColumnCounter + 1
    ' insert a new row
    wks.Rows(intRowCounter).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove
    Set rng = wks.Cells(intRowCounter, intColumnCounter)
    rng.Value = msg.Subject
    intColumnCounter = intColumnCounter + 1
    Set rng = wks.Cells(intRowCounter, intColumnCounter)
    rng.Value = msg.SentOn
    intColumnCounter = intColumnCounter + 1
    Set rng = wks.Cells(intRowCounter, intColumnCounter)
    rng.Value = msg.Body

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 15, 2015 3:15 AM
    Moderator
  • Hi Lan thanks for the reply.

    I want to continue from where I left off in the excel file by inserting a new row. I tried running your code but instead of adding new row at the bottom, new row are inserted at the line 1. 

    Wednesday, July 15, 2015 8:20 AM
  • HI,

    >>I want to continue from where I left off in the excel file by inserting a new row ?

    Do you mean that you want to start the row from the existing rows ? For example, your Excel file has 10 rows, the export will start from 11, right ?If it is, you may write data from the 11th row, not from the beginning. try to set intRowCounter a value being the total  row number the sheet has used.

    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Open (strSheet)
    Set wkb = appExcel.ActiveWorkbook
    Set wks = wkb.Sheets(1)
    wks.Activate
    appExcel.Application.Visible = True
    
    'add this line
    intRowCounter = wks.UsedRange.Rows.Count
    'Copy field items in mail folder.
    
    For Each itm In fld.Items

    By the way ,if you want to write rows without empty rows,  you may modify this line.

    intRowCounter = intRowCounter + 2

    being

    intRowCounter = intRowCounter + 1

    Hope this could help you.

    Best Rehards,

    Lan

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Derrick319 Thursday, July 16, 2015 2:41 AM
    • Marked as answer by L.HlModerator Monday, July 20, 2015 3:53 PM
    Wednesday, July 15, 2015 9:29 AM
    Moderator
  • Thanks so much Lan, your code works.

    However, i have some other question. How do I set up a condition using the email subject so that the email will only extract emails with the same subject.

    However the subject varies, the bold part is static while the not bold part is dynamic. 

    Example of email subject: Request For Information REF: CR15-06000

    For example scenario: I have 5 email in my inbox, 3 of them have the subject Request For Information REF: CR15-06021, Request For Information REF: CR15-06343 and Request For Information REF: CR15-060456 while the other two have other subject.

    Thursday, July 16, 2015 2:41 AM
  • Hi,

    You may create a condition to get these emails by Subject property.

    By the way , since your orginal issue has been solved, for the other question, I will suggest you open a new thread to post it, which will be better for other community members to be involved and help you on the new question.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 16, 2015 5:28 AM
    Moderator