Erikwe
10-17-2007, 01:08 AM
Hi.
I'm pretty new to vba and not a programmer and could use some help with a macro I am writing. In trying to follow the guidlines on how to get help i give you this:
I want my macro to do a file search and in the found files (ending with a number and ".htm") it shall extract the information I need, paste into a new document and save. I have adapted snippets of code for different methods from the web and a few recorded macros to get this done and predictably it doesn't work. That's normally ok, I usually get it sorted, only this time I don't. I use Word 2003.
The problems I am not able to solve are two:
1). When using Application.filesearch I try searching for .filename = "*[0-9].htm". It doesn't find any files doing this and it seems the only wildcard it accepts is the '*'. If that's the case then I can probably work around this (in some extremely roundabout way) if I just get the rest to work.
2). When the macro opens a found document it is supposed to search for the phrase "Fixation indices". Only this part doesn't do anything at all. I may be wrong about this but I suspect it's because I don't have the document active. I'm not able to activate the correct window 'cause I can't get the name of it from .FoundFiles.
Edit: Have solved no 2 with some help.
If someone would take a look at "my" macro I would be very grateful.
Ok... it is with great hesitation that I now present you with the code (don't laugh):
Sub locusbylocusextract()
'
' locusbylocusextract Makro
' Makro erstellt am 15.10.2007 von myself
' Including a general macro to do things with lots of files
Set fs = Application.FileSearch
Dim newbook, curbook, MyCount, myrange
Location = BrowseForFolder
Generic = InputBox("Please enter a name of the results file", "Enter file name")
With Documents.Add
.SaveAs FileName:=(Location & "\" & Generic & ".doc")
.Close
End With
With fs
'Browse for a location. Will open a set folder dialogue. See function BrowseForFolder below (not pasted)
.LookIn = Location
.SearchSubFolders = True 'you want to search subfolders
.FileName = "*.htm" 'filename ending with ".htm". Wildcards other than '*' doesn't work
'if you are looking for other than HTM files, alter the line above
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
'do actions here - in my case, that would be open the file and get it active
Documents.Open FileName:=.FoundFiles(i)
-------
snip
-------
ActiveDocument.Close savechanges:=False 'Read only, no changes, so will be closed without saves
Next i
Else
MsgBox "No files found. Chose the wrong directory, didn't ya?"
End If
End With
MsgBox "Macro finished"
End Sub
(There are bound to be other problems with this macro but please ignore those if you can stomach them)
I would be grateful, too, if you deem it possible to do this macro along the methods I have used above. It would help my understanding. I don't insist on it though :P .
Cheers!
Erik
(I'm sorry the macro code takes so much space)
Edit: snipped some code. Less reason to be laughed at :P
I'm pretty new to vba and not a programmer and could use some help with a macro I am writing. In trying to follow the guidlines on how to get help i give you this:
I want my macro to do a file search and in the found files (ending with a number and ".htm") it shall extract the information I need, paste into a new document and save. I have adapted snippets of code for different methods from the web and a few recorded macros to get this done and predictably it doesn't work. That's normally ok, I usually get it sorted, only this time I don't. I use Word 2003.
The problems I am not able to solve are two:
1). When using Application.filesearch I try searching for .filename = "*[0-9].htm". It doesn't find any files doing this and it seems the only wildcard it accepts is the '*'. If that's the case then I can probably work around this (in some extremely roundabout way) if I just get the rest to work.
2). When the macro opens a found document it is supposed to search for the phrase "Fixation indices". Only this part doesn't do anything at all. I may be wrong about this but I suspect it's because I don't have the document active. I'm not able to activate the correct window 'cause I can't get the name of it from .FoundFiles.
Edit: Have solved no 2 with some help.
If someone would take a look at "my" macro I would be very grateful.
Ok... it is with great hesitation that I now present you with the code (don't laugh):
Sub locusbylocusextract()
'
' locusbylocusextract Makro
' Makro erstellt am 15.10.2007 von myself
' Including a general macro to do things with lots of files
Set fs = Application.FileSearch
Dim newbook, curbook, MyCount, myrange
Location = BrowseForFolder
Generic = InputBox("Please enter a name of the results file", "Enter file name")
With Documents.Add
.SaveAs FileName:=(Location & "\" & Generic & ".doc")
.Close
End With
With fs
'Browse for a location. Will open a set folder dialogue. See function BrowseForFolder below (not pasted)
.LookIn = Location
.SearchSubFolders = True 'you want to search subfolders
.FileName = "*.htm" 'filename ending with ".htm". Wildcards other than '*' doesn't work
'if you are looking for other than HTM files, alter the line above
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
'do actions here - in my case, that would be open the file and get it active
Documents.Open FileName:=.FoundFiles(i)
-------
snip
-------
ActiveDocument.Close savechanges:=False 'Read only, no changes, so will be closed without saves
Next i
Else
MsgBox "No files found. Chose the wrong directory, didn't ya?"
End If
End With
MsgBox "Macro finished"
End Sub
(There are bound to be other problems with this macro but please ignore those if you can stomach them)
I would be grateful, too, if you deem it possible to do this macro along the methods I have used above. It would help my understanding. I don't insist on it though :P .
Cheers!
Erik
(I'm sorry the macro code takes so much space)
Edit: snipped some code. Less reason to be laughed at :P