PleaseGiveHelp
Programmer
What code do I need to access a directory on my PC and more specifically to look at the files within the directory?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to access directory structure via VB 1
- Thread starter PleaseGiveHelp
- Start date
- Status
-
1
- #2
The first step is to identify what direcrory on what drive you want to look into. Use the CHDrive and CHDir commands to set the drive and directory you want to look at. Recall from DOS that all directories have two objects in them that you won't be interested in. They are the "." and the ".." objects. Those need to be ignored.
To get a list of the files within a directory, you must look at each object in the directory, determine if it is the type of object you are interested in, and then do something with the information. This requires looping through all objects in the directory and ignoring the ones you don't care about (such as other directories). The following code runs through the MyDocuments directory on the C: drive. If it finds another directory, it does Nothing, if it finds a file it does Something
To get a list of the files within a directory, you must look at each object in the directory, determine if it is the type of object you are interested in, and then do something with the information. This requires looping through all objects in the directory and ignoring the ones you don't care about (such as other directories). The following code runs through the MyDocuments directory on the C: drive. If it finds another directory, it does Nothing, if it finds a file it does Something
Code:
ChDrive "C:"
Chdir "C:\MyDocuments"
PathName = "C:\MyDocuments"
FileName = Dir(PathName, vbDirectory) 'Retrieves the first entry in the folder
Do While FileName <> ""
If FileName <> "." And FileName <> ".." Then
If (GetAttr(PathName & FileName) And vbDirectory) = vbDirectory Then 'this checks to see if the object is a directory
Do Nothing
Else
Do Something
End If
End If
FileName = Dir 'this gets the next object in the folder
Loop- Thread starter
- #4
PleaseGiveHelp
Programmer
So if I want to look for all files that look like the first 9 characters of the file and merge them, how can I specify that?
The Filename variable will contain the name of the file. You could have a variable called first_nine that holds the first nine characters you want to match. Start by setting first_nine = "" before yo get into the Do ... Loop. Then when FileName is returned in the loop, use
to see if the first 9 characters of FileName equal the series of first 9 characters you are trying to match. ON the first one they won't, so you store the first nine from FileName to first_nine and start the merge process (isn't that described in another thread). On the next time through the loop, if there is a match, you continue the merge thing. If they do not, then you close and save the just merged file, then open a new merge file using the first nine from the FileName , being sure to save the first nine from the next file to the first_nine variable.
Code:
If Left(FileName,9) = first_nine- Thread starter
- #6
PleaseGiveHelp
Programmer
Can you look at my code and let me know if i am off to an ok start? I am stuck at this moment...
Function MergeDocs()
Dim strFileName As Variant
Dim Wrd As Word.Application
Dim newfilename As String
Dim originalfilename As Document
Dim nextfile As Document
Dim files As String
Dim i As Integer
Set Wrd = CreateObject("Word.Application"
ChDrive "F:"
ChDir "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
PathName = "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
FileName = Dir(PathName, vbDirectory) 'Retrieves the first entry in the folder
Do While FileName <> ""
If FileName <> "." And FileName <> ".." Then
If (GetAttr(PathName & FileName) And vbDirectory) <> vbDirectory Then 'makes sure the object is a file
'open file
'Somehow loop through file names and start with the first one (can we sort alphabetically?)
'first document open
Set originalfilename = FileName 'first document
Wrd.Documents.Open originalfilename, False, False, False, "", "", False, "", "", False, False, _
True, True
Set newfilename = Left(originalfilename, 9)
If Left(FileName, 9) = originalfilename Then
'if other documents exist with same 9 digit prefix (newfilename),
Wrd.Selection.EndKey wdStory
Wrd.Selection.InsertBreak wdSectionBreakNextPage
Wrd.Selection.InsertFile nextfile, "", False, False, False
'loop until no other files exist with same 9 digit prefix
Word.ActiveDocument.SaveAs newfilename.doc, wdFormatDocument, False, "", True, "", False, _
False, False, False
Else
Word.ActiveDocument.SaveAs newfilename.doc, wdFormatDocument, False, "", True, "", False, _
False, False, False
End If
Wrd.ActiveDocument.Close
'loop to open next document with diff 9 digit sequence
End If
End If
FileName = Dir 'this gets the next object in the folder
Loop
End Function
Function MergeDocs()
Dim strFileName As Variant
Dim Wrd As Word.Application
Dim newfilename As String
Dim originalfilename As Document
Dim nextfile As Document
Dim files As String
Dim i As Integer
Set Wrd = CreateObject("Word.Application"
ChDrive "F:"
ChDir "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
PathName = "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
FileName = Dir(PathName, vbDirectory) 'Retrieves the first entry in the folder
Do While FileName <> ""
If FileName <> "." And FileName <> ".." Then
If (GetAttr(PathName & FileName) And vbDirectory) <> vbDirectory Then 'makes sure the object is a file
'open file
'Somehow loop through file names and start with the first one (can we sort alphabetically?)
'first document open
Set originalfilename = FileName 'first document
Wrd.Documents.Open originalfilename, False, False, False, "", "", False, "", "", False, False, _
True, True
Set newfilename = Left(originalfilename, 9)
If Left(FileName, 9) = originalfilename Then
'if other documents exist with same 9 digit prefix (newfilename),
Wrd.Selection.EndKey wdStory
Wrd.Selection.InsertBreak wdSectionBreakNextPage
Wrd.Selection.InsertFile nextfile, "", False, False, False
'loop until no other files exist with same 9 digit prefix
Word.ActiveDocument.SaveAs newfilename.doc, wdFormatDocument, False, "", True, "", False, _
False, False, False
Else
Word.ActiveDocument.SaveAs newfilename.doc, wdFormatDocument, False, "", True, "", False, _
False, False, False
End If
Wrd.ActiveDocument.Close
'loop to open next document with diff 9 digit sequence
End If
End If
FileName = Dir 'this gets the next object in the folder
Loop
End Function
I'm working on the code and haven't sorted out all the logic yet, but there are few basic problems I see. The variables PathName and FileName need to be Dimmed as strings. I would suggest using something a little different such as currentpathname and currentfilename as strings. Also, the type document doesn't seem appropriate for originalfilename and nextfile, since that will dreat a type mismatch when you try to store a string to them or try to get the left 9 characters. They should probably be strings.
Are trying to run this from within Word?
Be specific about where you are stuck.
Are trying to run this from within Word?
Be specific about where you are stuck.
- Thread starter
- #8
PleaseGiveHelp
Programmer
Actually the database I'm using is in MS Access. So since MS Access already has the word object I decided to create a module for this code.
Would this be easier if the file names were stored in a database because I have that option as well - as opposed to looking at the directory structure.
This seems to make more sense in my head than it does out on paper - I'm not a VB programmer so I guess thats why I'm stuck.
I went ahead and changed the above variables to strings.
At what point should I be making filename = nine_code?
Function MergeDocs()
Dim strFileName As Variant
Dim Wrd As Word.Application
Dim new_file As String
Dim nine_code As String
Dim insert_file As String
Dim PathName As String
Dim FileName As String
Set Wrd = CreateObject("Word.Application"
Set nine_code = ""
ChDrive "F:"
ChDir "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
PathName = "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
FileName = Dir(PathName, vbDirectory) 'Retrieves the first entry in the folder
Do While FileName <> ""
If FileName <> "." And FileName <> ".." Then
If (GetAttr(PathName & FileName) And vbDirectory) <> vbDirectory Then 'makes sure the object is a file
'open file
'Somehow loop through file names and start with the first one (can we sort alphabetically?)
'first document open
Set nine_code = FileName 'first document
Wrd.Documents.Open nine_code, False, False, False, "", "", False, "", "", False, False, _
True, True
Set new_file = Left(nine_code, 9)
'look to go through other documents
'if other documents exist with same 9 digit prefix (newfilename),
If Left(FileName, 9) = nine_code Then
Wrd.Selection.EndKey wdStory
Wrd.Selection.InsertBreak wdSectionBreakNextPage
Wrd.Selection.InsertFile insert_file, "", False, False, False
'loop until no other files exist with same 9 digit prefix
Word.ActiveDocument.SaveAs new_file.doc, wdFormatDocument, False, "", True, "", False, _
False, False, False
Else
Word.ActiveDocument.SaveAs new_file.doc, wdFormatDocument, False, "", True, "", False, _
False, False, False
End If
Wrd.ActiveDocument.Close
'loop to open next document with diff 9 digit sequence
End If
End If
FileName = Dir 'this gets the next object in the folder
Loop
End Function
Would this be easier if the file names were stored in a database because I have that option as well - as opposed to looking at the directory structure.
This seems to make more sense in my head than it does out on paper - I'm not a VB programmer so I guess thats why I'm stuck.
I went ahead and changed the above variables to strings.
At what point should I be making filename = nine_code?
Function MergeDocs()
Dim strFileName As Variant
Dim Wrd As Word.Application
Dim new_file As String
Dim nine_code As String
Dim insert_file As String
Dim PathName As String
Dim FileName As String
Set Wrd = CreateObject("Word.Application"
Set nine_code = ""
ChDrive "F:"
ChDir "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
PathName = "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
FileName = Dir(PathName, vbDirectory) 'Retrieves the first entry in the folder
Do While FileName <> ""
If FileName <> "." And FileName <> ".." Then
If (GetAttr(PathName & FileName) And vbDirectory) <> vbDirectory Then 'makes sure the object is a file
'open file
'Somehow loop through file names and start with the first one (can we sort alphabetically?)
'first document open
Set nine_code = FileName 'first document
Wrd.Documents.Open nine_code, False, False, False, "", "", False, "", "", False, False, _
True, True
Set new_file = Left(nine_code, 9)
'look to go through other documents
'if other documents exist with same 9 digit prefix (newfilename),
If Left(FileName, 9) = nine_code Then
Wrd.Selection.EndKey wdStory
Wrd.Selection.InsertBreak wdSectionBreakNextPage
Wrd.Selection.InsertFile insert_file, "", False, False, False
'loop until no other files exist with same 9 digit prefix
Word.ActiveDocument.SaveAs new_file.doc, wdFormatDocument, False, "", True, "", False, _
False, False, False
Else
Word.ActiveDocument.SaveAs new_file.doc, wdFormatDocument, False, "", True, "", False, _
False, False, False
End If
Wrd.ActiveDocument.Close
'loop to open next document with diff 9 digit sequence
End If
End If
FileName = Dir 'this gets the next object in the folder
Loop
End Function
PGHA,
This works for me. I run it with Word already open, but it has to have a document open to start(which you could fix by checking for an ActiveDocument first). Also, the
statements will save the merged file with the same name as the very first file in the series, which you probably don't want. That should be changed to save the merged file with a new name and perhaps a different path.
This works for me. I run it with Word already open, but it has to have a document open to start(which you could fix by checking for an ActiveDocument first). Also, the
Code:
Application.Documents.Close True
Code:
Function MergeDocs()
Dim strcurrentfilename As Variant
Dim Wrd As Word.Application
Dim newcurrentfilename As String
Dim firstnine As String
Dim originalfilename As String
Dim lastfile As String
Dim files As String
Dim i As Integer
Dim currentpathname, currentfilename As String
Set Wrd = CreateObject("Word.Application")
'ChDrive "F:"
'ChDir "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
'currentpathname = "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
currentfilename = Dir(currentpathname, vbDirectory) 'Retrieves the first entry in the folder
lastfile = ""
Do While currentfilename <> ""
If currentfilename <> "." And currentfilename <> ".." Then
If (GetAttr(currentpathname & currentfilename) And vbDirectory) <> vbDirectory Then 'makes sure the object is a file
If Right(currentfilename, 3) = "doc" Then
'If first 9 chars of current file name ARE NOT the same as the last one
If Left(currentfilename, 9) <> lastfile Then
'save and close any open file
Application.Documents.Close True
'open the next document
Application.Documents.Open currentpathname & currentfilename
'documents has same 9 digit prefix (lastfile),
Else
'If first 9 chars of current file name ARE the same as the last one
Selection.EndKey wdStory
Selection.InsertBreak wdSectionBreakNextPage
'Application.Documents.Open currentpathname & currentfilename
Selection.InsertFile currentfilename, "", False, False, False
End If
'loop to open next document with diff 9 digit sequence
End If
End If
End If
lastfile = Left(currentfilename, 9)
currentfilename = Dir 'this gets the next object in the folder
Loop
'this closes the final document
Application.Documents.Close True
End Function- Thread starter
- #10
PleaseGiveHelp
Programmer
I am going to look at your code now. I do have one question. Am I correct to create this as a module in Access? If so I need to then create a macro which will run the module. How do I do this? Or should I create an executable?
I didn't know you were working this from within Access. I'm personnaly having trouble opening files in other MS Office applications but I have not tried it from Access.
I wouldn't store the file names in a database for 2 reasons. First, that's something extra for you to have to tell Access to do and if your not a VB programmer, why bother with it. Second, the file names may be changed by others without the database getting updated. Live data from the file structure is the better way to go.
The last code I gave you works fine within Word, but within Access, you'll have to replace the Application... with the Wrd.
I wouldn't store the file names in a database for 2 reasons. First, that's something extra for you to have to tell Access to do and if your not a VB programmer, why bother with it. Second, the file names may be changed by others without the database getting updated. Live data from the file structure is the better way to go.
The last code I gave you works fine within Word, but within Access, you'll have to replace the Application... with the Wrd.
- Thread starter
- #12
PleaseGiveHelp
Programmer
Will your code loop through the different filenames looking for the 9digit code prefix or just through all filenames in general?
The no-code solution is to start it from a macro. Open a New macro. Choose RunCode in the Actions. In the Function Name type MergeDocs(). Save the macro. How do you plan to start the macro?
If you have problems running the code from within Access let me know and I'll help you work them out. The logic is sound but the calls to the Word application may be a bit of a problem.
Just for my own edification, why are you performing this action from Access?
If you have problems running the code from within Access let me know and I'll help you work them out. The logic is sound but the calls to the Word application may be a bit of a problem.
Just for my own edification, why are you performing this action from Access?
- Thread starter
- #14
PleaseGiveHelp
Programmer
I was only performing this action from access because i had included the word object but i guess there's no reason why i couldn't put this into word as you did.
if i use word, i assume i'd open up VB editor within word and create a module?
if i use word, i assume i'd open up VB editor within word and create a module?
- Thread starter
- #15
PleaseGiveHelp
Programmer
Well it did something. I modified your code slightly so that when it opened a file it did a save as before closing (regardless of whether it merged with files). the only thing is...i can tell that something was done with these files because the file size has increased. 2 files of 20 kilobytes merge to make 1 file of 38 kilobytes. the thing is when i open the new file it looks like the original. as if it didn't merge. but i have a feeling it did and i just can't see the merged data. did you come across this?
- Thread starter
- #16
PleaseGiveHelp
Programmer
It worked the first time. Now I get an error:
Run time error 5174: This file could not be found. Try one or more of the following:
* Check the spelling of the name of the document
* Try a different name.
This error occurs when the macro finds a file that has the same 9 digit code sequence...
I don't understand why it worked the first time and now doesnt....
The first few files are:
000001_ML_strong.doc
000001_VG_hall.doc
000001_VG_nichels.doc
000001_VG_soper.doc
When it gets to the 000001_VG_nichels.doc file it bombs out. Why?
Run time error 5174: This file could not be found. Try one or more of the following:
* Check the spelling of the name of the document
* Try a different name.
This error occurs when the macro finds a file that has the same 9 digit code sequence...
I don't understand why it worked the first time and now doesnt....
The first few files are:
000001_ML_strong.doc
000001_VG_hall.doc
000001_VG_nichels.doc
000001_VG_soper.doc
When it gets to the 000001_VG_nichels.doc file it bombs out. Why?
- Thread starter
- #17
PleaseGiveHelp
Programmer
Ok so i was getting that error because I hadn't specified the directory. It works kindof. But I need some help with my inside loop (looking for files that have the same first 9 characters). It seems to get to some of those that have them but not others...can you take a look at my loop?
Sub Merge()
'
' Merge Macro
' Macro created 10/22/2003 by ssga
'
Dim strcurrentfilename As Variant
Dim Wrd As Word.Application
Dim newcurrentfilename As String
Dim firstnine As String
Dim originalfilename As String
Dim lastfile As String
Dim files As String
Dim i As Integer
Dim currentpathname, currentfilename As String
Set Wrd = CreateObject("Word.Application"
ChDrive "F:"
ChDir "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
currentpathname = "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
currentfilename = Dir(currentpathname, vbDirectory) 'Retrieves the first entry in the folder
lastfile = ""
Do While currentfilename <> ""
If currentfilename <> "." And currentfilename <> ".." Then
If (GetAttr(currentpathname & currentfilename) And vbDirectory) <> vbDirectory Then 'makes sure the object is a file
If Right(currentfilename, 3) = "doc" Then
'If first 9 chars of current file name ARE NOT the same as the last one
If Left(currentfilename, 9) <> lastfile Then
'save and close any open file
Word.ActiveDocument.SaveAs Left(currentfilename, 9), wdFormatDocument, False, "", True, "", False, _
False, False, False
Application.Documents.Close True
'open the next document
Application.Documents.Open currentpathname & currentfilename
'documents has same 9 digit prefix (lastfile),
Else
'If first 9 chars of current file name ARE the same as the last one
Selection.EndKey wdStory
Selection.InsertBreak wdSectionBreakNextPage
'Application.Documents.Open currentpathname & currentfilename
Word.ActiveDocument.SaveAs Left(currentfilename, 9), wdFormatDocument, False, "", True, "", False, _
False, False, False
Selection.InsertFile (currentpathname & currentfilename), "", False, False, False
End If
'loop to open next document with diff 9 digit sequence
End If
End If
End If
lastfile = Left(currentfilename, 9)
currentfilename = Dir 'this gets the next object in the folder
Loop
'this closes the final document
Application.Documents.Close True
End Sub
Sub Merge()
'
' Merge Macro
' Macro created 10/22/2003 by ssga
'
Dim strcurrentfilename As Variant
Dim Wrd As Word.Application
Dim newcurrentfilename As String
Dim firstnine As String
Dim originalfilename As String
Dim lastfile As String
Dim files As String
Dim i As Integer
Dim currentpathname, currentfilename As String
Set Wrd = CreateObject("Word.Application"
ChDrive "F:"
ChDir "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
currentpathname = "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
currentfilename = Dir(currentpathname, vbDirectory) 'Retrieves the first entry in the folder
lastfile = ""
Do While currentfilename <> ""
If currentfilename <> "." And currentfilename <> ".." Then
If (GetAttr(currentpathname & currentfilename) And vbDirectory) <> vbDirectory Then 'makes sure the object is a file
If Right(currentfilename, 3) = "doc" Then
'If first 9 chars of current file name ARE NOT the same as the last one
If Left(currentfilename, 9) <> lastfile Then
'save and close any open file
Word.ActiveDocument.SaveAs Left(currentfilename, 9), wdFormatDocument, False, "", True, "", False, _
False, False, False
Application.Documents.Close True
'open the next document
Application.Documents.Open currentpathname & currentfilename
'documents has same 9 digit prefix (lastfile),
Else
'If first 9 chars of current file name ARE the same as the last one
Selection.EndKey wdStory
Selection.InsertBreak wdSectionBreakNextPage
'Application.Documents.Open currentpathname & currentfilename
Word.ActiveDocument.SaveAs Left(currentfilename, 9), wdFormatDocument, False, "", True, "", False, _
False, False, False
Selection.InsertFile (currentpathname & currentfilename), "", False, False, False
End If
'loop to open next document with diff 9 digit sequence
End If
End If
End If
lastfile = Left(currentfilename, 9)
currentfilename = Dir 'this gets the next object in the folder
Loop
'this closes the final document
Application.Documents.Close True
End Sub
It needed to have the full path and file name when it got to the first different file name. The place where the merged files are saved also needs to be specified, otherwise the merged files could end up being inserted back into themselves. To do that I added a variable for storagepath (which you will need to identify, and must be somewhere other than the path stored as currentpathname).
Code:
Function MergeDocs()
Dim strcurrentfilename As Variant
Dim Wrd As Word.Application
Dim newcurrentfilename As String
Dim firstnine As String
Dim originalfilename As String
Dim lastfile As String
Dim files As String
Dim i As Integer
Dim currentpathname, currentfilename, storagepath As String
Set Wrd = CreateObject("Word.Application")
ChDrive "F:"
ChDir "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
currentpathname = "F:\HNW-IT\Team Folders\Ursula\ConsentTesting\Files\"
storagepath = needfoldername
currentfilename = Dir(currentpathname, vbDirectory) 'Retrieves the first entry in the folder
lastfile = ""
Do While currentfilename <> ""
If currentfilename <> "." And currentfilename <> ".." Then
If (GetAttr(currentpathname & currentfilename) And vbDirectory) <> vbDirectory Then 'makes sure the object is a file
If Right(currentfilename, 3) = "doc" Then
'If first 9 chars of current file name ARE NOT the same as the last one
If Left(currentfilename, 9) <> lastfile Then
'save and close any open file
If lastfile = "" Or lastfile = "." Or lastfile = ".." Then
Application.Documents.Close False
Else
Application.ActiveDocument.SaveAs storagepath & lastfile
Application.ActiveDocument.Close
'open the next document
End If
Application.Documents.Open currentpathname & currentfilename
'documents has same 9 digit prefix (lastfile),
Else
'If first 9 chars of current file name ARE the same as the last one
Selection.EndKey wdStory
Selection.InsertBreak wdSectionBreakNextPage
'Application.Documents.Open currentpathname & currentfilename
Selection.InsertFile currentpathname & currentfilename
'Selection.InsertFile currentfilename, "", False, False, False
End If
'loop to open next document with diff 9 digit sequence
End If
End If
End If
lastfile = Left(currentfilename, 9)
currentfilename = Dir 'this gets the next object in the folder
Loop
Application.ActiveDocument.SaveAs storagepath & lastfile
Application.ActiveDocument.Close
End Function- Thread starter
- #19
PleaseGiveHelp
Programmer
All I did was switch the insert file and the save as section and it works perfectly! thank you so much for your help! i appreciate all of it. this works perfectly!
The best way to run the code is to run it from the menus. On the Tools menu, select Macro, then Visual Basic Editor. Insert a module under the Normal document in its Modules folder. The past the code into the module. Then change it from a Function to a Sub procedure (the Macro menu doesn't show functions). Then you can run it from the Tools Menu by Selecting Tools, Macro, Macros.
You could also put the Run Macro button on the toolbar, or create a custom button to run just the Merge procedure.
You could also put the Run Macro button on the toolbar, or create a custom button to run just the Merge procedure.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question