Strange Occcurance

[rama4672]

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]

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]

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]

Hello Ian,

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

That's it! :)

[rama4672]

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]

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]

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]

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]

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]

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