Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / May 2008

Tip: Looking for answers? Try searching our database.

Access 2003 / 2000

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Dar - 16 May 2008 13:13 GMT
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] - 16 May 2008 16:17 GMT
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.
Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Hello:
> My company upgraded us from Windows2000 to WindowsXP:  Also upgraded
[quoted text clipped - 48 lines]
> Thank you
> End Function
Dar - 16 May 2008 19:34 GMT
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()
> > Dim bk As Workbook, sh As Worksheet
> > Dim rng As Range, lastrow As Long
[quoted text clipped - 13 lines]
> >  Next
> > End Sub

> 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
[quoted text clipped - 51 lines]
> > Thank you
> > End Function
Arvin Meyer [MVP] - 16 May 2008 20:17 GMT
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")
Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

>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
[quoted text clipped - 81 lines]
>> > Thank you
>> > End Function
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.