Option Explicit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

samanco@btinternet.com - 30 Apr 2008 20:26 GMT

I have a macro used to copy a range from a spreadshhet into a word
document and formats the table, etc. I have been trying to be
consistent and using Option Explicit across the whole application. I
think have decaled all the variables in this macro but it only works
as far as the saveas statement, then the error capture kicks in and
quits the macro. However if I remove the Option Explicit declaration
then the macro completes fully including the saveAs element as
required. If I step through the macro using f8 it does not report a
variable undeclared, if I remove the error traps then still no debug
but ends without the filesave section completing? Any ideas ?

Option Explicit

Sub autoword()
Dim wdApp As Object
ThisWorkbook.Sheets("METstats").Range("b6:h123").Copy 'Change me
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If wdApp Is Nothing Then
Set wdApp = GetObject("", "Word.Application")
End If
On Error GoTo 0
With wdApp
.Documents.Add
.Visible = True
End With
With wdApp.Selection
.Paste
.PageSetup.Orientation = wdOrientLandscape
.Tables(1).Select
.Tables(1).AutoFitBehavior (wdAutoFitWindow)
.Tables(1).AutoFitBehavior (wdAutoFitWindow)
.Font.Size = 10
.Tables(1).AutoFitBehavior (wdAutoFitWindow)
.Tables(1).AutoFitBehavior (wdAutoFitWindow)
.Style = "Table Simple 1"
End With
On Error GoTo Quit ' this is where things seen to go wrong
With wdApp 'change filename
'ChangeFileOpenDirectory "C:\temp\" 'remove comment tag and edit path
as required
ActiveDocument.SaveAs Filename:="METstatsrep.doc", FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
End With
Quit:
Set wdApp = Nothing
End Sub

Reply to this Message

Jim Thomlinson - 30 Apr 2008 20:50 GMT

Does the code compile???

Signature

HTH...

Jim Thomlinson

> I have a macro used to copy a range from a spreadshhet into a word
> document and formats the table, etc. I have been trying to be
[quoted text clipped - 48 lines]
> Set wdApp = Nothing
> End Sub

Reply to this Message

Per Jessen - 30 Apr 2008 20:56 GMT

Hi

I think you need a dot before ActiveDocument.SaveAs...

Regards,
Per

>I have a macro used to copy a range from a spreadshhet into a word
> document and formats the table, etc. I have been trying to be
[quoted text clipped - 48 lines]
> Set wdApp = Nothing
> End Sub

Reply to this Message

samanco345@googlemail.com - 30 Apr 2008 21:27 GMT

> Hi
>
[quoted text clipped - 4 lines]
>
> <sama...@btinternet.com> skrev i meddelelsennews:b4fca726-24cf-49bb-bf93-dced3e14751f@34g2000hsh.googlegroups.com...

Just one little Dot for hours of trying, thank you very much. Any
idea why this still compiled and worked without a Dot when Option
Explicit was removed ??

Reply to this Message

samanco345@googlemail.com - 30 Apr 2008 21:29 GMT

On Apr 30, 8:26 pm, sama...@btinternet.com wrote:

> I have a macro used to copy a range from a spreadshhet into a word
> document and formats the table, etc. I have been trying to be
[quoted text clipped - 48 lines]
> Set wdApp = Nothing
> End Sub

Yes I have just checked again and this compiles

Reply to this Message

Dave Peterson - 30 Apr 2008 23:19 GMT

What is the error description that you see when it fails?

Since you aren't getting any errors for the MSWord constants:
wdOrientLandscape
wdAutoFitWindow
wdFormatDocument

That must mean that you have a reference set to "Microsoft Word xx.0 Object
Library".

So I was explicit in my declarations.

Option Explicit
Sub autoword()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document

ThisWorkbook.Sheets("METstats").Range("b6:h123").Copy 'Change me

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wdApp Is Nothing Then
Set wdApp = GetObject("", "Word.Application")
End If

With wdApp
Set wdDoc = .Documents.Add
.Visible = True
End With

With wdApp.Selection
.Paste
.PageSetup.Orientation = wdOrientLandscape
.Tables(1).Select
.Tables(1).AutoFitBehavior wdAutoFitWindow
.Tables(1).AutoFitBehavior wdAutoFitWindow
.Font.Size = 10
.Tables(1).AutoFitBehavior wdAutoFitWindow
.Tables(1).AutoFitBehavior wdAutoFitWindow
.Style = "Table Simple 1"
End With

wdDoc.SaveAs Filename:="c:\temp\METstatsrep.doc", _
FileFormat:=wdFormatDocument, _
LockComments:=False, _
Password:="", _
AddToRecentFiles:=True, _
WritePassword:="", _
ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, _
SaveFormsData:=False, _
SaveAsAOCELetter:=False

Quit:
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

This is called early binding -- because of the way both wdApp and wdDoc were
declared (and the reference that was set).

The alternative is to use late binding--without the reference.

Option Explicit
Sub autoword()
Dim wdApp As Object
Dim wdDoc As Object

ThisWorkbook.Sheets("METstats").Range("b6:h123").Copy 'Change me

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wdApp Is Nothing Then
Set wdApp = GetObject("", "Word.Application")
End If

With wdApp
Set wdDoc = .Documents.Add
.Visible = True
End With

With wdApp.Selection
.Paste
.PageSetup.Orientation = 1 'wdOrientLandscape
.Tables(1).Select
.Tables(1).AutoFitBehavior 2 'wdAutoFitWindow
.Tables(1).AutoFitBehavior 2 'wdAutoFitWindow
.Font.Size = 10
.Tables(1).AutoFitBehavior 2 'wdAutoFitWindow
.Tables(1).AutoFitBehavior 2 'wdAutoFitWindow
.Style = "Table Simple 1"
End With

'wdFormatDocument = 0
wdDoc.SaveAs Filename:="c:\temp\METstatsrep.doc", _
FileFormat:=0, _
LockComments:=False, _
Password:="", _
AddToRecentFiles:=True, _
WritePassword:="", _
ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, _
SaveFormsData:=False, _
SaveAsAOCELetter:=False

Quit:
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

Notice that since excel doesn't know about those MSWord constants, I had to
provide the "translation".

I went into MSWord's VBE, showed the immediate window (using view|immediate
window) and then I typed things like:

?wdAutoFitWindow
2
?wdFormatDocument
0

=========
But none of this really helps you debug your problem. I couldn't get it to fail
and I didn't see any error messages.

> I have a macro used to copy a range from a spreadshhet into a word
> document and formats the table, etc. I have been trying to be
[quoted text clipped - 48 lines]
> Set wdApp = Nothing
> End Sub

Signature

Dave Peterson

Reply to this Message