Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Code for a directory | Excel Discussion (Misc queries) | |||
Macro - Open all word files in a directory | Excel Worksheet Functions | |||
Directory | Excel Discussion (Misc queries) | |||
Directory listing | Excel Discussion (Misc queries) | |||
How do I force an Excel macro to ask me which file and directory? | Excel Discussion (Misc queries) |