In XL VBA, Word Document SaveAs throws error
in [Programming]
|
From: Doug Robbins - Word MVP on 23 Mar 2010 22:38 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" <prof_ofwhat(a)yahoo.com> wrote in message news:f3e41b8e-7282-4dd1-a6a5-3287a2620bc7(a)15g2000yqi.googlegroups.com... > On Mar 23, 2:47 pm, "Doug Robbins - Word MVP" > <d...(a)REMOVECAPSmvps.org> wrote: >> 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 > > >> >> -- >> 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" <prof_ofw...(a)yahoo.com> wrote in >> messagenews:f57620ea-3a7c-46c7-908f-c384ae97801a(a)y17g2000yqd.googlegroups.com... >> >> >> >> > 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 >> >> > On Mar 23, 2:22 pm, "Doug Robbins - Word MVP" >> > <d...(a)REMOVECAPSmvps.org> wrote: >> >> 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" <prof_ofw...(a)yahoo.com> wrote in >> >> messagenews:9bff5e4b-2efa-4b4f-9a6f-8e49a58cf89a(a)g10g2000yqh.googlegroups.com... >> >> >> > 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)- Hide quoted text - >> >> >> - Show quoted text -- Hide quoted text - >> >> - Show quoted text - >
From: Ed from AZ on 24 Mar 2010 09:40 On Mar 23, 7:38 pm, "Doug Robbins - Word MVP" <d...(a)REMOVECAPSmvps.org> wrote: > 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)
From: Ed from AZ on 24 Mar 2010 12:25 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
From: Doug Robbins - Word MVP on 24 Mar 2010 22:48
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 "Ed from AZ" <prof_ofwhat(a)yahoo.com> wrote in message news:46148514-4e3e-4eae-9c88-97745156f30c(a)15g2000yqi.googlegroups.com... > On Mar 23, 7:38 pm, "Doug Robbins - Word MVP" > <d...(a)REMOVECAPSmvps.org> wrote: >> 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) |