PDA

View Full Version : [SOLVED] How to open word using excel if not already open?



roxnoxsox
12-16-2015, 08:07 AM
I'm trying to create a macro which will take a screenshot of an internet web page and paste it into word. Then if I paste another screenshot, it will put it in the same word document. However, I can't seem to get this to work so that it checks if word is already open and if so, doesn't keep trying to create a new document.




Dim mydoc As String
Dim myAppl As String

mydoc = "W:\ExampleLocation\Example UPLOAD " & Format(Date, "DD-MM-YYYY") & ".docx"
myAppl = "Word.Application"

'Check if Word Document exists and if not, create a new workbook and paste
If Not DocExists(mydoc) Then
Application.Run "CreateWordDocument"
Application.Run "TakeScreenshot"
Application.Run "PasteImagetoWord"
Exit Sub

'If Word Document already exists then paste rather than creating a new workbook
Else
Application.Run "TakeScreenshot"
Application.Run "PasteImagetoWord"
End If


Any clues where I'm going wrong with this? Many thanks

Bob Phillips
12-16-2015, 08:40 AM
I think you need something like


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

If wordApp Is Nothing Then

Set wordApp = CreateObject("Word.Application")
End If

roxnoxsox
12-16-2015, 08:46 AM
Hi xld, thanks for getting back to me - not sure how this would fit into my macro? When I run the "CreateWordDocument", this is automatically saving the document with a certain name?


Sub CreateWordDocument()
With CreateObject("Word.Document")
.Windows(1).Visible = True
.SaveAs Filename:=("W:\ExampleLocation\Example UPLOAD " _
& Format(Now(), "DD-MM-YYYY") & ".docx")
End With

Bob Phillips
12-17-2015, 02:14 AM
How to be precise roxnoxsox without seeing it all, but maybe something like this would work


Sub CreateWordDocument()
Static wd As Object

If wd Is Nothing Then

Set wd = CreateObject("Word.Document")
wd.Windows(1).Visible = True
wd.SaveAs Filename:=("W:\ExampleLocation\Example UPLOAD " _
& Format(Now(), "DD-MM-YYYY") & ".docx")
End If

End Sub

Not sure how you grab the handle to that new document in your code, so we may be just shifting the problem. May be better to declare wd as a public variable, and use that later.

gmayor
12-17-2015, 02:57 AM
I suggest the following

Sub Example()
Dim myDoc As String
Dim wdApp As Object
Dim oDoc As Object
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

myDoc = "W:\ExampleLocation\Example UPLOAD " & Format(Date, "DD-MM-YYYY") & ".docx"
myAppl = "Word.Application"

'Check if Word Document exists and if not, create a new workbook and paste
If Not FileExists(myDoc) Then
Set oDoc = wdApp.documents.Add
oDoc.SaveAs myDoc
Else
Set oDoc = wdApp.documents.Open(myDoc)
End If
TakeScreenshot oDoc
PasteImagetoWord oDoc
Exit Sub
End Sub

Public Function FileExists(strFullName As String) As Boolean
'Graham Mayor
'strFullName is the name with path of the file to check
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strFullName) Then
FileExists = True
Else
FileExists = False
End If
lbl_Exit:
Exit Function
End Function

You have not posted the code for the screen capture nor the image paste, but it is an easy matter to pass oDoc to the functions. e.g.


Sub PasteImageToWord(ByVal oDoc As Object)
Dim oRng As Object
Set oRng = oDoc.Range
oRng.collapse 0
oRng.Paste
lbl_exit:
Exit Sub
End Sub

snb
12-17-2015, 04:10 AM
sub M_snb()
c00 = "W:\ExampleLocation\Example UPLOAD " & Format(Date, "DD-MM-YYYY") & ".docx"

if dir(c00)="" then
with createobject("Word.document")
.saveas2 c00
.close
end with
end if

with getobject(c00)
TakeScreenshot
PasteImagetoWord
.close -1
end with
End Sub

roxnoxsox
12-18-2015, 03:08 AM
Many thanks for the suggestions guys, I really appreciate it! I struggle to wrap my head around vba sometimes. I ended up using snb's formula as I found it the easiest to follow but thank you all!