Help with Macro & Directory
 

Hi Guys,

I'm trying to run a macro that looks like this :

--------------------------------------------------------------------

Sub LoopMacroTest()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
..NewSearch
'Change path to suit
..LookIn = "C:\Documents and Settings\dhunter25\My Documents\Tests\GMS"
..FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls"

If .Execute 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(.FoundFiles(lCount))


Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Rows("1:5").Select
Range("A5").Activate
Selection.Delete Shift:=xlUp
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Range("D7").Select


wbResults.Close SaveChanges:=True


Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

-----------------------------------------------------------------------

The thing I want to edit is with regards to this line:

..LookIn = "C:\Documents and Settings\dhunter25\My Documents\Tests\GMS"



Rather than having a full directory path, i want to look in a folder called
GMS. This folder will be a subdirectory of the folder that this main macro
and workbook are stored in.

Can anyone tell me how to do this?



[If anyone wants to know, the macro will edit a folder full of excel
workbooks all at once by running a macro in another workbook]

Thanks!
Dave

Help with Macro & Directory
 

Dave,

Perhaps:

..LookIn = Thisworkbook.Path & "\GMS"


HTH,
Bernie
MS Excel MVP


"Dave" wrote in message
...
Hi Guys,

I'm trying to run a macro that looks like this :

--------------------------------------------------------------------

Sub LoopMacroTest()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\Documents and Settings\dhunter25\My Documents\Tests\GMS"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls"

If .Execute 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(.FoundFiles(lCount))


Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Rows("1:5").Select
Range("A5").Activate
Selection.Delete Shift:=xlUp
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Range("D7").Select


wbResults.Close SaveChanges:=True


Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

-----------------------------------------------------------------------

The thing I want to edit is with regards to this line:

.LookIn = "C:\Documents and Settings\dhunter25\My Documents\Tests\GMS"



Rather than having a full directory path, i want to look in a folder called
GMS. This folder will be a subdirectory of the folder that this main macro
and workbook are stored in.

Can anyone tell me how to do this?



[If anyone wants to know, the macro will edit a folder full of excel
workbooks all at once by running a macro in another workbook]

Thanks!
Dave