WBK1 (shared) has a routine in it which opens WBK2, copies the value
of WBK1-E3, pastes it into Next empty row col A-WBK2, goes back to
WBK1, copies the value of WBK1-B2, pastes it into Next empty row col B-
WBK2, etc, for 11 values – so that WBK2 ends up with a new row of 11
cell-values from WBK1. The routine then sorts the rows in WBK2 by col
A (which are numbers – from E3), saves and closes it, goes back to
WBK1 and clears the 11 cells of their values – ready for the next
entry.
I want the copying and pasting part of this to work for more users -
users who don’t have access to the folder which WKB2 is in (because IT
won’t/can’t be bothered to give them access). Is there any way I can
get this to work for those users, perhaps via the user’s and my
Outlook, ie adjust the copying and pasting routine for the non-access
users to something like this: copy, paste into email, send email to
me, get WBK2 opened on my PC, paste the values from the email into
WBK2???
(The non-access users can be identified from WBK1 by the fact that
there will be an “N” in Sheet1 Cell S27)

You may want to write this macro in Outlook VBA and have every thing
automated. You can hae outlook VBA filter you emial and when an email
with a specific Subject line is received run a Macro in Outlook which
will have your user access priviledges. the outlook VBA can open an
excel workbook(s).

The Outlook VBA will take the incoming email and same the workbook in
one of your folders. then open the workbook and add the new data to
your existing workbook.

You can also have Excel VBA search you outllok folder for specific
subject lines and take those emils and save the attachments into a
folder and then add the new lines from the updated workbooks into your
master workbook.

I have written both type macros before and can find code once you come
up with your preference.

filtering on a subject line in VBA code (both outlook and excel)
requires doing a search of your emails which requires a class module
in VBA.

The easier method would be to manually add a task to your outlook email
account which saves emails with a specific subject line to a outlook
folder. then the VBA code won't have to do the search.

Again if you want to some manual operations you can simply manuall save
the outlook emails to a PC folder and then your VBa macro just as to
open all the files in the PC folder and add the new lines.

The are lots of method to automated and semi-automate this process.
Just rember VBA code can access your email and open workbooks either
from Excel VBA or Outlook VBA. You can even set up a schedule event on
your PC which will run an Excel VBA macro. The advantages of running a
macro in Outlook is that your PC doesn't have to be on for an event to
run, while a PC schedule event your PC needs to be running.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183137

Microsoft Office Help

On Feb 27, 4:59*am, joel wrote:
You may want to write this macro in Outlook VBA and have every thing
automated. *You can hae outlook VBA filter you emial and when an email
with a specific Subject line is received run a Macro in Outlook which
will have your user access priviledges. *the outlook VBA can open an
excel workbook(s).

The Outlook VBA will take the incoming email and same the workbook in
one of your folders. *then open the workbook and add the new data to
your existing workbook.

You can also have Excel VBA search you outllok folder for specific
subject lines and take those emils and save the attachments into a
folder and then add the new lines from the updated workbooks into your
master workbook.

I have written both type macros before and can find code once you come
up with your preference.

filtering on a subject line in VBA code (both outlook and excel)
requires * doing a search of your emails which requires a class module
in VBA.

The easier method would be to manually add a task to your outlook email
account which saves emails with a specific subject line to a outlook
folder. *then the VBA code won't have to do the search.

Again if you want to some manual operations you can simply manuall save
the outlook emails to a PC folder and then your VBa macro just as to
open all the files in the PC folder and add the new lines.

The are lots of method to automated and semi-automate this process.
Just rember VBA code can access your email and open workbooks either
from Excel VBA or Outlook VBA. *You can even set up a schedule event on
your PC which will run an Excel VBA macro. *The advantages of running a
macro in Outlook is that your PC doesn't have to be on for an event to
run, while a PC schedule event your PC needs to be running.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=183137

Microsoft Office Help

Thanks.

Could something like this be done? Would this be the simplest way?
(btw – WBK1 is opened by all users Read Only – not shared - it's a
kind of form, ie it's never changed and saved by any user, it's just
used for collecting data then passing it to WBK2)

Non-Access-User has WBK1 open (Read Only), Data is entered by Non-
Access-User. Non-Access-User finishes with data, runs Copy-Paste-
Clear-Data macro.

But this macro has been adjusted so that if it sees an “N” in Sheet1
CellS27, instead of trying to open WBK2 (which it can’t do), it opens
a new Outlook message to me with “DataForStore” in the subject line,
attaches WBK1 to it, sends it to my Outlook, then clears the data from
the Non-Access-User’s WBK1 ready for the next entry.

My Outlook would have a rule to put anything with the subject line
“DataForStore” in a specific folder. My Outlook opens any msg in the
DataForStore folder on arrival, opens the attached WBK1 and runs the
Copy-Paste-Clear-Data macro, then closes the attachment and deletes
the email msg – bearing in mind that I am a user too and would have
WBK1 open (Read Only) too and be using it just the same as all other
Access-Users and Non-Access-Users. (In the Copy-Paste-Clear-Data
macro, I have a routine which checks whether WBK2 is open before it
tries to copy and paste the data to it and, if it is open, will show a
warning “Can’t clear yet, try again in a few seconds”.)

I'm not worried about it not working if I'm not at my PC - it doesn't
matter if some of the data is lost when I'm away.

I don't recommend shared workbooks or even testing if a workbook is
opened and waiting. My recommendation is to use an Access Database
instead. You can still use Excel as a Front-end user interface to write
data and to query the database.

Would your MIS people allow users write permission to an Access
database where the wouldn't allow the write access to an Excel workbook?
The Access database solution I think is better in this case than Excel.
MIS people like databases rather than Excel Workbook. does you MIS
group have an SQL Server that you can stoire the data?

Look into the ADO method of reading and writing databases. Your macro
can still be written in Excel VBA.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183137

Microsoft Office Help