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.