Macro - Open all word files in a directory

[Dileep Chandran]

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

 

[Bob Phillips]

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]

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

 

[Dileep Chandran]

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

 

[Bob Phillips]

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]

Bob,

Its showing a syntax error in

Set oWB = Workbooks.Open Filename:=file.Path

-Dileep

 

[Bob Phillips]

Why are you trying to open word documents in Excel?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dileep Chandran]

I want to copy and paste some data from word file to an the excel file.

Isnt possible? Thanks for your help

-Dileep

 

[Bob Phillips]

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]

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

 

[Nick Hodge]

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
(e-mail address removed)
www.nickhodge.co.uk

 

[Bob Phillips]

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)