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!
Expand|Select|Wrap|Line Numbers
- 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