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!
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