Linking all files

[DaLiMan]

Hi,

I'm looking for a way to automate the task of linking files (especially NEW files in the directory)
Is there a way to create a macro or something that checks all .txt files in a specific directory and compares if they are already linked.
If they are not linked do this automatically in a new table in the Access Database.

Sinds I have no way of telling when a new file is added in this directory I need to check all by hand. Now I'm not very happy with this as you can understand. I really hope someone can help me with this.

 

[mdmilner]

There are three pieces to the answer, which is Yes.
A macro is unlikely to have the flexibility you need, but a VBA module can do it rather easily.

First, here is a technique for checking files for changes.
I've only tested it to see how easy it is to use (easy), not implemented it but I plan on doing so at some point.

http://www.mvps.org/access/modules/mdl0053.htm

Second, It's possible to use API calls to grab filenames for import into a database. The above file change checker (haven't looked today) *must* have the technique built into it or it'd logically never work.

Third, specific code syntax. You can choose acLink as an option and it links spreadsheets. If I get a chance later today I'll post an integrated solution, but I know a few of the frequent posters have put up the technique I'll do, so if you have it already done and easy to paste in, please feel free.

DoCmd.TransferSpreadsheet

Mike

 

[DaLiMan]

Thanx Mike,

I downloaded the file for testing, but I keep getting an error like:
Error429:
AtiveX component can't create object.

I had to download regsvr.exe to register the supplied dll and still I keep getting this error.

Can you help me get this one to work, prefered in Access so I don't have to use an external program?

 

[mdmilner]

Ok, I was wrong - I had a simpler way tested and figured out using the filesystem object.

Here's a quick demo showing how to pull up a list of files in a given hardcoded folder showing the last accessed time/modify time/full path to the file.

Function ShowFiles()
Dim fsoSysObj As FileSystemObject
Dim fdrFolder As Folder
Dim filFile As File
Dim strPath As String

strPath = UCase("s:\AssignmentList\2003\")
'strPath = path_to_file_in_double_quotes

Set fsoSysObj = New FileSystemObject

Set fdrFolder = fsoSysObj.GetFolder(strPath)

For Each filFile In fdrFolder.Files
    Debug.Print filFile.DateLastAccessed & " " & filFile.Name
    Debug.Print filFile.DateLastModified
    Debug.Print filFile.path
Next

End Function

A simple method would be to use the above to iterate through the folder and call an import routine (link routine) which might look like:

Function GetFiles()
Dim fsoSysObj As FileSystemObject
Dim fdrFolder As Folder
Dim filFile As File
Dim strPath As String

strPath = UCase("s:\FolderName\SubFolderName\")

Set fsoSysObj = New FileSystemObject
    'Set fs = CreateObject("Scripting.FileSystemObject")
    'Set f = fs.GetFile(filespec)
Set fdrFolder = fsoSysObj.GetFolder(strPath)

For Each filFile In fdrFolder.Files
   ' Will use the name and path to the file inside the loop
   If Not ObjectExists(filFile.Name) Then
      Call ImportExport("acLink", filFile.Name, filFile.path
   End If
Next

End Function

Public Function ImportExport(ByVal Ltype As String, ByVal Tname As String, _
                                 ByVal TLoc As String) As Long
Dim intCnt As Integer


Select Case Ltype:
    Case "acImport":  Ltype = 0
      'DoCmd.TransferSpreadsheet acImport, 8, Tname, TLoc, True, ""
    Case "acExport":  Ltype = 1
      'DoCmd.TransferSpreadsheet acExport, 8, Tname, TLoc, True, ""
    Case "acLink":    Ltype = 2
      'DoCmd.TransferSpreadsheet acLink, 8, Tname, TLoc, True, ""
End Select
DoCmd.TransferSpreadsheet " " & Ltype, 8, Tname, TLoc, True, ""

End Function

Function ObjectExists(ByVal strObjectName As String) As Boolean

     Dim dbs As Database
     Dim tbl As TableDef
     
     Set dbs = CurrentDb()
     ObjectExists = False
     
     For Each tbl In dbs.TableDefs
          If tbl.Name = strObjectName Then
               ObjectExists = True
               Exit Function
          End If
     Next tbl
     
End Function

Please note, this makes the assumption that you are going to use the file name as the name of your table. (and that this is how you did it previously). Any method to make a name would work, just be consistent. This could be adjusted to import tables extremely easily.

Mike

 

[DaLiMan]

Wow, thanks Mike.

Sorry for the late reply, but I had a day off.
I'm gonna try it as soon as I get the change today and let you know.

Daniel

 

[DaLiMan]

Sorry, but I don't get it.

I thought it was just like in Excel, but can't make it work.
I copy and pasted your code in the VBeditor in my database but all I get is a compile error: User-defined type not defined.

Can you tell me what I'm doing wrong?
I did change the path_to_file between the quotes to mine.

Daniel

 

[mdmilner]

Did it also highlight the very first FileSystemObject variable declaration?
Inside the same code module, go to the Tools Menu - References. Find the Microsoft Scripting Runtime listed and check it ( then hit Ok)

I believe you're missing a reference to the object library.

While you're at it, make sure the MS DAO. 3.6 Object Library is also checked.

Mike

 

[DaLiMan]

Checked the "Microsoft Scripting Runtime" option. (was indeed the problem)

Must I create a macro from which to call the functions?
and which function do you use for what purpose.
can you make me help understand most of youre script?

Maybe then I can make it work, because I feel quite stupid right now.

PS: When I'm in the first function and I hit the [run] button in the VBeditor I see something happening but no tables are created.

 

[mdmilner]

Start out by editing this line in the first function:

strPath = UCase("s:\FolderName\SubFolderName\")

Substitute the correct path to the folder you wish to use.
This sends the first function to the folder you indicate and begins to work through the entire list of everything there within the For Each...Next loop.

The if statement inside the loop checks to see whether the the tablename exists within your database already. As a note, it's looking for the whole thing...so a spreadsheet named file1 is actually file1.xls.

What you should do to 'fix' that is to use a combination of two string manipulation functions. InStr & Left & Len

strVal = Left(filFile.Name, LEN(filFile.Name) - InStr(filFile.Name,".")-1)

This takes the file (file1.xls) and shortens it to just (file1) and pushes it into the variable (strVal). You'll need to declare it above AND substitute references in the ObjectExists & ImportExport function to strVal

If it doesn't find the table name, it attempts to call ImportExport.
By passing it the value "acLink" it actually executes a line of code that will look like the 3rd entry in the Select Case statement. Note, the entries within each Case are commented out but left in for visual clarity.

The DoCmd.TransferSpreadsheet command actually does the work of Importing/Exporting/Linking

Mike

 

[DaLiMan]

Thanx for the explanation Mike,
Now I know a little bit what were doing here.

I did change the strPath like U said but now the next error occurs.

Run-time error '5':
Invalid procedure call or argument.

it highlights:
Set f = fs.GetFile(filespec)

I use .txt files in a hidden folder. This couldn't be the problem could it?