Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general,microsoft.public.word.vba.userforms
|
|||
|
|||
Automated Forms issue
G'day there One & All,
Please forgive my indiscretion if I've contravened polite usage of crossposting. I've not needed to send to more than one group before so I'm unsure of the protocols or accepted practices. I'm currently working on an automation project in which I have a report in Word that refers to data in various Excel worksheets which are of different layouts. The layouts are preset (by higher powers than my humble position) in the database report builder and I have no ability to influence the layouts or formats. To select which cells are referred to for data I have a form in my Word document which is used to select & open the appropriate Excel document - fortunately there is only a single worksheet in each workbook - and then allow my users to simply click on a cell to select the appropriate heading or list. I don't want to hijack my user's systems and prevent them from opening or using other applications (although a few of them are absolute gumbys and that wouldn't be a bad idea) but I want to keep my automation windows layered so that I have the Word document on the bottom, Excel window next, then my userform. I don't care if anyone wants to check their email while it's open, but when they come back to work on it I want it to have my windows in that order. At the moment, the form appears; I select & open the desired workbook; I click the control to select the worksheet cell and when I click the worksheet cell the Excel application is brought to the front of the screen - which is exactly what I expected, but I want my modeless form to remain on top as I switch Office applications. Is it possible in some way to switch the parent of the form in mid run? I've not found such a procedure and doubt it's possible but more knowledgeable minds may be able to think of somehow to do it. I don't know if it's possible to do any the above, but the only alternative I can think of is to write code in Word to insert code in Excel and its VBE that builds a userform with its code to run in the XL app. Something I may spend weeks writing only to find it doesn't work anyway. Thanks for listening, Ken McLennan Qld, Australia |
#2
Posted to microsoft.public.excel.programming, microsoft.public.word.vba.general,microsoft.public.word.vba.userforms
|
|||
|
|||
Automated Forms issue
On Dec 31, 7:13*pm, Ken McLennan wrote:
G'day there One & All, * * * * Please forgive my indiscretion if I've contravened polite usage of crossposting. I've not needed to send to more than one group before so I'm unsure of the protocols or accepted practices. * * * * I'm currently working on an automation project in which I have a report in Word that refers to data in various Excel worksheets which are of different layouts. The layouts are preset (by higher powers than my humble position) in the database report builder and I have no ability to influence the layouts or formats. To select which cells are referred to for data I have a form in my Word document which is used to select & open the appropriate Excel document - fortunately there is only a single worksheet in each workbook - and then allow my users to simply click on a cell to select the appropriate heading or list. * * * * I don't want to hijack my user's systems and prevent them from opening or using other applications (although a few of them are absolute gumbys and that wouldn't be a bad idea) but I want to keep my automation windows layered so that I have the Word document on the bottom, Excel window next, then my userform. I don't care if anyone wants to check their email while it's open, but when they come back to work on it I want it to have my windows in that order. * * * * At the moment, the form appears; I select & open the desired workbook; I click the control to select the worksheet cell and when I click the worksheet cell the Excel application is brought to the front of the screen - which is exactly what I expected, but I want my modeless form to remain on top as I switch Office applications. * * * * Is it possible in some way to switch the parent of the form in mid run? I've not found such a procedure and doubt it's possible but more knowledgeable minds may be able to think of somehow to do it. * * * * I don't know if it's possible to do any the above, but the only alternative I can think of is to write code in Word to insert code in Excel and its VBE that builds a userform with its code to run in the XL app. Something I may spend weeks writing only to find it doesn't work anyway. Thanks for listening, Ken McLennan Qld, Australia It sounds like you have a fun project to work on. I'm not sure my suggestions will help but I think I've had to do something similar in the past. The way I would do this is to not display the word document until the users were done selecting whatever value(s) they needed in Excel. I would then instantiate an instance of word within Excel VBA, use bookmarks to insert the values that were selected in excel, then finally open the word document for display. I've not ever tried anything like this while the word document was visible. I know this is not exactly what you want to do but perhaps by reducing the scope a bit you can get it done and move on rather than fight it for the rest of your career. Good Luck! |
#3
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general,microsoft.public.word.vba.userforms
|
|||
|
|||
Automated Forms issue
Check out ZOrder property and method in VBA help files. You could probably
use that to maintain the window order. "Ken McLennan" wrote: G'day there One & All, Please forgive my indiscretion if I've contravened polite usage of crossposting. I've not needed to send to more than one group before so I'm unsure of the protocols or accepted practices. I'm currently working on an automation project in which I have a report in Word that refers to data in various Excel worksheets which are of different layouts. The layouts are preset (by higher powers than my humble position) in the database report builder and I have no ability to influence the layouts or formats. To select which cells are referred to for data I have a form in my Word document which is used to select & open the appropriate Excel document - fortunately there is only a single worksheet in each workbook - and then allow my users to simply click on a cell to select the appropriate heading or list. I don't want to hijack my user's systems and prevent them from opening or using other applications (although a few of them are absolute gumbys and that wouldn't be a bad idea) but I want to keep my automation windows layered so that I have the Word document on the bottom, Excel window next, then my userform. I don't care if anyone wants to check their email while it's open, but when they come back to work on it I want it to have my windows in that order. At the moment, the form appears; I select & open the desired workbook; I click the control to select the worksheet cell and when I click the worksheet cell the Excel application is brought to the front of the screen - which is exactly what I expected, but I want my modeless form to remain on top as I switch Office applications. Is it possible in some way to switch the parent of the form in mid run? I've not found such a procedure and doubt it's possible but more knowledgeable minds may be able to think of somehow to do it. I don't know if it's possible to do any the above, but the only alternative I can think of is to write code in Word to insert code in Excel and its VBE that builds a userform with its code to run in the XL app. Something I may spend weeks writing only to find it doesn't work anyway. Thanks for listening, Ken McLennan Qld, Australia |
#4
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general,microsoft.public.word.vba.userforms
|
|||
|
|||
Automated Forms issue
This might provide information more in the line you are looking for:
http://allapi.mentalis.org/tips/tip4.shtml "Ken McLennan" wrote: G'day there One & All, Please forgive my indiscretion if I've contravened polite usage of crossposting. I've not needed to send to more than one group before so I'm unsure of the protocols or accepted practices. I'm currently working on an automation project in which I have a report in Word that refers to data in various Excel worksheets which are of different layouts. The layouts are preset (by higher powers than my humble position) in the database report builder and I have no ability to influence the layouts or formats. To select which cells are referred to for data I have a form in my Word document which is used to select & open the appropriate Excel document - fortunately there is only a single worksheet in each workbook - and then allow my users to simply click on a cell to select the appropriate heading or list. I don't want to hijack my user's systems and prevent them from opening or using other applications (although a few of them are absolute gumbys and that wouldn't be a bad idea) but I want to keep my automation windows layered so that I have the Word document on the bottom, Excel window next, then my userform. I don't care if anyone wants to check their email while it's open, but when they come back to work on it I want it to have my windows in that order. At the moment, the form appears; I select & open the desired workbook; I click the control to select the worksheet cell and when I click the worksheet cell the Excel application is brought to the front of the screen - which is exactly what I expected, but I want my modeless form to remain on top as I switch Office applications. Is it possible in some way to switch the parent of the form in mid run? I've not found such a procedure and doubt it's possible but more knowledgeable minds may be able to think of somehow to do it. I don't know if it's possible to do any the above, but the only alternative I can think of is to write code in Word to insert code in Excel and its VBE that builds a userform with its code to run in the XL app. Something I may spend weeks writing only to find it doesn't work anyway. Thanks for listening, Ken McLennan Qld, Australia |
#5
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general,microsoft.public.word.vba.userforms
|
|||
|
|||
Automated Forms issue
G'day there Jack,
* * * * I'm currently working on an automation project in which I have a report in Word that refers to data in various Excel worksheets which are of different layouts. The layouts are preset (by higher powers than my humble position) in the database report builder and I have no ability to influence the layouts or formats. To select which cells are referred to It sounds like you have a fun project to work on. I'm not sure my suggestions will help but I think I've had to do something similar in the past. You're right, it IS fun, but I'm certainly practising my vocabulary of swear words :) It might be quite a bit easier if I had a clue about what I was doing <g. The way I would do this is to not display the word document until the users were done selecting whatever value(s) they needed in Excel. I would then instantiate an instance of word within Excel VBA, use bookmarks to insert the values that were selected in excel, then finally open the word document for display. I've not ever tried anything like this while the word document was visible. Nodnodnod. I can understand your thinking. I much prefer to work in Excel's object model. Also, since the reports are exported as XL workbooks it's just plain common sense to use it. Usually I'm bound by what other people have designed and I'm stuck with trying adapt their documents. Maybe this time I can have a win!! If I can get it going properly I may be able to convince the powers that be to use what I come up with. I know it will be adopted at a local level since the people I work with will be pleased to have their job simplified. Those higher up tend to want their own name attached to any projects... especially successful ones. I may be stuck with Word eventually, but I'll have a go at trying to adapt the form to Excel. The main problem there will be trying to fit various arbitrary table formats into something consistent. I can easily write code for the formats I already know about, but I have to write something that non pooter-literate staff can use to suit any report formats that don't yet exist. Not impossible, but might be awkward. I'll have a look at it anyway and see what I can do while continuing with what I've started. I know this is not exactly what you want to do but perhaps by reducing the scope a bit you can get it done and move on rather than fight it for the rest of your career. Heeheechuckle!! My career is non existent. I program applications for personal satisfaction and to assist my co-workers :) ...sometimes people even say "Thankyou!!" Thanks for your suggestion, Take care, Ken McLennan Qld, Australia |
#6
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general,microsoft.public.word.vba.userforms
|
|||
|
|||
Automated Forms issue
G'day there JLGWhiz,
Check out ZOrder property and method in VBA help files. You could probably use that to maintain the window order. That's what I thought might work but I can't find it in my Help files. Well, perhaps I should qualify that... According to my help files, ZOrder belongs to Word, Excel & Forms. The Word files relate to drawing objects, the Excel to the order of series, and the Forms to grouping controls. I tried to use it with the Forms object, but it doesn't appear in the autolist that pops up and when I ran it with "me.ZOrder" it spat the dummy at me and causd a loud explosion with flames and debris all over the screen. Well, to qualify once again it actually just stopped and told me the method or data member wasn't found. Hence I'm assuming that it's not a part of what I have to work with. I'm guessing that it's probably in stand alone VB but not in Excel or Word :(. Thanks for helping See ya Ken McLennan Qld, Australia |
#7
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general,microsoft.public.word.vba.userforms
|
|||
|
|||
Automated Forms issue
G'day there JLGWhiz,
This might provide information more in the line you are looking for: http://allapi.mentalis.org/tips/tip4.shtml You're right, that might just be what I'm looking for! I might have to fiddle with where it's called, but it certainly looks workable. I'll take that code to work and play with it there. Just 'cause it works here at home doesn't mean that it will work at work <g. Thanks for helping, Ken McLennan Qld, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel forms - authorise / deny forms | Excel Programming | |||
Forms, Dropdowns, and Automated Information | Excel Discussion (Misc queries) | |||
Can This Be Automated? | Excel Programming | |||
RefEdits and normal forms / forms in a DLL | Excel Programming | |||
Calling Forms from Forms - Exit problems | Excel Programming |