PDA

View Full Version : [SOLVED] Strange Occcurance



rama4672
02-14-2005, 09:47 AM
I am running my macros, If i run them without the file being shared they are working ok, but if i then share my file i get this strange message, and i have looked at it and i can not see what it means.

Here is the message i get:

Can not rename a sheet to the same name as another sheet, Referenced object library or a workbook referenced by visual basic.
Lets hope some one can shed some light on this one.

This only happens when i am in shared mode, so i can not even run the macro line by line to see where it stops at, as you can not edit a macro when the file is shared.
The macro transfer's data over a week, the sheets are named sunday through to saturday, if i transfer the data a day at a time it will only do the first day before it stops, yet when the file is not in share mode it goes all through the week.

Thanks

Ian

austenr
02-14-2005, 11:59 AM
Add a reference to Microsoft Excel Object Library 9.0
then declare objExcel like this:


Dim objExcel as Excel.Application

after you are done doing what you want and are ready
to exit,
Before you do:


Set objExcel = Nothing

Do this


objExcel.Quit


HTH

rama4672
02-14-2005, 03:30 PM
Thank you for the reply austenr, I will try that, but first how do i Add a reference to Microsoft Excel Object Library 9.0

Thanks



Ian

Zack Barresse
02-14-2005, 03:50 PM
Hello Ian,

From the VBE (Alt + F11) goto Tools --> References. Check the appropriate reference and click Ok.

That's it! :)

rama4672
02-14-2005, 04:08 PM
Thanks for the reply Zack, Just tried that, it was already referenced, so i put the code in that austenr posted but still not working.


Regards

Ian

rama4672
02-15-2005, 10:51 AM
I have found out what the problem is, but is there a way of getting around it.
When the macros run it adds temp sheets to work with the data then it delete's them, that is where the problem starts, I have just realised that you can not delete a sheet in shared mode, so does anybody know of a way round this

Thanks

Ian

Zack Barresse
02-15-2005, 10:54 AM
Can you create a new workbook instead, and then Kill it when done? This should work because you wouldn't be affecting the current workbook with adding/deleting any worksheets.

rama4672
02-15-2005, 11:58 AM
Thanks for that Zack, I will give that a try, That is a good Idea, I never thought of doing it that way, But will it slow it down much by creating a new workbook, as i will need to creat a workbook with at least 5 sheets in it

Also will it be better to creat a macro to make the workbook and then call it up when needed, and then another to delete the new workbook.

Regards

Ian

Zack Barresse
02-15-2005, 12:13 PM
If you're going to do that, I suggest you keep a location on your hard drive for this temporary file. E.g. "C:\temp.xls". And I'd only suggest this if the file would be used quite a bit by other routines. Otherwise, it may be just as fast to open a file, edit what you want, then close w/o saving changes.

rama4672
03-14-2005, 09:02 AM
I know that it has took me a long time to reply to this but i have been busy at work, but i am going to mark this as solved, I realised that when you share a workbook you can not delete sheets in share mode, so i just inserted the sheets on a permanent basis and then hid them.

Regards

Ian