Replies

the short cut does get the job done fine, but if it wasn’t a hastle i’d liuke to see what that code would look like. its not a big deal im just a little curious of howw you would go about that. thanks a lot

great hans!!!!! thats an awesome little tid bit. i never would have known it was an exploer issue and needed ot be done within access. my DB is finally perfect, never thought i could say that!!!!!!!!!!!!!!!!!!! thanks

thanks guys, silly me why didnt i think to look for it there??? appreciate it

thats exactly what i thought. i went uder tools > options >advanced and checked off exclusive. this was also done on th eother computer that would be opening the file. we can still both open the DB at the same time. is there another setting that needs to be in place? i feel as if we have missed a step somewhere. i followed the help instrcutions and yours but nothing is done. whether or not exclusive is checked the DB can be opened 2x. any suggestions or settings we also need in place to get tiss to work? thanks alot.

thanks for the reply. in the macro for the access button that runs the above coding i then i have an action transferspreadsheet. this is where access takes the data transfered on the management action plan.xls file and then imports the formatted data into access. actually it takes a sheet in excel because the original entering of the data and the macro formatting occur within the same spreadsheet.

for the transferspreadsheet action i have the following:

transfer type: import
spreadsheet type: Microsoft excel 8-9
table name: Action Plan 2
file name: c:databasemanagement action plan.xls
has field names: yes
range: Blank!A:W

this use to work back in the 97 version, if my coding is ok for the 2000 format then is it something to do with my importing action? i can not thank you enough for this help. so many weeks were spent creating this and its looking like it might be junked now. thanks a lot for eveything.

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”)

‘ 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:DatabaseDummy.xls”
‘ Open Management Action Plan excel file
appExcel.Workbooks.Open “c:DatabaseManagement 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:DatabaseMAPHolder.xls”
‘ To troublshoot, if necessary, open ‘c:MetricsMAPHolder.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”).Close False
appExcel.Workbooks(“MAPHolder.xls”).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:DatabaseDummy.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”).Activate
‘ Makes Dummy.xls the active window
Range(“A2:A3”).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”).Select
‘ Makes A2 the active (selected) cell
ActiveCell.FormulaR1C1 = “N/A”
‘ Inserts the text “N/A” in cell A2
Range(“A3”).Select
‘ Makes A3 the active (selected) cell
ActiveCell.FormulaR1C1 = “TBD”
‘ Inserts the text “TBD” in cell A3
Columns(“A:A”).Select
‘ Selects (highlights) column A
Selection.Copy
‘ Copies selected column (column A)
Windows(“Management Action Plan.xls”).Activate
‘ Makes Management Action Plan.xls the active window

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

Application.ScreenUpdating = True
Windows(“Management Action Plan.xls”).Activate
Range(“A2”).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.

thanks for the reply,

We have decided to upgrade everyone here to Access2000. my question now is how to convert my acc97 DB so that i wont lose it in the upgrade. should i install both on my comp at first? or cani just install the upgrade and then “convert the acc97 db to acc2000 by specifying a new name” by going to ‘database utilities’ , convert database’ and then ‘to current database version’. if i do it this way do i need both versions on my computer.

FYI – the DB file is just one file created in 97 that exists on a network accessible to multiple users. some of us currently have 97 while others have 2000. i will be converting to 2000 soon and we dont really care aboutt he 97 users regarding this db anymore. only 1 or 2 people need access to the db, so as long as 2 people can work it it is fine. however it needs to be 2000 not 97 for the few users of the DB. i dont want to make a front/back end files. i just want one 2000 DB that the needed users can access. will the simple covert option work fine. there are no modules or reports in this DB. only tables, forms, queries and macros.

NOTE – one of the macros imports an xls97 file. will i have to edit anything there or will it still recognize the file even though it will be upgraded? thank a lot. if you need more detaisl please let me know.

hi there i tried your fix with the ‘as object’ the thing is that in my switchboard i set the run code as the command. What exactly do i enter in the code name. do i use the module name i savbed it as “TextFormat” or do i use what the sub is called in the programming itself “IsFormated()” what would the exact text look like. is it better to try and run the module with a macro and use the switchboard to run the macro instaed of run code? thanks alot

Well something isnt too right here, I set a db for exclusive under the options tab and placed it on the shared drive. Needless to say, multiple users still had access to it while i was on it. Is there something else needed. I also entered the command line proprerty for it there and locked the records. None of this did anyhing though.

It’s ok John, if you feel you need more questions to beat Andrew I’m your guy. thanks for the help. Its working great now.

I need to check that a value in one cell exist in a range of other cells. The colored columns peach and blue get their info from links on other sheets. I then want to check if that country/division was included in my table to the right. The table titles are all in column A now. If the value in the colored cell is not found somewhere in column A then I want what is missing to be visible in the cell.

Hi John,

What I am doing is linking columns M and O from another worksheet that produces a list of all the countries and divisions on our database. I then want to check these lists to the tables that are on the same page. So that if a country/division appears on our database but does not have any record on the table the user will be flagged. All i want is to check if that division/country is also found in one of the charts.

Even when I only select 2 cells to look in in column D, i get the same error. Both contain text not numbers???

What you suggested always gives back “OK” as long as any text is found. It is snot checking exact spelling of the words, just for words. Get it? i need to find that specific text(cell value) in a range of other cells. Thanks.

I tried that but the array does not work. It returns the false valuse always. It does not recognize to search fro the string value in the other cells. How can i make it smart???? For example,

if a1 = cat

c1 = turtle
c2 = fox
c3 = dog
c4 =horse
c = cat

The statement {=(if {a1=(C:C), “ok”, “error”)} should return “ok” because cat is found in the C column. However, my statements are always reurning the false. IS this because the array uses an “and” and not an “or” statement?? WHat do you think?

Thank you wassim problem solved.

Hey wassim,

I used auto_open but the coding that i ran has a close of that file automatically. How can i stop the auto_open from executing automatically on the open of the file. So that i can edit that file? I cant get to my coding in that file, i just need to pause it for edit