Tjyylli

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. Currently, 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.

Here is the macro:

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:UserszsirotiloDocumentsRetention DBInterviews"

Application.ScreenUpdating = False



If myFolder = "" Then Exit Sub

Set myWkSht = ActiveSheet

ActiveSheet.Cells.Clear



Range("A1") = "Company Name"

Range("A1").Font.Bold = True

Range("C1") = "Date of Interview"

Range("C1").Font.Bold = True

Range("D1") = "Type of Company by Number(see Case Notes)"

Range("D1").Font.Bold = True



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

It should work by removing the line ActiveSheet.Cells.Clear, because this is the line that removes the content of the cells.

The line i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row is used to know the last row with content to write the new content from then. So it will work as long as the last row has content on the first column.

To avoid to read the same files over and over, it's needed to move the already exported files to another folder. My idea is this:

1. Create a folder, for example:C:UserszsirotiloDocumentsRetention DBExported.




2. Add the line exportedFolder = "C:UserszsirotiloDocumentsRetention DBExported" after the line that defines the variable myFolder.



3. 
   

   After the line myDoc.Close SaveChanges:=False add the following lines:

   
   
   

       FileCopy myDoc, exportedFolder & "" & strFile 'copy word file to Exported folder
   
       Kill myDoc 'deletes the word file
   
   

   
   

Make a backup of the Word files on C:UserszsirotiloDocumentsRetention DBInterviews before testing because it will delete the files.