Building a Access DB from text/word input

[Per Olsen]

Hi, I want to build some kind of register over the photos/videos that I have used in Windows Movie Maker, that saves files in raw text format (extension .vlmp).

My general idea is to use Word/Access VBA to run through my VLMP files, and verify the file location. Suplementray - if the files was move to a diffrent harddisk/folder the program must "analyze" the HD, and build an access table containing all photo/video files.

That is the photo/video file-names are uniqe, but I had to move them from my pc to an external harddisk...

VLMP format, may look like this:

<MediaItem id="17" filePath="D:\Videos\Kamera\201406\201406A0\030620142102.mp4" arWidth="1280" arHeight="720" duration="28.639099999999999" songTitle="" songArtist="" songAlbum="" />

my program should then register the pathName "D:\Videos\Kamera\201406\201406A0\" and fileName: "030620142102.mp4"

.... when the pathsName\fileName does not exist, the program should simply suggest a new pathName and perhaps modify the VLMP file and save it with a new name.

Access/Word/FilePath?

Off the top of my head I assume that I need to use a mix of VBA functions, and I am quite familliar with programming (I have done a bit of VBA sample modifications).


What I need is some "expert direction" as to where to "start out":

• Is this an Access application?
• What VBA libaries do I need?
• Where do I find sample code that runs through a text/file, or would you suggest I use Word-VBA commands?

Best regards
Per Olsen

[jonh]

•Is this an Access application?
Sure, you could use Access to make it. Your final application might be a bit clunky (if you're going to be searching your entire hard drive on a regular basis, I'd be tempted to download vbexpress and build a service to run in the background) but it's alright at storing data.

•What VBA libaries do I need?
Just standard stuff. I prefer to use the FileSystemObject for file based stuff in vba though.

•Where do I find sample code that runs through a text/file
Here. Below. Google.

•or would you suggest I use Word-VBA commands?
No

To search an entire drive or a branch of sub folders you use recursion.

recursiveSearch CreateObject("Scripting.FileSystemObject").GetFolder("C:\")

Sub recursiveSearch(folPath)
 GetFiles folPath
    For Each folder In folPath.SubFolders
  recursiveSearch folder
  doevents
    Next
End Sub

otherwise you can just use

sub GetFiles(folder)
 'using dir() with a filter instead of a for loop might be quicker here
    For Each file In folder.files
  if lcase(Right(file.Name, 5)) <> ".vlmp" then AddToDB file.Name
    Next
End sub

Read the file.

sub AddToDB(strFile as string)
 ary = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(strFile).ReadAll(), vbCrLf)
 For each s in UBound(ary)
  If InStr(1, s, "filePath=", vbTextCompare) Then
   'trim/split the path
   '?write out to temp csv for import? or...
   'check if file exists in table / path correct ?
   '   if not add it / fix it
   'etc
  End If
 Next
end sub

[Per Olsen]

Thanks Jonh..
I was just wondering if there is ways to use Words-VBA interface, to finde/replace (modify/update) in the .vlmp file once found. But perhaps its bette to use standard VBA functions.
(I may not need to scan the entire hd, but "entire" folder structures... )

I will need one table for the correct media file locations, and an other for the the .vlmp files and their relations. Im rather familliar with SQL etc. so once I get some clue on how to run this I guss the we are talking about a rather simple program.
I would prefer to run this in Access, for the simple reason that it makes it easy for me to view the tables on the fly.

Anyway - Very usefull samples.. I will post the code when I get it done.

Regards Per