View Single Post
 
Old 10-28-2013, 10:49 AM
dbs179 dbs179 is offline Windows XP Office 2010 32bit
Novice
 
Join Date: Oct 2013
Posts: 10
dbs179 is on a distinguished road
Default

Fumei and Charles,

After discussing this with both of you, I decided to dig a bit into VBA, bookmarks and a few other things. I actually have code working that will pull my responses from Document B, based on answers in Document A and place them automatically in Document C. I do have a few questions though.

1. How can I auto-increment what bookmark location I need to place the next set of "findings". For example, question 1 will always go at bookmark F1, but Question 2 could go at either bookmark F2 or bookmark F1, if no response is needed for question 1. Is this as simple as having a variable that I increment each time I find a true statement? And if so, I've included BKMK as a variable in my code, but don't know how to increment or use that variable to indicate the correct bookmark position.

2. All of my coding is reliant on file location, which right now is on my desktop, however in the future, it may or may not be, and I may even have other users running this code. What is an easy and effective way to handle that? An option I have used in the past with other coding projects I have undertaken is placing all of the working documents in the same folder and then being able to use the directory of that folder once a file is opened. For example, I've used AutoIT in the past to automate a different process. Once the .exe I created was launched it was able to determine the path the .exe was located at and used that as a variable.

3. All of my "findings" or responses need to be numbered. Right now in document c I have the numbering replaced with a "*" and formatted in red. When the script runs to bring over the response, the formatting is removed. I would like to be able to auto-number these as well.

Here is what I have come up with so far and sorry if there is a code box on the forum to put this in, I didn't see one if there is. Also, if this should be moved to the VBA section, could a Mod please move it and please accept my apologies, as I didn't even know VBA could do what I needed so I had no idea where to create this thread other than the general word section.

Thanks again for your help so far, just discussing it and picking your brains has helped tons.

Dave



Sub Auto_Populate()

'_________________________________________________ _______________________
'declares variables

Dim doc_a As Document
Dim doc_b As Document
Dim doc_c As Document
Set doc_a = Documents.Open(FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document A.docx")
Set doc_b = Documents.Open(FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx")
Set doc_c = Documents.Open(FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document C.docx")
Dim Q1NO As Boolean
Dim Q2NO As Boolean
Dim Q3NO As Boolean
Dim BKMK As Integer
BKMK = 1
'end of variable delcaration
'_________________________________________________ _________________________


'_________________________________________________ _________________________
'opens Document A

Documents.Open FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document A.docx", ReadOnly:=True

'_________________________________________________ _________________________
'sets variables based on Document A checkboxes

Q1NO = ActiveDocument.FormFields("Check2").CheckBox.Value
Q2NO = ActiveDocument.FormFields("Check4").CheckBox.Value
Q3NO = ActiveDocument.FormFields("Check6").CheckBox.Value

'end of setting variables based on Document A checkboxes
'_________________________________________________ _________________________



'_________________________________________________ _________________________
'sets variable based on Document A checkboxes

If Q1NO = True Then
'Opens Document B
Documents.Open FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx", ReadOnly:=True
'Activates Document C
doc_c.Activate
'Inserts finding at the first bookmark
ActiveDocument.Bookmarks("F1").Range.Text = Documents("C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx").Bookmarks("F1").Range.Text
End If

If Q2NO = True Then
'Opens Document B
Documents.Open FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx", ReadOnly:=True
'Activates Document C
doc_c.Activate
'Inserts finding at the first bookmark
ActiveDocument.Bookmarks("F2").Range.Text = Documents("C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx").Bookmarks("F2").Range.Text
End If

If Q3NO = True Then
'Opens Document B
Documents.Open FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx", ReadOnly:=True
'Activates Document C
doc_c.Activate
'Inserts finding at the first bookmark
ActiveDocument.Bookmarks("F3").Range.Text = Documents("C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx").Bookmarks("F3").Range.Text
End If

End Sub
Reply With Quote