Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
Updating 1 worksheet with result from another worksheet | Excel Discussion (Misc queries) | |||
Replace worksheet name in formula linked to a different workbook | Excel Discussion (Misc queries) | |||
How do I remove empty chart plots from linked worksheet charts | Charts and Charting in Excel | |||
copyright and worksheet protection | Excel Discussion (Misc queries) |