Link Excel Files Programatically

[mmmbl]

In a database I have table that contains all our vendors. What I need to find
out is how to go about:
1. check the filedate of the excel spreadsheet on the network (done)
2. If the filedate is within 2 days of the current day then I need to link
the excel spreadsheet. The files are named using the vendor numbers in the
table. In some instances the xls might already be attached but there will xls
that are not attached and these are the ones I need to link. I have a vba
that checks the vendor file and see if there are new files out there but I
cannot figure how to link the unlink ones.

thank you for any help on this

 

[Alex Dybenko]

Hi,
have a look here:
http://www.mvps.org/access/tables/tbl0012.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[mmmbl]

This help me relink and refresh the already attached files but I'm still
having problems finding if the xls file is present on the LAN or not. I read
the vendor table and I use the vendor number to look for the xls on the LAN.
It runs until it gets to the vendor that does not have any xls files on the
LAN. Here is the code I'm trying to work on

Dim dbCurrent As Database
Dim rstObjects As Recordset
Dim varDate As Variant


Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rstObjects = dbCurrent.OpenRecordset("tbl_VENDORS")

rstObjects.MoveFirst


Do Until rstObjects.EOF
strFileName = "O:\FTP\BUYERS\DSD Cost Changes\" &
rstObjects![vnd_num] & ".xls"
If FileDateTime(strFileName) <> "" Then

varDate = FileDateTime(strFileName)
rstObjects.Edit
rstObjects![CostFileDate] = varDate
rstObjects.Update
Else



End If
rstObjects.MoveNext
Loop

rstObjects.Close

 

[mmmbl]

Nevermind I got it to work...

On Error GoTo Err_CheckForXLSFiles
Dim dbCurrent As Database
Dim rstObjects As Recordset
Dim varDate As Variant


Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rstObjects = dbCurrent.OpenRecordset("tbl_VENDORS")

rstObjects.MoveFirst


Do Until rstObjects.EOF

strFileName = "O:\FTP\BUYERS\DSD Cost Changes\" &
rstObjects![vnd_num] & ".xls"
If FileDateTime(strFileName) <> " " Then

varDate = FileDateTime(strFileName)

rstObjects.Edit
rstObjects![CostFileDate] = varDate
rstObjects.Update
Else



End If

Start_Check:
rstObjects.MoveNext
Loop

rstObjects.Close


Exit_CheckForXLSFiles:
Exit Function

Err_CheckForXLSFiles:

Resume Start_Check

This help me relink and refresh the already attached files but I'm still
having problems finding if the xls file is present on the LAN or not. I read
the vendor table and I use the vendor number to look for the xls on the LAN.
It runs until it gets to the vendor that does not have any xls files on the
LAN. Here is the code I'm trying to work on

Dim dbCurrent As Database
Dim rstObjects As Recordset
Dim varDate As Variant


Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rstObjects = dbCurrent.OpenRecordset("tbl_VENDORS")

rstObjects.MoveFirst


Do Until rstObjects.EOF
strFileName = "O:\FTP\BUYERS\DSD Cost Changes\" &
rstObjects![vnd_num] & ".xls"
If FileDateTime(strFileName) <> "" Then

varDate = FileDateTime(strFileName)
rstObjects.Edit
rstObjects![CostFileDate] = varDate
rstObjects.Update
Else



End If
rstObjects.MoveNext
Loop

rstObjects.Close

Hi,
have a look here:
http://www.mvps.org/access/tables/tbl0012.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com