Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
jwrnana
 
Posts: n/a
Default 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


  #2   Report Post  
jwrnana
 
Posts: n/a
Default

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




  #3   Report Post  
KJ
 
Posts: n/a
Default

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.
  #4   Report Post  
Bill Manville
 
Posts: n/a
Default

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

  #5   Report Post  
jwrnana
 
Posts: n/a
Default

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





  #6   Report Post  
Bill Manville
 
Posts: n/a
Default

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

  #7   Report Post  
jwrnana
 
Posts: n/a
Default

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



  #8   Report Post  
Bill Manville
 
Posts: n/a
Default

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

  #9   Report Post  
jwrnana
 
Posts: n/a
Default

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



  #10   Report Post  
Bill Manville
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conversion of Cell Contents into a Functional Worksheet name ? GMJT Excel Worksheet Functions 1 August 21st 05 04:59 PM
Updating 1 worksheet with result from another worksheet mwrfsu Excel Discussion (Misc queries) 0 August 19th 05 10:01 PM
Replace worksheet name in formula linked to a different workbook Jen and Debra Excel Discussion (Misc queries) 1 February 11th 05 12:02 AM
How do I remove empty chart plots from linked worksheet charts Lee IT Charts and Charting in Excel 3 January 31st 05 05:31 PM
copyright and worksheet protection dow Excel Discussion (Misc queries) 2 January 3rd 05 04:07 PM


All times are GMT +1. The time now is 11:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"