Hi anyone

I have created a tool using Excel 2003 which contains several macros,
and runs fine on my machine and also on my colleagues' with the same
versions of Excel and Windows (XP Pro).

However, when a client attempts to open the file it appears to open
very slowly until they get a message stating that there is a problem
and Excel crashes.

They are using the same operating system, but running Excel 2000.

My inital suspicion was that the problem lay with the module which
opened MS Word and pasted information across, but i think it may be
more fundamental than that.

I have tried:

- Changing the Microsoft Office Object Library (which is version 11 in
Excel 2003, and is Microsoft Office 9.0 Object Library on their
machine).
- Re-saving the file as 97-2003 compatible.

Can anyone help?

Thanks, N.

Hard to tell without any code, but if you intend to support Office 2000, you
should write you code on that system.
2003 has capabilities that are not supported on 2000 and will lead to
problems if used.
Depends on your references and error handling also.
Using late binding instead may be option.
http://word.mvps.org/faqs/InterDev/E...ateBinding.htm

NickHK

"noggins" wrote in message
ups.com...
Hi anyone

I have created a tool using Excel 2003 which contains several macros,
and runs fine on my machine and also on my colleagues' with the same
versions of Excel and Windows (XP Pro).

However, when a client attempts to open the file it appears to open
very slowly until they get a message stating that there is a problem
and Excel crashes.

They are using the same operating system, but running Excel 2000.

My inital suspicion was that the problem lay with the module which
opened MS Word and pasted information across, but i think it may be
more fundamental than that.

I have tried:

- Changing the Microsoft Office Object Library (which is version 11 in
Excel 2003, and is Microsoft Office 9.0 Object Library on their
machine).
- Re-saving the file as 97-2003 compatible.

Can anyone help?

Thanks, N.

Hi there

Thanks for your thoughts, can you get programmes to install on your
computer that mimic Excel 2000?

i have included the only macro that links to another application below.
I think i have used late binding. How does this affect excel failing to
open the file? All the macros are initiated by the user clicking on
buttons. Does excel check these macros before opening the file?

Thanks

Nige


Sub Reports_to_Word2()

Dim AppWd As Word.Application

Dim A1 As Range
Set A1 = Range("B12:I32")

Dim W1 As Range
Set W1 = Range("n40:u65")

Dim R1 As Range
Set R1 = Range("ac75:AL100")

Dim WU1 As Range
Set WU1 = Range("Aq108:ay135")

Dim E1 As Range
Set E1 = Range("be143:bm173")

Dim PI1 As Range
Set PI1 = Range("Bs181:ca213")

Dim Report_no As Single

Dim answer As String

answer = msgbox("Please wait whilst reporting forms are transfered to
MS Word. This may take a few minutes." & vbCrLf & vbCrLf & "To stop the
process hit the Escape key at anytime." & vbCrLf & vbCrLf & "Click OK
to continue.", vbOKCancel, "Generating Annual Reports")
If answer = vbCancel Then Exit Sub

On Error GoTo error_message

Application.ScreenUpdating = False

Application.StatusBar = "0 of 6 reports complete. Press escape to
stop generating reports."

'open new Word doc - Note the correct version VBA References is
required, located in VB Editor/Tools/References/MS Word XX Object
Library
Set AppWd = CreateObject("Word.Application.9")
AppWd.Visible = False
AppWd.ScreenUpdating = False
AppWd.documents.Add

'change to landscape and more margins
AppWd.Selection.PageSetup.Orientation = wdOrientLandscape
AppWd.Selection.PageSetup.TopMargin = CentimetersToPoints(2)
AppWd.Selection.PageSetup.BottomMargin = CentimetersToPoints(1.5)


Report_no = 1

Do

If Report_no = 1 Then A1.Copy
If Report_no = 2 Then W1.Copy
If Report_no = 3 Then R1.Copy
If Report_no = 4 Then WU1.Copy
If Report_no = 5 Then E1.Copy
If Report_no = 6 Then PI1.Copy

'paste in form
AppWd.Selection.PasteSpecial Link:=False,
DataType:=wdPasteOLEObject, Placement _
:=wdInLine, DisplayAsIcon:=False


Report_no = Report_no + 1

'goto end of word doc and insert page break
AppWd.Selection.EndKey Unit:=wdStory
If Report_no < 7 Then AppWd.Selection.InsertBreak
Type:=wdSectionBreakNextPage

Application.StatusBar = Report_no - 1 & " of 6 reports
complete. Press escape to stop generating reports."

Loop Until Report_no = 7


AppWd.Selection.HomeKey Unit:=wdStory
AppWd.Visible = True
AppWd.ScreenUpdating = True

Application.ScreenUpdating = True

msgbox "All reports have been generated. Reports can be
edited in Word by double clicking on the report table.", , "Reporting
complete"

Application.StatusBar = ""
AppWd.Activate

error_message: Application.StatusBar = "Reporting cancelled."
AppWd.Visible = True
AppWd.ScreenUpdating = True

End Sub

NickHK wrote:

Hard to tell without any code, but if you intend to support Office 2000, you
should write you code on that system.
2003 has capabilities that are not supported on 2000 and will lead to
problems if used.
Depends on your references and error handling also.
Using late binding instead may be option.
http://word.mvps.org/faqs/InterDev/E...ateBinding.htm

NickHK

"noggins" wrote in message
ups.com...
Hi anyone

I have created a tool using Excel 2003 which contains several macros,
and runs fine on my machine and also on my colleagues' with the same
versions of Excel and Windows (XP Pro).

However, when a client attempts to open the file it appears to open
very slowly until they get a message stating that there is a problem
and Excel crashes.

They are using the same operating system, but running Excel 2000.

My inital suspicion was that the problem lay with the module which
opened MS Word and pasted information across, but i think it may be
more fundamental than that.

I have tried:

- Changing the Microsoft Office Object Library (which is version 11 in
Excel 2003, and is Microsoft Office 9.0 Object Library on their
machine).
- Re-saving the file as 97-2003 compatible.

Can anyone help?

Thanks, N.

Not sure what you mean by your "mimic" question.

Nothing springs to mind in your code, but I don't use Word much.

And you using Early Binding:
The reference to the MS Word XX Object Library shows that. Along with the
use of "Dim AppWd As Word.Application".
To use late binding you need to remove that reference and declare all your
Word related variable as the generic "Object".
Then VBA will use the default version installed on the system.
You still need to make sure you do not use methods/properties that are not
available in earlier versions of Word or Excel. You're subject line
indicates the problem is with Excel.
So run your code on Excel 2000 and see where it fails.

NickHK

"noggins" wrote in message
ups.com...
Hi there

Thanks for your thoughts, can you get programmes to install on your
computer that mimic Excel 2000?

i have included the only macro that links to another application below.
I think i have used late binding. How does this affect excel failing to
open the file? All the macros are initiated by the user clicking on
buttons. Does excel check these macros before opening the file?

Thanks

Nige


Sub Reports_to_Word2()

Dim AppWd As Word.Application

Dim A1 As Range
Set A1 = Range("B12:I32")

Dim W1 As Range
Set W1 = Range("n40:u65")

Dim R1 As Range
Set R1 = Range("ac75:AL100")

Dim WU1 As Range
Set WU1 = Range("Aq108:ay135")

Dim E1 As Range
Set E1 = Range("be143:bm173")

Dim PI1 As Range
Set PI1 = Range("Bs181:ca213")

Dim Report_no As Single

Dim answer As String

answer = msgbox("Please wait whilst reporting forms are transfered to
MS Word. This may take a few minutes." & vbCrLf & vbCrLf & "To stop the
process hit the Escape key at anytime." & vbCrLf & vbCrLf & "Click OK
to continue.", vbOKCancel, "Generating Annual Reports")
If answer = vbCancel Then Exit Sub

On Error GoTo error_message

Application.ScreenUpdating = False

Application.StatusBar = "0 of 6 reports complete. Press escape to
stop generating reports."

'open new Word doc - Note the correct version VBA References is
required, located in VB Editor/Tools/References/MS Word XX Object
Library
Set AppWd = CreateObject("Word.Application.9")
AppWd.Visible = False
AppWd.ScreenUpdating = False
AppWd.documents.Add

'change to landscape and more margins
AppWd.Selection.PageSetup.Orientation = wdOrientLandscape
AppWd.Selection.PageSetup.TopMargin = CentimetersToPoints(2)
AppWd.Selection.PageSetup.BottomMargin = CentimetersToPoints(1.5)


Report_no = 1

Do

If Report_no = 1 Then A1.Copy
If Report_no = 2 Then W1.Copy
If Report_no = 3 Then R1.Copy
If Report_no = 4 Then WU1.Copy
If Report_no = 5 Then E1.Copy
If Report_no = 6 Then PI1.Copy

'paste in form
AppWd.Selection.PasteSpecial Link:=False,
DataType:=wdPasteOLEObject, Placement _
:=wdInLine, DisplayAsIcon:=False


Report_no = Report_no + 1

'goto end of word doc and insert page break
AppWd.Selection.EndKey Unit:=wdStory
If Report_no < 7 Then AppWd.Selection.InsertBreak
Type:=wdSectionBreakNextPage

Application.StatusBar = Report_no - 1 & " of 6 reports
complete. Press escape to stop generating reports."

Loop Until Report_no = 7


AppWd.Selection.HomeKey Unit:=wdStory
AppWd.Visible = True
AppWd.ScreenUpdating = True

Application.ScreenUpdating = True

msgbox "All reports have been generated. Reports can be
edited in Word by double clicking on the report table.", , "Reporting
complete"

Application.StatusBar = ""
AppWd.Activate

error_message: Application.StatusBar = "Reporting cancelled."
AppWd.Visible = True
AppWd.ScreenUpdating = True

End Sub

NickHK wrote:

Hard to tell without any code, but if you intend to support Office 2000,
you
should write you code on that system.
2003 has capabilities that are not supported on 2000 and will lead to
problems if used.
Depends on your references and error handling also.
Using late binding instead may be option.
http://word.mvps.org/faqs/InterDev/E...ateBinding.htm

NickHK

"noggins" wrote in message
ups.com...
Hi anyone

I have created a tool using Excel 2003 which contains several macros,
and runs fine on my machine and also on my colleagues' with the same
versions of Excel and Windows (XP Pro).

However, when a client attempts to open the file it appears to open
very slowly until they get a message stating that there is a problem
and Excel crashes.

They are using the same operating system, but running Excel 2000.

My inital suspicion was that the problem lay with the module which
opened MS Word and pasted information across, but i think it may be
more fundamental than that.

I have tried:

- Changing the Microsoft Office Object Library (which is version 11 in
Excel 2003, and is Microsoft Office 9.0 Object Library on their
machine).
- Re-saving the file as 97-2003 compatible.

Can anyone help?

Thanks, N.