Need code help to update activesheet in .xls

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Yoni Hasid
    New Member
    • Feb 2011
    • 30

    Need code help to update activesheet in .xls

    Hello,
    I am very new to VBA and need help !
    I have VB macro in Excel that grabs the data from a Word document and imports it into Excel worksheet.
    The code in macro has expression that clears the active worksheet and places the new records. However, I need only to update active sheet with new records, or add new records. So, trying to figure out how to accomplish it within the existing code.
    Your help is appreciated!

    Code:
    Sub getWordFormData()
    Dim wdApp As New Word.Application
    Dim myDoc As Word.Document
    Dim CCtl As Word.ContentControl
    Dim myFolder As String, strFile As String
    Dim myWkSht As Worksheet, i As Long, j As Long
    
    myFolder = "C:\Users\username\Documents\Retention DB\Interviews"
    Application.ScreenUpdating = False
    
    If myFolder = "" Then Exit Sub
    Set myWkSht = ActiveSheet
    ActiveSheet.Cells.Clear
    
    Range("A1") = "Company Name"
    Range("A1").Font.Bold = True
    Range("B1") = "Type of Company"
    
    i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
    strFile = Dir(myFolder & "\*.docx", vbNormal)
    
    While strFile <> ""
    i = i + 1
    
    Set myDoc = wdApp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    
    With myDoc
    j = 0
    For Each CCtl In .ContentControls
    j = j + 1
    myWkSht.Cells(i, j) = CCtl.Range.Text
    Next
    myWkSht.Columns.ColumnWidth = 25
    End With
    myDoc.Close SaveChanges:=False
    strFile = Dir()
    Wend
    wdApp.Quit
    Set myDoc = Nothing: Set wdApp = Nothing: Set myWkSht = Nothing
    Application.ScreenUpdating = True
    
    End Sub
Working...