In XL VBA, Word Document SaveAs throws error

[Ed from AZ]

I'm working in Vista with Word and Excel 2007. I'm writing a macro in
Excel that opens and reads a file using FSO, creates a new Word doc
and writes the file data into the doc, and then saves the doc so I can
use Word ranges to grab the data and write it into the Excel file.

Every time the code gets to the SaveAs command for the Word document,
the code balks and throws a run-time error. If I click Debug and hit
either F8 or F5, the command immediately executes and the code
continues running just fine. The error is:
-2147417851 (80010105)
Method 'SaveAs' of object '_Docuement' failed

I tried adding Sleep in case there was something going on that wasn't
finished, and even threw in a DoEvents. No joy! If anyone can help
me over this hump, I'd greatly appreciate it!

Thanks!
Ed

strPath = Left(strFile, Len(strFile) - 4)
Sleep (15000)
DoEvents
objDoc.SaveAs strPath & ".docx" 'Both statements throw the error
'objWd.Documents(objWd.Documents.Count).SaveAs Filename:=strPath &
".doc"
Sleep (15000)

 

[Doug Robbins - Word MVP]

What does strFile contain, or more specifically, what is its extension. If
the extension is docx, your code will be trying the save a file with a name
that has two periods before the extension.

Try objDoc.SaveAs strPath & "docx"

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Ed from AZ]

Hi, Doug.

No - the end of strFile is ".xml". So it's a clean string without the
"."

And when it errors and I Debug, then F8 or F5, it saves fine, with no
changes to the file path and name strings.

Ed

 

[Doug Robbins - Word MVP]

You may need to set the SaveFormat to wdFormatDocument as you are converting
from one format to another

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Ed from AZ]

You may need to set the SaveFormat to wdFormatDocument as you are converting
from one format to another

I'll give that a try, Doug. I'm not sure, though - I am not opening
the XML in Word and resaving as a doc. The XML is opened in FSO and
read, a new do is created using the Word app, contents of the XML are
pasted into the new unsaved Word doc, and then I'm trying to save the
new doc.

Is there some interaction between Word and FSO that I might not be
aware of that's causing this?
Ed

 

[Doug Robbins - Word MVP]

It would help if you showed ALL of the code. Otherwise, we are just
guessing.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Ed from AZ]

It would help if you showed ALL of the code.  Otherwise, we are just
guessing.

Thanks for sticking with this, Doug.
Here's everything except a couple of message boxes from the beginning
to saving the Word file:

strMeName = Environ("username")

'Set application objects
Set objWB = ActiveWorkbook

Set objFSO = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Err.Clear
Set objWd = GetObject(, "Word.application")
If Err Then
Set objWd = New Word.Application
End If
Err.Clear
On Error GoTo 0

'Select a file to work on
strFile = Application.GetOpenFilename("XML Files (*.xml),*.xml")
If strFile = "False" Then
MsgBox "No file selected"
Exit Sub
End If

'Get contents
Set objFile = objFSO.OpenTextFile(strFile, ForReading)
strAll = objFile.ReadAll

'Write to doc
Set objDoc = objWd.Documents.Add
objWd.Visible = True
Set rngDoc = objDoc.Content
rngDoc.Text = strAll
objFile.Close
Set objFile = Nothing

strPath = Left(strFile, Len(strFile) - 4)
objWB.SaveAs "C:\Users\" & strMeName & "\Desktop\MaintWorkbook.xls"

Sleep (15000)
DoEvents
objDoc.SaveAs strPath & ".docx"
'objWd.Documents(objWd.Documents.Count).SaveAs Filename:=strPath &
".doc"
Sleep (15000)

 

[Ed from AZ]

Okay - got a solution. Don't know if it's "proper", but it seems to
work.

I replaced:

Sleep (15000)
DoEvents
objDoc.SaveAs strPath & ".docx"
'objWd.Documents(objWd.Documents.Count).SaveAs Filename:=strPath &
".doc"
Sleep (15000)

with:

Do
On Error Resume Next
objDoc.SaveAs strPath & ".doc"
On Error GoTo 0

strDoc = objDoc.Name
Loop While strDoc = "Document1"

Like I said, it works. I do wish I understood why the "right" way
wouldn't, though. Maybe some day ...

Thanks for keeping with me, Doug.
Ed
Ed

 

[Doug Robbins - Word MVP]

Hi Ed,

Trimming your code down to the bare minimum

Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim strpath As String
Dim strAll As String
strpath = "C:\MergeFiles\test"
strAll = "This is the text."
On Error Resume Next
Err.Clear
Set objWd = GetObject(, "Word.application")
If Err Then
Set objWd = New Word.Application
End If
Err.Clear
On Error GoTo 0

'Write to doc
Set objDoc = objWd.Documents.Add
objWd.Visible = True
Set rngDoc = objDoc.Content
rngDoc.Text = strAll

objDoc.SaveAs strpath & ".docx"

and running that code, a document containing the "This is the text." is
created and saved with the path\name of c:\mergefiles\test\docx with no
problem.

Try re-arranging your code as follows:

strMeName = Environ("username")

'Set application objects
Set objWB = ActiveWorkbook

Set objFSO = CreateObject("Scripting.FileSystemObject")

On Error Resume Next
Err.Clear
Set objWd = GetObject(, "Word.application")
If Err Then
Set objWd = New Word.Application
End If
Err.Clear
On Error GoTo 0

'Select a file to work on
strFile = Application.GetOpenFilename("XML Files (*.xml),*.xml")
If strFile = "False" Then
MsgBox "No file selected"
Exit Sub
End If
strPath = Left(strFile, Len(strFile) - 4)

'Get contents
Set objFile = objFSO.OpenTextFile(strFile, ForReading)
strAll = objFile.ReadAll

'Write to doc
Set objDoc = objWd.Documents.Add
objWd.Visible = True
Set rngDoc = objDoc.Content
rngDoc.Text = strAll
objDoc.SaveAs strPath & ".docx"
objFile.Close
Set objFile = Nothing

objWB.SaveAs "C:\Users\" & strMeName & "\Desktop\MaintWorkbook.xls"



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com