Cross application forms

[SgtPepps]

I need to load a Form that was created in Excel, in Word. The form contains code specific to excels object model. I can open the file ok using:

Sub test()
Dim Spreadsheet As Object
Set Spreadsheet = GetObject("C:\Spreadsheet.xls&quot
With Spreadsheet
.Application.Visible = True
.Parent.Windows("Spreadsheet.xls&quot.Visible = True
.Application.Workbooks("Spreadsheet.xls&quot.Saved = True
.Application.Quit
End With
Set Spreadsheet = Nothing
End Sub

but in the middle of the 'With' statement, i'd like to load a form stored in the xls. I get an error message saying (Object Required). Is this the best way to go about it or should i do it a diffrent way??

Regards

Mike Hold the wheel and Drive

 

[RobBroekhuis]

What is the code that gave you the error?
Rob

 

[SgtPepps]

Sub test()
Dim Spreadsheet As Object
Set Spreadsheet = GetObject("C:\Spreadsheet.xls&quot
With Spreadsheet
.Application.Visible = True
.Parent.Windows("Spreadsheet.xls&quot.Visible = True

Load Form -- This code gives the error
Form.Show --

.Application.Workbooks("Spreadsheet.xls&quot.Saved = True
.Application.Quit
End With
Set Spreadsheet = Nothing
End Sub

 

[SgtPepps]

It seems to work ok with this code when i have the Form stored in the .doc as well as the .xls, i havn't tried removing it from the .xls yet.

Thanks

Mike

 

[SgtPepps]

Although the code does work, it runs extremely slowly. Within the form is a loop that searches a .dbf for strings that match the value of a textbox. When i run the code from excel, the time it takes to run the code is extremely quick, however, when run from word, using the above code, it takes up to a minute to do what it took a fraction of a second to do before. Do you know why this is?

Mike

 

[RobBroekhuis]

Applications that are not the active application often seem to run MUCH slower, so your circuitous route to your database via Excel from Word is probably to blame - in which case, the only thing that will help is to do the stuff right inside Word - is that an option?
Rob

 

[SgtPepps]

I havn't got a clue how to search a .dbf without using any applications other than word, or even if its possible. All it does is open a .dbf, show a form with 2 list boxs and 1 text box. In list box1 are the db field labels, you choose which one you want to search. In the text box you type the first few letters of the word your searching for, within the '_Change' procedure for that textbox is code that finds strings that match what you have typed and populates the last list box with these items. Its not special but as far as i'm aware, requires excel to open the .dbf. If you know of a better way, i'm open for options!!

Thanks

Mike

 

[RobBroekhuis]

I just tried a Word/Excel thing myself, and did not find that things moved more slowly if an Excel function is called from Word. Try putting your form load code inside an Excel sub, and calling the sub, instead of trying to load it directly from within Word. Something like


(in Excel)
sub LoadForm
Form.show
end sub

(in Word)
...
Spreadsheet.application.run "LoadForm"
...

Rob

 

[SgtPepps]

I get "Error:9 Subscript out of range" with that. I did try loading the form in the "Workbook_Open" procedure, and opening the .xls from the Word procedure, this has worked perfectly. Now I need to try it on a MSOffice97 install :-/

Thanks for your help!

Mike