Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Hi All,
I need a macro to open all word files in a directory, do a particular function, and close the file. I have to run this from an excel file. can anyone help? -Dileep |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Dim oFSO Sub LoopFolders() Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object Dim file As Object Dim fldr dim oWB as Workbook Set Folder = oFSO.GetFolder(sPath) For Each fldr In Folder.Subfolders selectFiles fldr.Path Next fldr For Each file In Folder.Files If file.Type Like "*Excel*Worksheet" Then Set oWB = Workbooks.Open Filename:=file.Path 'do your stuff oWB.save oWB.Close End If Next file -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dileep Chandran" wrote in message oups.com... Hi All, I need a macro to open all word files in a directory, do a particular function, and close the file. I have to run this from an excel file. can anyone help? -Dileep |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Hi Bob,
This is not working for me. I have to open all word files (.doc) in a directory (C:\test), do a particular function, and close it and open the next file. Any idea how to do it? Is my question clear? -Dileep |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Hi Bob,
This is not working for me. I have to open all word files (.doc) in a directory (C:\test), do a particular function, and close it and open the next file. Any idea how to do it? Is my question clear? -Dileep |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Replace
If file.Type Like "*Excel*Worksheet" Then with If file.Type Like "*Word*Document" Then -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dileep Chandran" wrote in message ups.com... Hi Bob, This is not working for me. I have to open all word files (.doc) in a directory (C:\test), do a particular function, and close it and open the next file. Any idea how to do it? Is my question clear? -Dileep |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Bob,
Its showing a syntax error in Set oWB = Workbooks.Open Filename:=file.Path -Dileep |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Why are you trying to open word documents in Excel?
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dileep Chandran" wrote in message ups.com... Bob, Its showing a syntax error in Set oWB = Workbooks.Open Filename:=file.Path -Dileep |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
I want to copy and paste some data from word file to an the excel file. Isnt possible? Thanks for your help -Dileep |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
I think that you would need to open the word document within Word and copy
and then paste to excel. This would involve running it from Word and starting an instance of Excel, or vice versa. Does that mean anything to you, or is it above your capability? If the latter, how will you determine what is to be copied from the Word doc (Word is not my forte really)? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dileep Chandran" wrote in message ps.com... I want to copy and paste some data from word file to an the excel file. Isnt possible? Thanks for your help -Dileep |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Hi Bob, I dont have much expertise in macro and VB, but if I get the code to open a .doc file in a particular location, I can go ahead with the balance. Any way, thank you for your help. Revert back if you have any bright ideas Thanks & Regards Dileep |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Dileep
If you are just looking to automate Word, then, after setting a reference to Microsoft Word library (as I use early binding), in the Excel VBE under Tools (Code not tested) Sub OpenWordandDoc() Dim wdApp as Word.Application Dim wdDoc as Word.Document Set wdApp=New Word.Application Set wdDoc=WdApp.Open("C:\Test.doc") wdapp.Visible=True 'Do your stuff here wdDoc.Close SaveChanges:=True Set wdDoc=Nothing wdApp.Quit Set wdApp=Nothing End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Dileep Chandran" wrote in message ups.com... Hi Bob, I dont have much expertise in macro and VB, but if I get the code to open a .doc file in a particular location, I can go ahead with the balance. Any way, thank you for your help. Revert back if you have any bright ideas Thanks & Regards Dileep |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro - Open all word files in a directory
Personally, I would use late binding, a bit simpler
Dim wdApp As Object Dim wdDoc As Object 'before the loop start Set wdApp = CreateObject("Word.Application") wdApp.Visible = True 'then within the loop Set wdDoc = wdApp.Open(file.Path) 'Do your stuff here on wdDoc wdDoc.Close SaveChanges:=True 'and after the loop Set wdDoc = Nothing wdApp.Quit Set wdApp = Nothing -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Nick Hodge" wrote in message ... Dileep If you are just looking to automate Word, then, after setting a reference to Microsoft Word library (as I use early binding), in the Excel VBE under Tools (Code not tested) Sub OpenWordandDoc() Dim wdApp as Word.Application Dim wdDoc as Word.Document Set wdApp=New Word.Application Set wdDoc=WdApp.Open("C:\Test.doc") wdapp.Visible=True 'Do your stuff here wdDoc.Close SaveChanges:=True Set wdDoc=Nothing wdApp.Quit Set wdApp=Nothing End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Dileep Chandran" wrote in message ups.com... Hi Bob, I dont have much expertise in macro and VB, but if I get the code to open a .doc file in a particular location, I can go ahead with the balance. Any way, thank you for your help. Revert back if you have any bright ideas Thanks & Regards Dileep |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
how do you get Word to open documents in it's own window(s)? | Excel Discussion (Misc queries) | |||
in an excel macro can you import data from word into a cell? | Excel Discussion (Misc queries) | |||
file open via IE hyperlink causes already open files to shrink and tile | Setting up and Configuration of Excel | |||
Open Word Doc From Excel Macro | Excel Discussion (Misc queries) |