opening new files in a folder

[radio_man]

Hi,
Really new to scripting and jumping in head first. I'm sure I'll be nearly swimming after this though. I'm trying to create a macro that will detect when a file is created in a folder open it in MS access making some version conversions along the way and then export to a text file.
At first I thought I could just record it and then realized there were some variables involved and I'm not sure where to go from here.

Thanks

[Marcus Tettmar]

To do this I would make a macro which gets the latest file in the folder. See:
http://www.mjtnet.com/forum/viewtopic.php?p=8011

This gets the latest file. Add your code to it to open it in Access:

Run>c:\....\msaccess.exe %filename%

Now under Macro Properties/Trigger select "Folder Event" and "New File in Folder" and specify the folder path.

Now when a new file arrives in the folder, the macro is triggered, gets the latest file (therefore the new file) and opens it in Access.

[radio_man]

So I've set this up as follows:

VBSTART
Function NewestFile(\\sheila2\c\bsi32\logs)
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(\\sheila2\c\bsi32\logs)
dPrevDate = "0"
For Each oFile In oFolder.Files
If DateDiff("s", dPrevDate, oFile.DateLastModified) > 0 Then
sNewestFile = oFile.Path
dPrevDate = oFile.DateLastModified
End If
Next
NewestFile = sNewestFile
End Function
VBEND
VBEval>NewestFile("\\sheila2\c\bsi32\logs"),filename

Run>C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE %filename%

I'm getting a couple of errors, the first is:
Microsoft VBScript compilation error: 1010
Expected identifier
Line 2, Column 20

and, the second:

Microsoft VBScript runtime error:13
Type mismatch: 'NewestFile'
Line 2, Column 0

This is all really new to me so if there was something that I was supposed to know to change I apologize, I didn't/don't. Thanks so much for your help on this and if there is someplace I can go to enhance a better understaing of writing scripts I'd gladly receive the input.

[Marcus Tettmar]

You didn't need to change the VBScript code. This is what your script should look like:

Code: Select all

VBSTART
Function NewestFile(Folder)
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(Folder)
dPrevDate = "0"
For Each oFile In oFolder.Files
  If DateDiff("s", dPrevDate, oFile.DateLastModified) > 0 Then
    sNewestFile = oFile.Path
    dPrevDate = oFile.DateLastModified
  End If
Next
NewestFile = sNewestFile
End Function
VBEND

VBEval>NewestFile("\\sheila2\c\bsi32\logs"),filename

Run>"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "%filename%"

This will open the newest file in the folder in Access. Of course as it stands it does not care whether this is a mdb file or something else. But assuming you are only putting mdb files in this folder then it needs no modification. Just set it up on a trigger to activate when a new file appears in the folder.

[radio_man]

oh. ok.
Thanks.