upgrade 2000 oding problems

[scrappe7]

sorry for the lack of info, wil try a lot harder to give as many details as i can type. here goes.

we have a database that is used with excel to enter in reports for our departmental needs. there was a lot of integration between excel and access when it was created. however, it was created in off 97. we have upgraded a few of the machines and have found out the lack of conjunction with 2000. the project has transfered over pretty well except for a few problms.

1) i use a switchboard that allows the user to press a button and an import from excel occurs. it looks like this. it runs 2 macros within access that updates some other info and then i have a run code line which uns the following module: (keep in mind it was created in 97)

Public Function blnDumMAP() As Boolean

Dim appExcel As Excel.Application

' Link to Excel using automation
Set appExcel = CreateObject("Excel.Application&quot

' So you don't watch what's happening...
appExcel.Visible = False

' Open Dummy File, which contains all unique names found in all of the various "responsible" fields
' (e.g. Primary Responsible, Primary Responsible2, Secondary Responsible, Secondary Responsible3)
appExcel.Workbooks.Open "c:\Database\Dummy.xls"
' Open Management Action Plan excel file
appExcel.Workbooks.Open "c:\Database\Management Action Plan.xls"

appExcel.Visible = True

'Open hidden excel file MAPHolder, which contains a macro that will copy the "responsible" data from the dummy
'file to the Management Action Plan
appExcel.Workbooks.Open "c:\Database\MAPHolder.xls"
' To troublshoot, if necessary, open 'c:\Metrics\MAPHolder.xls', hit the 'enable macros' button
' that pops up upon opening, and then select 'Tools' -> 'Macro' -> 'Macros', highlight 'MapHold' and select 'Edit'.
' This will display the macro code that will execute. Comments will explain the function of the code.

'Run macro that copies data from Dummy file to MAP
appExcel.Run "Module1.MapHold"

'Close the dummy and MAPHolder files
appExcel.Workbooks("Dummy.xls&quot.Close False
appExcel.Workbooks("MAPHolder.xls&quot.Close False

'Delete the dummy file (so that Access does not ask the user whether he/she wants to overwrite it each time
Kill "c:\Database\Dummy.xls"
End Function

As you can see it opens an excel file that then runs this macro:


Sub MapHold()
'
' MapHold Macro
' Macro recorded 1/10/02 by Edward Santevecchi
'

Application.ScreenUpdating = False


' COPIES OVER RESPONSIBLE COLUMN
' INSERTS N/A, TBD INTO COLUMN AND REHIDES
Windows("Dummy.xls&quot.Activate
' Makes Dummy.xls the active window
Range("A2:A3&quot.Select
' Selects cells A2 and A3
Selection.Insert Shift:=xlDown
' Inserts two blank cells in A2 and A3 and shifts existing cells down
Range("A2&quot.Select
' Makes A2 the active (selected) cell
ActiveCell.FormulaR1C1 = "N/A"
' Inserts the text "N/A" in cell A2
Range("A3&quot.Select
' Makes A3 the active (selected) cell
ActiveCell.FormulaR1C1 = "TBD"
' Inserts the text "TBD" in cell A3
Columns("A:A&quot.Select
' Selects (highlights) column A
Selection.Copy
' Copies selected column (column A)
Windows("Management Action Plan.xls&quot.Activate
' Makes Management Action Plan.xls the active window



Columns("AG:AG&quot.Select
' Selects column AG
ActiveSheet.Paste
' Pastes data copied from the dummy file to column AG in the MAP
Columns("AG:AG&quot.Select
' Selects column AG
Selection.EntireColumn.Hidden = True
' Hides column AG
Range("AJ1&quot.Select
Windows("Dummy.xls&quot.Activate
Application.CutCopyMode = False


Application.ScreenUpdating = True
Windows("Management Action Plan.xls&quot.Activate
Range("A2&quot.Select




End Sub

I dont know where the problem is but there is no importing occuring. when i check my table the data isnt there. i dont get any errors just no data???? so i figure the coding is compatible and the comp cant read the new stuff. how do i fix my coding to become accepable for 2000.

 

[cheerio]

Office 2000 has much stronger security regarding macro viruses. You need to ensure that both Excel and Access have the security set at a level that will allow the code to run.

Also, I suggest you put a temporary debugging messagebox in each procedure at the start that simply pops up and tells you the procedure is running. You can then begin to narrow down where the code is sticking.

Ken

PS I hope you do not mind me mentioning a point which is nothing to do with your problem. Much of the code is twice as long as it need be. For example:

Range("A3&quot.Select
' Makes A3 the active (selected) cell
ActiveCell.FormulaR1C1 = "TBD"
' Inserts the text "TBD" in cell A3

can be replaced by:

Range("A3&quot.FormulaR1C1 = "TBD"
' Inserts the text "TBD" in cell A3

It is hardly ever necessary for VBA to select a cell and when you do so the action is very slow as Excel goes through the motions of redrawing the screen to show that cell as selected. Your code is far harder to follow as well.