Emailing linked worksheet
 

I have 2 worksheets in excel. The first one is imported from my access
database. The second is linked to that worksheet. I need to email the
second sheet to others for them to input information in several unprotected
cells. When they receive it, however, it asks the question if they want to
update the linked information. Can this be avoided?

Or am I not going about this the wrong way?

Once I receive this back, is there a way to pull the data back to access via
excel?

Thank you
JR

My apologies for reposting, but I am running into a timing problem. Thank
you in advance
"jwrnana" wrote in message
...
I have 2 worksheets in excel. The first one is imported from my access
database. The second is linked to that worksheet. I need to email the
second sheet to others for them to input information in several
unprotected
cells. When they receive it, however, it asks the question if they want
to
update the linked information. Can this be avoided?

Or am I not going about this the wrong way?

Once I receive this back, is there a way to pull the data back to access
via
excel?

Thank you
JR

jwrnana wrote:
My apologies for reposting, but I am running into a timing problem. Thank
you in advance
"jwrnana" wrote in message
...

I have 2 worksheets in excel. The first one is imported from my access
database. The second is linked to that worksheet. I need to email the
second sheet to others for them to input information in several

unprotected

cells. When they receive it, however, it asks the question if they want

to

update the linked information. Can this be avoided?

Or am I not going about this the wrong way?

Once I receive this back, is there a way to pull the data back to access

via

excel?

Thank you
JR






If using Excel versions *after* 2000 you can break the external links.
Otherwise, copy, paste special - values to retain only the information.

Jwrnana wrote:
Once I receive this back, is there a way to pull the data back to access via
excel?


You will generally need to run a query of some sort to update the database.
Depending on the structure of the data you need to import.
- If it is in a columnar table, I would import the table into Access and then
run an Append query in Access to add the data to the master tables.

- If it is distributed in different cells then I would construct an Append
query in a VBA macro and have that executed.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I have never done an Append Query - Can you give me some direction?

Thank you,

"Bill Manville" wrote in message
...
Jwrnana wrote:
Once I receive this back, is there a way to pull the data back to access
via
excel?


You will generally need to run a query of some sort to update the
database.
Depending on the structure of the data you need to import.
- If it is in a columnar table, I would import the table into Access and
then
run an Append query in Access to add the data to the master tables.

- If it is distributed in different cells then I would construct an Append
query in a VBA macro and have that executed.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

You will need Tools / References / Microsoft DAO Library 3.6 (or
similar)

Sub AppendSomeData()
Dim DB As DBEngine.Database
Set DB=OpenDatabase("C:\MyDir\MyDatabase")
DB.Execute "INSERT INTO MyTable (MyNumField1, MyAlphaField2) VALUES
(" & Range("A1") & ", '" & Range("B1") & "')"
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thank you for the append information. Very helpful.

I am using Excel 2000 - Am I correct that I cannot avoid having the pop-up
box asking if I want to update each time I open a linked worksheet -- when
emailing?

Your assistance is greatly appreciated!
"Bill Manville" wrote in message
...
You will need Tools / References / Microsoft DAO Library 3.6 (or
similar)

Sub AppendSomeData()
Dim DB As DBEngine.Database
Set DB=OpenDatabase("C:\MyDir\MyDatabase")
DB.Execute "INSERT INTO MyTable (MyNumField1, MyAlphaField2) VALUES
(" & Range("A1") & ", '" & Range("B1") & "')"
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

With Excel 2000 you can't control whether or not the "do you want to
update links" dialog appears when you open the workbook through the
user interface.

When opening a workbook using code you can do e.g.
Workbooks.Open "C:\MyLinkedBook.xls", UpdateLinks:=0 ' don't update

So one way to avoid the message is to have a small workbook which has
an Auto_Open procedure that opens the linked workbook and specifies
whether or not links are to be updated using the UpdateLinks parameter.
Probably not an option in your case.

Another way is to replace all the formulas with values before sending
the workbook by email - again not always what you would want to do.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill -

I went to Tools, Macros and entered Auto_Open. From that point, I got lost.
I have never done this and don't want to create more problems that I now
have. Guidance please.
Thanks,

"Bill Manville" wrote in message
...
With Excel 2000 you can't control whether or not the "do you want to
update links" dialog appears when you open the workbook through the
user interface.

When opening a workbook using code you can do e.g.
Workbooks.Open "C:\MyLinkedBook.xls", UpdateLinks:=0 ' don't update

So one way to avoid the message is to have a small workbook which has
an Auto_Open procedure that opens the linked workbook and specifies
whether or not links are to be updated using the UpdateLinks parameter.
Probably not an option in your case.

Another way is to replace all the formulas with values before sending
the workbook by email - again not always what you would want to do.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

As I said, I don't think this approach will be appropriate for you if
you are sending the book containing the links by email. However:

File / New
Tools / Macro / VB Editor (or Alt+F11)
Insert / Module
Type in the 2 following Sub procedures

Sub Auto_Open()
' change the name of RealBook to be the book containing the links
' change 0 to 3 if you WANT to update the links on opening the workbook
Workbooks.Open ThisWorkbook.Path & "\RealBook.xls", UpdateLinks:=0
Application.OnTime Now, "CloseMe"
End Sub

Sub CloseMe()
ThisWorkbook.Close False
End Sub

Alt+F11 back to Excel
File / Save .. in the same folder as the book you want this to open
without a links message (of course you will get a macros message
instead unless you can sign the project)

Close the file.
On reopening it it should automatically open the other workbook and
close itself down.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup