Replies

Mark

That is exactly what I wanted to know – sorry for the delay in replying.

Nick

Charlotte

Whether I have got the correct porperty or not is a good question. I have been delving through help files, newsgroups, Google, everything I can find on the net, but I have not found the right answer yet

I have tried using :

objVSCell = rst(rstTable!FieldName) Type mismatch again
objVSCell.Formula = rst(rstTable!FieldName) #Name error even with quotes around it, and an equals sign
objVSCell.Value = rst(rstTable!FieldName) Object does not support this property or method

If you can suggest any more, I’d be happy to try them

Thanks

Nick

A good suggestion. This worked & completely solved the problem.

Thanks

David

Thanks for the tip – it might be easier to do that than waiting to find a solution for the Automation Error.

Nick

The solution to this problem seems to lie somewhere in the registry. When I try to instantiate another copy of Access – whether from within Access itself or another Office application it keeps coming up with an Automation Error & file not found. We are continuing to investigate – if anybody has come across this problem before, I would be interested to know how they solved it.

Nick

Mark

Tried it out on my Pc at home (same setup – Access 97 + Office 2000) & it worked a treat. No problem at all. I have to conclude that there may be some regsitry settings on my PC at work causing it to fail. I shall investigate further & let you know.

Nick

Mark

Thanks for your prompt reply. I had a look at your code, but the problem I still have is that any attempt to open another instance of Access using either early or late binding causes an Automation error -2147… File not found. This makes me think that it may be something to do with my PC setup – I run Office 2000 with Access 97. I wonder if you have any thoughts on that.

Nick

It’s intended to speed up my own development work, but it could work for other databases, so long as they are starting from scratch.

I think the import trick should sort out the problem. thanks for the help

I have created an Access add-in which automatically builds forms and code for existing tables and queries. It makes extensive use of record types and expects two modules – modRecordType and modRecordBuild – to already exist in the target database. I know it’s easy enough to flag a warning message if they are not found, but what I would like to do is use the add-in to create them for me. However, there does not seem to be the equivalent of CreateForm, CreateReport for modules, & I was wondering how to get round that. Perhaps a warning message is the easy answer ?

The first one, Charlotte. I know how to add code to existing modules, but what I would like to do is actually create a new module using VBA

That should do the trick. You learn something new every day.

Thanks

Nick

Thanks for the help, but adding the .Name produced the same error. I managed to find out that if you merge styles you have to reference the original workbook where the styles were defined.

So this was the code that worked :

objXLRange.Style = objXLBookLookup.Styles(“WO32”)

A minor irritation is that when running this line of code :

objXLBook.Styles.Merge Workbook:=objXLBookLookup

Excel produces a dialog box to confirm over-writing existing styles with the same name, and I cannot find any way of avoiding this using code. Ah well.

Charlotte

Thanks for your reply. I also posted this on the Excel board, and I received this helpful tip :

If TypeName(objXLSheet.Cells(intRow, intCol).Value) = “Error” Then…

which has done the trick exactly.

Nick

Thanks for the replies.

TypeName certainly did the trick.

BTW, ISError returned True against the contents of the cell, but False for the cell formula. One to be aware of.

Also BTW, IsError does not appear in the Visual Basic Help index. It goes from IsEmpty to IsExpanded. I wonder why that is ?

Thanks to everyone again

Nick

Thank you Charlotte

It now works perfectly

Nick