Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.word.programming
|
|||
|
|||
Running Word (and Excel) code from within Excel, to process both file types
I'm running Windows XP, Excel 2003 and Word 2003.
I have written two sets of code - one to be run from Excel, stored in modules in an Excel workbook, and another that is stored in modules in a Word document. Each set basically does the same thing: - prompts the user for a folder on a network share; - searches that folder (and its sub-folders, if specified) for Excel workbooks / Word docs (as appropriate); - opens each file in sequence (only if a password is not required to open); - checks headers and footers (for each worksheet and chart sheet in Excel; for each section in Word) for existing text; - edits existing header/footer text or adds header/footer where none exists as appropriate, and under certain circumstances; - saves and closes the workbook / document, if changed; - adds a hyperlinked filename and path for each file checked to a listing in a worksheet in the Excel code workbook (for the Excel files) / on the page in the Word code document (for the Word files) with a comment as to whether the file was changed; - moves onto the next file; - at the end, each set of code shows a summary message box with the total files checked etc. Each set of code runs fine from within Excel / Word, but ideally what I'd like to do is to run one set of code from Excel, and have this check all Excel and Word files in the specified location, do the editing as necessary, and provide a full list on the Excel worksheet. This will mean running the Word VBA code that manipulates the Word documents from within Excel. I know that this is possible using early binding (preferable - I've seen an example here http://word.mvps.org/FAQs/InterDev/C...WordFromXL.htm ). My queries: 1. Once I've Dim'd the Word objects at the top as per: Dim oWord As Word.Application Dim oDoc As Word.Document (plus any other string / Boolean etc references that I use in the Word code), can I literally just copy the Word code into the Excel module? 2. A I am opening more than one Word doc in sequence, would it be preferable to open a new instance of Word app for each Word doc that I check, or close just the document each time and keep an empty Word app running ready for the next Word doc? 3. Also, I know that for early binding I need to set a reference to MS Word 11.0 Word Object Library in Excel's VBE via Tools | References. Is this a setting that is "saved" with the workbook containing the code (I want to distribute it for others to use), or will each user have to set this reference? I'd rather keep users well clear of the VBE. (If necessary, can this setting be made via VBA?) Grateful for any other general advice. I've not posted any code, but I could do this if it helps. As I say, each set runs perfectly well in its own app, checking for its own type of file, I'd just rather push it all into Excel. In both cases I'm using Application.Filesearch to search for the files to process. As I see it, there would be one instance of Application.Filesearch and this would then branch to the Excel code to process the Excel files, or to the Word code for the Word files, but for there to just the one list/report for all the files, and for this to be on an Excel worksheet. Many thanks in advance. Ian |
#2
Posted to microsoft.public.excel.programming,microsoft.public.word.programming
|
|||
|
|||
Running Word (and Excel) code from within Excel, to process both file types
"IanKR" wrote in message
My queries: 1. Once I've Dim'd the Word objects at the top as per: Dim oWord As Word.Application Dim oDoc As Word.Document (plus any other string / Boolean etc references that I use in the Word code), can I literally just copy the Word code into the Excel module? a) Fully qualify all "implicit" object references back to the reference to the Word application. b) Best to fully declare Word object declarations, particularly "Range" which of could easily get confused Instead of - Dim oDoc As Document Set oDoc = ActiveDocument ' implicit do - Dim oDoc As Word.Document Set oDoc = oWord.ActiveDocument ' qualified 2. A I am opening more than one Word doc in sequence, would it be preferable to open a new instance of Word app for each Word doc that I check, or close just the document each time and keep an empty Word app running ready for the next Word doc? If you are about to work on a new document stick with the same instance. If not sure quit the instance, after ensuring you have released and object references. 3. Also, I know that for early binding I need to set a reference to MS Word 11.0 Word Object Library in Excel's VBE via Tools | References. Is this a setting that is "saved" with the workbook containing the code (I want to distribute it for others to use), or will each user have to set this reference? I'd rather keep users well clear of the VBE. (If necessary, can this setting be made via VBA?) The reference will be saved. If there is any possibility a user with an earlier version of Office will use your file you will either need to convert to Late-Binding, or ensure the reference is always saved with the lowest version of any user. Keep in mind the reference might get resaved and changed a newer version if the file is exchanged between users. I didn't follow what you wrote about FileSearch. Regards, Peter T |
#3
Posted to microsoft.public.excel.programming,microsoft.public.word.programming
|
|||
|
|||
Running Word (and Excel) code from within Excel, to process both file types
My queries:
1. Once I've Dim'd the Word objects at the top as per: Dim oWord As Word.Application Dim oDoc As Word.Document (plus any other string / Boolean etc references that I use in the Word code), can I literally just copy the Word code into the Excel module? a) Fully qualify all "implicit" object references back to the reference to the Word application. b) Best to fully declare Word object declarations, particularly "Range" which of could easily get confused Instead of - Dim oDoc As Document Set oDoc = ActiveDocument ' implicit do - Dim oDoc As Word.Document Set oDoc = oWord.ActiveDocument ' qualified Thanks - understood. 2. A I am opening more than one Word doc in sequence, would it be preferable to open a new instance of Word app for each Word doc that I check, or close just the document each time and keep an empty Word app running ready for the next Word doc? If you are about to work on a new document stick with the same instance. If not sure quit the instance, after ensuring you have released and object references. OK. I'll probably keep the same instance throughout and then close it at the end. 3. Also, I know that for early binding I need to set a reference to MS Word 11.0 Word Object Library in Excel's VBE via Tools | References. Is this a setting that is "saved" with the workbook containing the code (I want to distribute it for others to use), or will each user have to set this reference? I'd rather keep users well clear of the VBE. (If necessary, can this setting be made via VBA?) The reference will be saved. If there is any possibility a user with an earlier version of Office will use your file you will either need to convert to Late-Binding, or ensure the reference is always saved with the lowest version of any user. Keep in mind the reference might get resaved and changed a newer version if the file is exchanged between users. That's a relief! We're all using Office 2003, so there's no issue here. I didn't follow what you wrote about FileSearch. Sorry, I rather hurried this bit. My point was that at the moment, in the Excel code I do an Application.FileSearch and look just for .xls files and process those. Then when I run the Word code (in MS Word) I also do an Application.FileSearch and look just for .doc files, and process those. In my unified code running in MS Excel I will do an Application.FileSearch and look for .xls AND .doc files .This is possible using the .FileType method to choose one, and then the .FileTypes.Add method to choose the second one. E.g.: Set fs = Application.FileSearch With fs .LookIn = strPathref .FileType = msoFileTypeExcelWorkbooks ' will search for Excel files .FileTypes.Add msoFileTypeWordDocuments 'will also search for Word files If .Execute 0 Then 'if any files are found For i = 1 To .FoundFiles.Count If .FoundFiles(i).Creator = wdCreatorCode Then 'run Word VB code to process the .Foundfiles(i) Word file End If If .FoundFiles(i).Creator = xlCreatorCode Then 'run Excel VB code to process the .FoundFiles(i) Excel file End If Next i Else MsgBox "There were no Excel or Word files found." End If End With Many thanks for your help, Peter. Ian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Starting excel and running it on the same process | Excel Programming | |||
Excel Process Still Running | Excel Programming | |||
another macro is running ... how to recognise that process in code??? | Excel Programming | |||
Excel window closed but process still running | Excel Programming | |||
Excel process remains running | Excel Programming |