Posts by Phoenix

No the excel file is not fine.
The excel file locks and then I cannot update its content or delete it
It is essential that the excel wbk keep the same name as it is looked up by an acess db when it opens.
"How do you connect ODBC or Pivot table? "
umm can I plead ignorance. I use a defined range for the excel sheet and then used acess's import link data function.

Ok that is no problem but theere is a problem my access db is linkto that excel sheet with that name. So when I cannot open it to edit the data or delete itand replace it gets inaccurate and I cannot delete the file to piut a corrected file in its place. I do not have the acess db open when trying to modify the excel sheet

As I said I am on a peer to peer network running win me and using excel 2000. my machine is Windows ME and the problem is on may machine wether it is connected to the network or not.

it says it is locked for editing by me!
and I am the closest thing to a netork admin our peer to peer has

Ok neither MSKB nor was it corrupted. I went and did loads of reaseach on the running processes etc of Windows me and manage to by dleting non necessasry processes and doing various disk clean procedures manage to get it to realease the locks . Now when the problem happens-regulraly, as it does, I wait 24hrs and the lock is realeased. But i have not found out where inb the registry key of ME to turn locks off

Thanks Dave I had tried Microsoft help first but to no avail. IUt is a tru shared work book but I could think of no other applicable topic subject category to put it in.

the work book was never shared in the true sense.

Excel .exe crahed whilst closing a file. Now when I try to open the file it says locked by< beth&gt; which is me!. I have logged out , shut down rebooted emptioed temp files delted xlb files arghh.
I am on a peer to peer network running win me and using excel 2000.
Will had helped me solved the link to acxess problem and I need to be able to always have the file called the same name for acess to see it.
So though I am able to take a copy though then readable is not a complete solve as I need to be able to rename the openable file the same as the now locked file. And as it is locked I cannot delete it.

in win me I am not confident to do a dos deletion and my norpotons wipe did not assist

I wanted to add that having looked at the formula of the named ram\nge it is not putting the ' aound the sheet name. and I cannot seem to edit or add the necessary' to make it see the range'

Ok am a newbie to mail merging but having had suces so far the following has me stumped
I used insert name define to name the ranges on different worksheets in the one xls doc that I was going to need on different word docs. Then when I went to word to do the merging it could not see any of the named ranges. it said entire worksheet only and not even seeing the diffrent worksheets in the xls.
I am using Word and Excell 2000 on a Win ME machine

True solution found as the problem cam up again
1)make a new column
use the funtion of text- fixed,
to generate a function of the column you want eg FIXED(f2, o, true ) so that no decimals and no commas are put into the number and the generate colum which you drag down the sheet to replicate for whole column.

Ok Just fixed the problem with a work around I used wipe info to wipe the file and then re imported it from a back up.
This does not delte the locked file record from the system but the new copy opens, links and runs just fine.

I have an excel file which is linked into acess.

I am on a windows me machine and the file is an Excel 2000 on the c drive of the same machine.

When I try to open the file after it had an ecel.exee error
it now says locked for editing.
I have emptied the temp files and cleaned up as much as possibl;e but it persist.
can I do a regedit or similar to chnge the locking permission?

Will tanks for the donation suggestion I have done so
2ndly I have solved my dilema. not a perfect solution but in this case concatenateing the fileds creating a new field and getting aces to look at that one solved it also in the options I toggled the options&gt;error checking&gt; number stored as text off.

All up a solution found now on to the nastier side the access front end

Ok from the start
I have 14 excel worksheets. I have linked these into acess and thanks to your help done this as workspaces. All sheets have a first row as Heading. This list come from 8 states plus some extras to make the membership database. It cannot be done as one cetrally maintained DB as the staes all want to retain control of their bits.
So I changed all worksheets contents to Text.
then I linked each one
into which each one goes via an append.
I have constructed a macro to make it create the table and then append each set of dat into.
It would be fine if on importing from excel some of the columns had not been assigned by acess as number instead of the text I was expecting. As a result those field stop the process running giving a numeric field overflow warning.

I have limited the error problem to the existence of thes #NUM! occurances in the data once imported.

Most are one of blank field etc I can fix but One particular table has a column of house, lot and unit numbers so things like 17A, Lot 77 etc and the rest just numerics. How can I force excel/acess to acept my aasigned field format type.

Sorry to write a history but I thought it may help to have the whole picture
I am Using Acess 2000 and excel 2002
BTW your help on the other count was invaluable am I meant to "pay" you in some fashion?

Thanks for the welcome this forum is great and the support is wonderful

But yes 0 or Null or blank will not help as the #NUM are occuring once the data has come into access-not in the excel sheet.
in excel the field says , for example Lot77.
I have, as far as I can tell may the whole worksheet in excel text using the format cells process. However when i creat the link to acess it decides to override that formatting. Is there something else I should do to the excel sheet to LOCK the formatting

Now when I run my multiple appends I am getting the Numeric overflow thing caused by the presence of #num in some cells I presume. This seem to be anoth excel to acess thing.
Nothing I do manages to force acess to accept my assigend Cell foramtt of text . It changes colums to number and then due to the characters in some fields assigns them a #NUM. Can I overide this overriding???

Brilliant! sorry for that Gaff name ranges solving that one neatly but.. as alwasy I have another problem now
posted as a new topic

I have 12 linked excel tables from which I am endeavouring to run a macro which appends all my append queries , based on the linked tables, into one table. so far no problmes but when the excel worksheets are linked they insist on brining all 65534 rows with them and not just the rows with data in them.

Is there a way of limiting the rows shown/ used?