Access 2003 / 2000

[Dar]

Hello:
My company upgraded us from Windows2000 to WindowsXP: Also upgraded
Microsoft Office 2000 to 2003. I had a working Access database and Excel
Macro that now will not work. I get the error message "can't find excel
file". I have made sure the paths are correct, but not getting success.
The excel file has a macro in it that basically opens another excel file and
performs deletions of certain rows based on null criteria.
The worksheets are then imported into the Access database.

Could someone work with me here and be detailed in your responses, versus
short answers that assumes I know what you're talking about? I am attaching
the code for the excel macro
Sub ProcessWorkbook()
Dim bk As Workbook, sh As Worksheet
Dim rng As Range, lastrow As Long
Set bk = Workbooks.Open("M:\Clinic\TECList\ClinicTECList.xls")
For Each sh In bk.Worksheets
lastrow = sh.Cells(Rows.Count, 11).End(xlUp).Row
For i = lastrow To 1 Step -1
Set rng = sh.Cells(i, 11)
If Not IsEmpty(rng) Then
If IsDate(rng) Then
If rng < Now() Then
rng.EntireRow.Delete
End If
End If
End If
Next
Next
End Sub
Also I am attaching the code that is within Access:
Public Function CleanClinic()
Dim appXL As Excel.Application
Set appXL = New Excel.Application
appXL.Visible = False
MsgBox "Please Wait While Clinic List Updates"
DoCmd.Hourglass True
appXL.Workbooks.Open "M:\Clinic\TECList\ClinicTECList.xls"
appXL.Run "ExcelTest.xls!ThisWorkbook.ProcessWorkbook"
MsgBox "Almost Done"
appXL.ActiveWorkbook.Save
appXL.ActiveWindow.Close
appXL.Quit
MsgBox "Update Complete"
DoCmd.Hourglass False

If the code needs to be changed to reflect the upgraded versions, please
indicate where and what. I appreciate anyones help.
Thank you
End Function

 

[Arvin Meyer [MVP]]

It looks like the files are running from a server. Depending upon how those
mappings were created, they may not always work accurately. I suggest
remapping the paths and using the new mappings in your code.

 

[Dar]

I have most of it working now, except for the part that deletes rows if the
date is less than Now(). Can you check to see if I'm using the right syntax
for that code.
Sub ProcessWorkbook()

 

[Arvin Meyer [MVP]]

Now() is a timestamp function and includes the current time as well as the
date. Try replacing:

If rng < Now() Then

with:

If rng < Date Then

Also, I don't see where you've Dim'd or instatiated Excel:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com