Excel macro to VBS script

I have the following code that works great inside my excel workbook. However, the spreadsheet where this script resides is about to be converted into a shared workbook and I will be unable to use macros in it. I was looking at using a stand-alone script outside excel to modify the workbook but I have run into issues.

Following is the code I have in excel:

Please Login or Register  to view this content.

This is my start but I keep runnin into issues:

Please Login or Register  to view this content.

Any help/advise would be appreciated.


Thanks,

Met

Hi Metrazal,

When in situations like yours, I have had problems in the past with Excel trying to identify which workbook and which sheet is being referenced, especially when using the .Cells syntax. I have had problems with .Cells (I'm not sure if the problems are real or imaginary) when using the 'with Sheets' construction that you are using.

My first inclination would be to explicitly reference these items like in the untested sample code below:

Please Login or Register  to view this content.

I wouldn't remove the 'with Sheets', I'd just add the explicit references.

Lewis

LJ, Thanks for the advice. I have tried playing around with your suggestion but to no avail. There are probably multiple issues with the script but the first error occurs at line 5.

Thanks,
Met

There I go again. I did the same thing yesterday. You're talking VBS and I'm thinking VBA from an Independent workbook.

Is it possible for you to use the 'shared workbook' as an Excel Data file, by putting all your code in a Master workbook, and just reading (and possibly writing) to the 'shared workbook' (if Microsoft allows either).

Your original code might work with the modifications I originally suggested.

Lewis

LJ, Thanks again. I think you are correct in that I can run the script as is in Excel regardless of whether or not the workbook is shared. (I just cannot modify it while it is shared.)

However, It may be cleaner if I run this as a VBS script because I am debating about excuting the script through Scheduler.

I am going to play around with shg's suggestion and see if I can get the vbs scipt to work. (It may be a better option.)

Thanks to all..

Met

Thanks to everyone for the help. I decided to, as suggested, run the script inside excel and schedule the task from there.

Thanks again,

Met