Solved: VBA - from open doc 1, open doc 2, close doc 1

[tca_VB]

I will be opening and editing numerous word documents. To ease the user in following an 'order' to the documents, I would like to have a command button at the end that saves document 1, opens document 2, and closes document 1.

They will then continue on document 2 and follow a similar path until the final doc (another story).

Below is the vba I have so far. I can open doc 2, but in closing doc 1 I get a File In Use pop up that says doc 1 is locked for editing by "owner"
Do you want to: Open a Read Only copy, Create a local copy and merge your changes later, Receive notification when the original copy is available.

I'm guessing the error lies in my close statement.
Thanks for the help.
' FileSave
' Saves the active document or template

Dim UserSaveDialog As Dialog
Set UserSaveDialog = Dialogs(wdDialogFileSaveAs)
With UserSaveDialog
.Name = saveString
UserSaveDialog.Execute
End With
'Open next document
npathString = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("B3")
ndotString = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("D3")
nopenString = npathString & ndotString
'Open Word Instance
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True

'Open Template as Document
wdApp.Documents.Add Template:=(nopenString)
wdApp.Activate
'Close document wdApp.Documents.Open(saveString).Close
wdApp.Documents.Open(saveString).Close

End Sub

[fumei]

Certainly the line:

wdApp.Documents.Open(saveString).Close

is wrong. You have an Open and Close in the same instruction.

Make an Document object of the current document, and then save and close it.

You ignored the comment I made in your previous post about creating the Word instance each time. I will repeat...this is a BAD idea.

I will also say it again, using the Dialog seems - at least as far as I can tell - not a good idea either. Why are you doing that?

I have no idea where you are getting saveString. Your code does not show this. In any case, if you want to save the current active document with a name, then (assuming it IS the Document object wdDoc, AND "saveString" is a properly qualified path and name...)

With wdDoc
.SaveAs Filename:=saveString
.Close
End With
' destroy current Document object
Set wdDoc = Nothing

' the above means you can use wdDoc again
' Set as a new document



As I mentioned previously, if you are indeed going through multiple documents you can make each of them a Document object (wdDoc - Set...used...Closed...Destroyed...Set again). AND you can do this with ONE instance of Word (wdApp). You do not need to make a new instance of Word each time. In fact, again, it is a BAD idea to do that.

[tca_VB]

fumei,
Sorry, I didn't intentionally ignore your previous post. I've been trying to piece together many moving parts.

I tried to change to the recommended save/close code, but it give me an error on the saveas line. I commented out my savedialog method while I try to change over. I will post my entire code (hope not to confuse). There is alot of back and forth to excel collecting data for form fields and writing data back to excel for use in future documents.

The trick is to close document 1, open document 2and prefill doc 2 from excel before the user does anything on doc 2.

Any suggestions on what I'm missing is greatly appreciated.

Private Sub cmdCloseSave_Click()
Dim fldCount As Integer
Dim missString As String
Dim dataCount As Integer
Dim xlApp As Object
Dim wdApp As Object
Dim pathString As String
Dim docString As String
Dim clinameString As String
Dim catnameString As String
Dim saveString As String
Dim g As Integer
Dim npathString As String
Dim ndotString As String
Dim nopenString As String
'Set document row number for document after PIN Approval
g = 3

'Determine if any form fields are empty
missString = ""
For fldCount = 1 To ActiveDocument.FormFields.Count
If Len(ActiveDocument.FormFields(fldCount).Result) = 0 Then
missString = missString & " " & ActiveDocument.FormFields(fldCount).Name & ","
End If
Next fldCount
'If empty, provide message and end
If Len(missString) > 0 Then
missString = Left(missString, Len(missString) - 1)
MsgBox ("You must Complete All Fields. Return to " & missString & ". Then click again.")
Exit Sub
End If
'Add Data to Pin_Index
Set wdApp = GetObject(, "Word.Application")
tempName = ActiveDocument.Name

For dataCount = 1 To ActiveDocument.FormFields.Count
wdResult = ActiveDocument.FormFields(dataCount).Result
Set xlApp = GetObject(, "Excel.Application")
xlApp.Visible = True
xlApp.Workbooks("MasterIndex.xls").Activate
xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index").Cells(2, dataCount).Value = wdResult
Next dataCount
'Add Document value to Excel
Set xlApp = GetObject(, "Excel.Application")
xlApp.Visible = True
xlApp.Workbooks("MasterIndex.xls").Activate
xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index").Cells(2, 7).Value = g
'Create File Save path and name
pathString = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("C2")
docString = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("E2")
clinameString = xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index").Range("B2")
catnameString = xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index").Range("F2")
saveString = pathString & catnameString & "\" & clinameString & "_" & docString

' FileSave
' Saves the active document or template

'Dim UserSaveDialog As Dialog
'Set UserSaveDialog = Dialogs(wdDialogFileSaveAs)
'With UserSaveDialog
' .Name = saveString
' UserSaveDialog.Execute
'End With
With wdApp
.SaveAs FileName:=saveString
.Close
End With
MsgBox ("File has been saved to: " & saveString & " Please continue.")
Set wdApp = Nothing
'Open next document row 3
npathString = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("B3")
ndotString = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("D3")
nopenString = npathString & ndotString
'Open Word Instance
' Set wdApp = CreateObject("Word.Application")
' wdApp.Visible = True

'Open Template as Document
wdApp.Documents.Add Template:=(nopenString)
wdApp.Activate
'Prefill Data - field names and values from excel
Dim nameFLD As String
Dim pinFLD As String
Dim rnFLD As String
Dim rnpinFLD As String
nameFLD = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("M3")
pinFLD = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("N3")
rnFLD = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("O3")
rnpinFLD = xlApp.Workbooks("MasterIndex.xls").Sheets("Doc_Index").Range("P3")
With wdApp.ActiveDocument
If Len(nameFLD) > 0 Then
.FormFields(nameFLD).Result = xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index").Range("B2")
End If
If Len(pinFLD) > 0 Then
.FormFields(pinFLD).Result = xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index").Range("C2")
End If
If Len(rnFLD) > 0 Then
.FormFields(rnFLD).Result = xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index").Range("D2")
End If
If Len(rnpinFLD) > 0 Then
.FormFields(rnpinFLD).Result = xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index").Range("E2")
End If
End With

'Close document wdApp.Documents.Open(saveString).Close
'wdApp.Documents.Open(saveString).Close

End Sub

[fumei]

I am sorry. Perhaps someone else can help you. From my perspective you are not listening. I wrote:

"(assuming it IS the Document object wdDoc, AND "saveString" is a properly qualified path and name...)"

with the code:
With wdDoc
.SaveAs Filename:=saveString
.Close
End With
' destroy current Document object
Set wdDoc = Nothing



You use the Application object, like this:
With wdApp
.SaveAs FileName:=saveString
.Close
End With
This should get errors! In fact, the code is now even more full of errors. You make the Application object = Nothing (NOT a Document object as I suggested)...then try to use it.


I am unclear how you can posssibly do:


For fldCount = 1 To ActiveDocument.FormFields.Count

which implies that Word is both open, and that this code is running from Word...

BEFORE, repeat BEFORE, you run:

Set wdApp = GetObject(, "Word.Application")

which gets an existing instance of Word. You can not use use a plain ActiveDocument.whatever unless you ARE in Word.

I still do not think you are using Option Explicit as there is wdResult being used (undeclared).

Try to use proper object whereever you can. As there is that ActiveDocument apparently being used BEFORE an instance of Word being made, i am really not sure what the heck is going on. But your code:
'Determine if any form fields are empty
missString = ""
For fldCount = 1 To ActiveDocument.FormFields.Count
If Len(ActiveDocument.FormFields(fldCount).Result) = 0 Then
missString = missString & " " & _
ActiveDocument.FormFields(fldCount).Name & ","
End If
Next fldCount

IF you in Word, could be better done like:
Dim oFF As FormField

For Each oFF in ActiveDocument.Formfields
If off.result = "" Then
missString = oFF.Name & ","
End If
Next



Hopefully someone else can help you more. Good luck.

[tca_VB]

Thanks for the comments. There is some code still missing from my post as I actually open the first instance of Word App and first word document from a macro in Excel. As I mentioned, lots of back and forth.

I will work with the suggestions you made and see if I can clean up my code for the parts in Word. I see where you are going, I'm not up on my terms - more of a hack coder piecemealing things together. Not a great approach I agree.

I will repost when I get it working with your suggestions for those following along. You have provided me with alot of help. Thanks!

[fumei]

OK, then let's walk this through.

IF you are indeed "open the first instance of Word App and first word document from a macro in Excel."

THEN you do not, repeat do not, need to ever make an instance in your code. So....let me re-code based on the assumption there is an existing instance of Word. And that you have done this in another procedure. Correct?

Also, I will remove all useless variable, such as tempName. This variable is NOT declared (no Option explicit again???), and...it is never used. You give it a value...and never use it. What is the point of that?

If so, then the object used for that instance MUST be a globally declared Public object. Let's assume you have done this. In that case:
Option Explicit

Sub cmdCloseSave_Click()
Dim FFCount As Long
Dim missString As String
Dim dataCount As Long
Dim xlApp As Object
Dim wdDoc As Object
Dim wdResult As String
' unless you DO have it declared globally elsewhere!

Dim pathString As String
Dim docString As String
Dim clinameString As String
Dim catnameString As String
Dim saveString As String
Dim g As Integer
Dim npathString As String
Dim ndotString As String
Dim nopenString As String
'Set document row number for document after PIN Approval
g = 3
Set wdDoc = wdApp.ActiveDocument

' get formfield count
FFCount = wdDoc.FormFields.Count
' Determine if any form fields are empty
' kept your longer code for your understanding

missString = ""
For fldCount = 1 To FFCount
If Len(wdDoc.FormFields(fldCount).Result) = 0 Then
missString = missString & " " & _
wdDoc.FormFields(fldCount).Name & ","
End If
Next fldCount
'If empty, provide message and end
If Len(missString) > 0 Then
missString = Left(missString, Len(missString) - 1)
MsgBox ("You must Complete All Fields. Return to " & _
missString & ". Then click again.")
Exit Sub
End If
'Add Data to Pin_Index

' removed two lines that did nothing

' note that you can re-use FFCount!

For dataCount = 1 To FFCount
wdResult = wdDoc.FormFields(dataCount).Result
Set xlApp = GetObject(, "Excel.Application")
xlApp.Visible = True
xlApp.Workbooks("MasterIndex.xls").Activate
xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index") _
.Cells(2, dataCount).Value = wdResult
Next dataCount
'Add Document value to Excel
Set xlApp = GetObject(, "Excel.Application")
xlApp.Visible = True
xlApp.Workbooks("MasterIndex.xls").Activate
xlApp.Workbooks("MasterIndex.xls").Sheets("Pin_Index") _
.Cells(2, 7).Value = g
'Create File Save path and name
pathString = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("C2")
docString = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("E2")
clinameString = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Pin_Index").Range("B2")
catnameString = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Pin_Index").Range("F2")
saveString = pathString & catnameString & "\" & _
clinameString & "_" & docString

With wdDoc
.SaveAs FileName:=saveString
.Close
End With
' doc saved, closed, and now object is destroyed
Set wdDoc = Nothing

MsgBox ("File has been saved to: " & saveString & _
" Please continue.")
' you had Set wdApp = Nothing here
' NO! Keep the same instance of Word

'Open next document row 3
npathString = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("B3")
ndotString = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("D3")
nopenString = npathString & ndotString

' you have code to open a template as document
' WHY?????????????

' as previous Word doc object destroyed
' set a new one with new doc
Set wdDoc = wdApp.Documents.Open(FileName:=nopenString)

'Prefill Data - field names and values from excel
Dim nameFLD As String
Dim pinFLD As String
Dim rnFLD As String
Dim rnpinFLD As String
nameFLD = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("M3")
pinFLD = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("N3")
rnFLD = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("O3")
rnpinFLD = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("P3")
With wdDoc
If Len(nameFLD) > 0 Then
.FormFields(nameFLD).Result = _
xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Pin_Index").Range("B2")
End If
If Len(pinFLD) > 0 Then
.FormFields(pinFLD).Result = _
xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Pin_Index").Range("C2")
End If
If Len(rnFLD) > 0 Then
.FormFields(rnFLD).Result = _
xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Pin_Index").Range("D2")
End If
If Len(rnpinFLD) > 0 Then
.FormFields(rnpinFLD).Result = _
xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Pin_Index").Range("E2")
End If
End With
' the new doc (wdDoc) is still active
' you do NOT, repeat NOT, have a name for it!
' you used saveString for the previous doc
' WHAT are you going to use for THIS doc file???
With wdDoc
.SaveAs FileName:=need_a_name!!
.Close
End With
' doc saved, closed, and now object is destroyed
Set wdDoc = Nothing

' NOTE the wdDoc object is saved, closed, and destroyed
' ready to be used again
' the wdApp object is STILL instantiated
' as you (I assumed) created this elsewhere, you
' MUST destroy it there
' also as this seems to be a commandbutton Sub
' what are you doing AFTER this???
End Sub

Please notice that I have used indenting on the code.

[fumei]

Notice also that I changed your "As Integer" to "As Long".

VBA now always internally converts Integers to Longs.

[fumei]

Also, again to suggest proper use of objects...instead of:
nameFLD = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("M3")
pinFLD = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("N3")
rnFLD = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("O3")
rnpinFLD = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index").Range("P3")

where you use xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index") over and over again, use an object.


Dim ThatSheet As WorkSheet
Set ThatSheet = xlApp.Workbooks("MasterIndex.xls") _
.Sheets("Doc_Index")

' now you can

nameFLD = ThatSheet.Range("M3")
pinFLD = ThatSheet.Range("N3")
rnFLD = ThatSheet.Range("O3")
rnpinFLD = ThatSheet.Range("P3")

' rather neater and easier to read
' don't you think?

[tca_VB]

fumei,
Thanks for all of your suggestions. I got it working now using all that you provided.

To one of your questions as to why I'm opening a template as a word doc - because that's what I need to do. I have protected form templates that need to remain intact. The code that I am working on is to open a document from the template, fill out the form fields, and save - then next.

You are also correct that I didn't finish off naming the last open file, that is because the code basically starts repeating since I'm doing this for a series of documents. Once working in this, I can take it from there.

Thanks again for all your help. I'm much relieved!

[fumei]

"To one of your questions as to why I'm opening a template as a word doc - because that's what I need to do. I have protected form templates that need to remain intact. "

Huh? That does not make sense. I suspect you do not fully understand how templates are used. A template (.DOT) file can be cloned into a new document...and that new document can still be protected for forms.

There are very very few reasons for opening a template as document, and as far as I can see, what you are doing is NOT one of them.