Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Workbook Macro - with a twist
Hi All,
I'm looking to open a 'register' workbook, look down a column for the next available (sequential) index number (first instance of an empty cell in the target column), send that number (number above the first empty cell +1) into the workbook from which the macro was called, update the 'register' workbook by entering the now allocated index number into the previously empty cell, save the 'register' workbook and close it. Seems easy, however there is a slight complication. The 'register' workbook is in a directory that is generally read only to most users. I need the macro to pass (write) credentials so that it can open the file, edit and save, all without the user knowing this is happening (screenupdating = false). My IT guys have set up a domain user named 'Macro' that has write access to the directory in which the 'register' workbook resides. Can this be dome within an Excel VBA macro, passing of credentials whilst opening another Excel workbook ? I'm using Excel 2007 but some of my users will be Excel 2000. Any ideas ? Thanks, Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Workbook Macro - with a twist
Your "IT Guys" should know that simply giving a user name won't help, access to network folders get the network login name for access purposes, having said that i'm not competent at network issues so could e wrong, why don't you simply call the macro first, get your number and then make the workbook protected? Neil;367517 Wrote: Hi All, I'm looking to open a 'register' workbook, look down a column for the next available (sequential) index number (first instance of an empty cell in the target column), send that number (number above the first empty cell +1) into the workbook from which the macro was called, update the 'register' workbook by entering the now allocated index number into the previously empty cell, save the 'register' workbook and close it. Seems easy, however there is a slight complication. The 'register' workbook is in a directory that is generally read only to most users. I need the macro to pass (write) credentials so that it can open the file, edit and save, all without the user knowing this is happening (screenupdating = false). My IT guys have set up a domain user named 'Macro' that has write access to the directory in which the 'register' workbook resides. Can this be dome within an Excel VBA macro, passing of credentials whilst opening another Excel workbook ? I'm using Excel 2007 but some of my users will be Excel 2000. Any ideas ? Thanks, Neil -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=102953 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Workbook Macro - with a twist
What I would recommend is to treat the worksheet like a access database. the
access table and an excel worksheet are basically the same. the reason why I'm recommending the database solution is access has bettter protection methods then excel. I would recommend password protecting the workbook. You can modify the file using ADO methods of access. You can also password protect the VBA page so users will not be able to see the password used to modify the worksheet. Here are some exaples of ADO method http://www.erlandsendata.no/english/...php?t=envbadac The file can be either a MDB file or an XLS file. "Neil" wrote: Hi All, I'm looking to open a 'register' workbook, look down a column for the next available (sequential) index number (first instance of an empty cell in the target column), send that number (number above the first empty cell +1) into the workbook from which the macro was called, update the 'register' workbook by entering the now allocated index number into the previously empty cell, save the 'register' workbook and close it. Seems easy, however there is a slight complication. The 'register' workbook is in a directory that is generally read only to most users. I need the macro to pass (write) credentials so that it can open the file, edit and save, all without the user knowing this is happening (screenupdating = false). My IT guys have set up a domain user named 'Macro' that has write access to the directory in which the 'register' workbook resides. Can this be dome within an Excel VBA macro, passing of credentials whilst opening another Excel workbook ? I'm using Excel 2007 but some of my users will be Excel 2000. Any ideas ? Thanks, Neil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enable macro message on VBA Workbook Open then macro ends | Excel Programming | |||
Creating new worksheets in a workbook....with a twist | Excel Programming | |||
Open workbook macro- find correct month to open? | Excel Programming | |||
open workbook from a macro and continue this macro afterwards | Excel Programming | |||
Run macro in one open workbook only | Excel Programming |