Tuesday, October 21, 2014

Need macro to transfer Word data into Excel

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:\Users\zsirotilo\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("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

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...