The link is how the documents looks where I need the help. I'm modifying over 100 documents and a script to do so would be great! I need to add data to the next unpopulated row in the table in the attached document but I can't figure out how to get to the row I need to.Any ideas? *take out the spaces...I can't do links yet :-( * https:// db.tt /VmKY9iQe
Code:sub M_snb()
with documents.open("G:\OF\example.docx")
for j=1 to .tables(1).rows.count
if len(.tables(1).cell(j,1).range)=1 then
.tables(1).cell(j,1).range.text="...."
exit for
end if
next
.close -1
end with
End sub
A few questions:Quote:
Originally Posted by ryanadanders
1. Are all these documents in the same folder?
2. Your attachment has two tables, neither of which has an entirely empty row, so:
a) What constitutes an 'unpopulated row'; and
b) Are all tables to be processed? If not, how does the macro identify the tables to process?
3. Are the update data the same for all tables? If not, how are they updates for each table to be determined?
Sorry for the ambiguity and thanks for the interst in helping!Quote:
Originally Posted by macropod
1. All the documents can be put into the same folder. They're under one folder in sub folders seperated due to the ticket number that has requested them to be changed. There are only 7 folders so I'd just change the ticket numer accordingly.
2a. Unpopulated will have a number in column A and nothing in column B, C, or D. Table is 4 columns by 3 or more rows.
2b. I am using the find feature to go to "7.0 revisions" and then to the table from there.
2c. Only the first table after the text "7.0 revisions" should be processed. It should run once per doc then move on.
3. The data will be the same for all tables under one folder. Row B: 03/25/2014; Row C: 123456; Row D: JA
I get to the table, and my macros do everything I need to EXCEPT find the next available empty cell. I manually click the empty space. This works for now but it'd be neat if I didn't have to click, obviously.
I'm trying to go through the code but I don't understand exactly what it's doing.
I went through and tried to understand the code. Please correct me if I'm wrong. I want to learn this and I put my best guess out there.
If I'm understanding this code correctly, it will only look in the first column. If I change (j,1) to (2,j) would it be looking in row 2 going across? If row 2 does not activate the if, then I'd need it to continue sequentially until it finds a blank.
Sorry If I'm a bother, I'm really trying to figure this out.[/Code:sub M_snb() with documents.open("G:\OF\example.docx") 'does this go into a folder and apply this to all documents in the folder?
for j=1 to .tables(1).rows.count 'starts a loop in the tables. is it counting rows?
if len(.tables(1).cell(j,1).range)=1 then 'len was used to count characters in excel (is it short for length?), does it do the same here? if so, does this line say "If table cell 1,1 is one character long then...end if"? If I change this to 0, would it work with blank cells?
.tables(1).cell(j,1).range.text="...." 'I think this finds the table, identifies the cell 1,1 and puts text in it, assuming there count of the cell was one as above.
exit for 'exits the loop if above is true
end if
next 'this will continue until it finds a cell with one character, correct? (J,1)
.close -1 '.close closes the document? what does the -1 do?
end with
End sub
Try:
The above code will process all doc, docx & docm files in the selected folder, updating the first table (if any) after "7.0 Revisions".Code:Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String
Dim wdDoc As Document, i As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, _
AddToRecentFiles:=False, Visible:=False)
With wdDoc
With .Range.Find
.ClearFormatting
.Text = "7.0 Revisions"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = True
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute
End With
If .Range.Find.Found = True Then .Range.Start = .Duplicate.End
On Error Resume Next
With .Range.Tables(1)
For i = 2 To .Rows.Count
If Len(.Cell(i, 2).Range.Text) = 2 Then
.Cell(i, 2).Range.Text = "03/25/2014"
.Cell(i, 3).Range.Text = "123456"
.Cell(i, 4).Range.Text = "JA"
Exit For
End If
Next
End With
.Close SaveChanges:=True
End With
strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub
'
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function