PDA

View Full Version : Solved: Excel to Word



swoozie
05-23-2006, 09:16 AM
:help Sub CreateWord()
NewBook = ActiveWorkbook.Name

Dim myWd As Object
Set myWd = CreateObject("Word.Application")

myWd.Documents.Add
'I need to be able to open either a new document or a template document
'to be able to paste the charts into that I created in excel and add text
'based on the charts. The text is static other than the numbers that wiull'
'be inserted when the charts are updated. As of right now, I do not know
'where to start. I am not even able to open word at this point.

Windows(NewBook).Activate
Sheets("CHART").Select
For i = 1 To 6
CName = "Chart " & i
Sheets("Chart").ChartObjects(CName).Activate
Sheets("Chart").ChartObjects(CName).Copy
' After I access the chart how would I return to the word doc?
myWd.PasteAndFormat (wdChartPicture)
Next i
myWd.Visible = True

'Save New File as : Need to Change File Location information.
'This is where the newly created file is saved to.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Drive = "c:\"
Path = "Path location"
Filename = "FileName & SName1 "
ext = ".doc"

ChangeFileOpenDirectory _
Drive & Path
myWd.Documents.SaveAs Filename:=Filename & ext, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False

myWd.Documents.Save
myWd.Documents.Close
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Set myWd = Nothing
End Sub



Please point me into the right direction. I am doing something wrong

lucas
05-23-2006, 09:35 AM
Line breaks added to your code to keep it from running off the page....also added End sub.

Take a look at this from a search of the kb.....would also recommend that you search the forum.
http://vbaexpress.com/kb/getarticle.php?kb_id=436

swoozie
05-23-2006, 10:27 AM
I have seached the forum, and searched.
Sub CreateWord()
NewBook = ActiveWorkbook.Name

Dim myWd As Object
Set myWd = CreateObject("Word.Application")

myWd.Documents.Add


This doesn't start or activate word so I must be missing somthing even though several posts use the exact same syntax

Sheets("Chart").ChartObjects(CName).Copy
' After I access the chart how would I return to the word doc?
mywd.activate 'doenst work here, well probably since the initial call for word doenst work.
myWd.PasteAndFormat (wdChartPicture)

So, What I need to figure out is how actually OPEN and work with Word and how to create either bookmarks or place holders for me to paste the summary text and the chart image into/at.

The forums I found really did not provide much insite on why the above code wasnt working.

lucas
05-23-2006, 10:46 AM
Did you try the kb entry....take note of this line at the top of the code:


'Remember: this code requires a referece to the Word object model

In the vbe go to tools-references and check the mirosoft word (your ver.)object library....you have to have this reference.

swoozie
05-23-2006, 10:54 AM
The reference is checked. Did that first.

lucas
05-23-2006, 10:56 AM
You ran the kb entry?.......

lucas
05-23-2006, 11:09 AM
Try this, it also works for me.....its from a search of the forum

swoozie
05-24-2006, 06:26 AM
Sub CreateWord()
Dim Appword As New Word.Application
Dim wdDoc As Word.Document

Set Appword = CreateObject("Word.Application")
Appword.Documents.Add

NewBook = ActiveWorkbook.Name '(records the name of the current xl spreadsheet)
For i = 1 To 6
Windows(NewBook).Activate
Sheets("CHART").Select
CName = "Chart " & i
Sheets("Chart").ChartObjects(CName).Activate
Sheets("Chart").ChartObjects(CName).Copy 'copies the
'Do I need something here to activate the word document?
Appword.Selection.Paste 'Since it is a chart to I need to paste and format? (wdChartPicture)

Next i

Appword.Visible = True

'Save New File as : Need to Change File Location information. This is where the newly created file is saved to.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Drive = "c:\"
Path = "Directory location\"
FileName = "Monthly_Summary" & SName1
ext = ".doc"

ChangeFileOpenDirectory _
Drive & Path
Appword.ActiveDocument.SaveAs FileName:=FileName & ext, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False

Appword.Documents.Save
Appword.Documents.Close
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Set Appword = Nothing
End Sub


It is stalling out. The code looks like it finished, however the document is not available.

I think I faux paux'd

swoozie
05-25-2006, 08:31 AM
I have a hard time believing that no one is able to look at my code and correct.

Do we all think that it should work fine?

mdmackillop
05-25-2006, 10:01 AM
Option Explicit
Sub CreateWord()

Dim myWd As New Word.Application
Dim NewBook As String, CName As String, Drive As String
Dim Path As String, Fil As String, Ext As String, MyFile As String
Dim i As Long

Application.ScreenUpdating = False

NewBook = ActiveWorkbook.Name

Set myWd = CreateObject("Word.Application")
myWd.Documents.Add

Windows(NewBook).Activate
Sheets("CHART").Select
For i = 1 To 3
CName = "Chart " & i
Sheets("Chart").ChartObjects(CName).Activate
Sheets("Chart").ChartObjects(CName).Copy
' After I access the chart how would I return to the word doc?
myWd.Selection.Paste
Next i
'Save New File as : Need to Change File Location information.
'This is where the newly created file is saved to.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Drv = "c:\"
pth = InputBox("File location") & "\"
Fil = InputBox("File name")
Ext = ".doc"

MyFile = Drv & pth & Fil & Ext

myWd.ActiveDocument.SaveAs Filename:=MyFile, FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False

myWd.Documents.Close
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

myWd.Quit
Set myWd = Nothing
Application.ScreenUpdating = True
If Len(Dir(MyFile)) > 0 Then
MsgBox MyFile & " created"
End If
End Sub

swoozie
05-25-2006, 11:03 AM
Option Explicit
Sub CreateWord()

Dim myWd As New Word.Application
Dim wdDoc As Word.Document
Dim NewBook As String, CName As String, Drive As String
Dim Path As String, Fil As String, Ext As String, MyFile As String
Dim i As Long

Application.ScreenUpdating = False

NewBook = ActiveWorkbook.Name

Set myWd = CreateObject("Word.Application")
myWd.Documents.Add

Windows(NewBook).Activate
Sheets("CHART").Select
For i = 1 To 3
CName = "Chart " & i
Sheets("Chart").ChartObjects(CName).Activate
Sheets("Chart").ChartObjects(CName).Copy
' After I access the chart how would I return to the word doc?
myWd.Selection.Paste
Next i
'Save New File as : Need to Change File Location information.
'This is where the newly created file is saved to.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Drv = "c:\"
pth = InputBox("File location") & "\"
Fil = InputBox("File name")
Ext = ".doc"

MyFile = Drv & pth & Fil & Ext

myWd.ActiveDocument.SaveAs Filename:=MyFile, FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False

myWd.Documents.Close
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

myWd.Quit
Set myWd = Nothing
Application.ScreenUpdating = True
If Len(Dir(MyFile)) > 0 Then
MsgBox MyFile & " created"
End If
End Sub


Okay other than Mywd = Appword, this being before the for...next statement
Windows(NewBook).Activate
Sheets("CHART").Select and Appword.visible = True
What is the difference? Does your code work?
I have pasted into my workbook and it is still stalling out at mywd.selection.paste after stalling, the application advances to
Private Sub Workbook_Open()
frmATSMain.Show
End Sub
and stops with nothing created further. Then an error box pops up stating excel is waiting for another application to complete and OLE process. Then everything just stops.

I re-itterate, I have searched and looked and compared and I can not find how my code differs from other codes for similar items. By all respects it should work. I have all references checks, all variables declared, the only thing I question is the line"

dim wdDoc as word.document

Where is this actually used and how is it actually used? I only see a declaration and nothing else, so why would it be needed?

PS. If i didnt mention it earlier, I did try mdmackillop's code and it did what I described earlier, which is the identical thing my code was doing.

mdmackillop
05-25-2006, 11:09 AM
Works for me.
Here's a copy of my file.

mdmackillop
05-25-2006, 11:45 AM
You're right. wdDoc is not used and should be deleted. I can't see that it would cause your problem though.

swoozie
05-25-2006, 12:49 PM
Well, your file worked fine with mine still doesnt. I think I will just delete all the code and for this section and start over. There must be something small infinite, that is wrong. I did change to .pastespecial and it pasted as a pic instead of an imbedded object.

Thanks

swoozie
05-30-2006, 07:26 AM
You're right. wdDoc is not used and should be deleted. I can't see that it would cause your problem though.

Please look at my attached file, I can not see anything different and it doenst work. It stalls out after pasting the first The chart.xls file you sent me worked perfectly well.

Thank you,

Swoozie

mdmackillop
05-30-2006, 10:11 AM
Hi Swoozie,
I've added code to disable events which I think was causing your userform to reopen and interfere with things. I also reset the i value from 3 to 6 to catch all your charts. This ran without trouble and I've attached my result.
Regards
MD

Private Sub btntest_Click()
'Chart Creation
Application.EnableEvents = False
Call CreateWord
Application.EnableEvents = True
End Sub



For i = 1 To 6
CName = "Chart " & i

swoozie
05-30-2006, 11:12 AM
Thanks, That worked! I knew it had to do something with the form but I didn't know what.

Now I am off to figure out the rest of the stuff. So close to completion.

mdmackillop
05-30-2006, 11:47 AM
It's necessary sometimes and usually code runs quicker if Events, ScreenUpdating are turned off. You can use a sub similar to the following to do this. Just add ToggleStuff False into your code, remembering to reset it to True on completion.

Sub ToggleStuff(x As Boolean)
Application.EnableEvents = x
Application.ScreenUpdating = x
'Delete the following if not required.
If x = False Then
Application.Calculation = xlManual
Else
Application.Calculation = xlAutomatic
End If
End Sub