OK I got it--thanks. You copy the bin/Debug folder into the end-user folder.
The end-user makes and saves modifications to the spreadhseet, then wants
the VSTO code updated. You first run the program to remove the customization
from the spreadsheet, then copy the un-customized spreadsheet (e.g.
Test.xlsm) back into .../Visual Studio 2008/Projects/ProjectName/ProjectName.
Then you open up Visual Studio, and it will show the latest changes in the
Designer, and you can recompile it to put the VSTO code back into it. If you
fail to remove customization before copying it back into the Projects folder,
you'll get the error.
I got the above to work, although I must say that while I am developing the
spreadsheet and VSTO code, the above is laborious. Therefore, while
developing the spreadsheet/VSTO, it may be easier to make any changes to the
spreadhseet through the Designer in Visual Studio. One problem I have with
this is that the Designer does not provide all the Excel functionality--2
examples: 1. You cannot write VBA macros in the Designer mode (I create VBA
macros mostly to generate VBA code which can be converted into
VB code for
VSTO--it's usually easier than figuring out the commands directly from
VB,
although the conversion process can be frustrating at times)--when you click
Developer from the Designer menu, the option to Record Macro is not there; 2.
In the Designer Excel menu, when I click Data--From Other Sources, the
option "From Microsoft Query" is not available. So using the Designer to
make changes to the spreadhseet during development has some limits to
functionality.
One more question. In the link you provided "How to Remove Managed Code
Extensions from Documents (2007 System)", it has a section "To remove the
customization assembly at run time". I'm unclear on how to apply this. Does
it mean that in my VSTO Excel Worksheet project I can add the code to remove
the customization? I got the remove customization to work using the code in
the "To remove the customization assembly from a closed document or a
document on a server" section to work by writing a new project separate from
the VSTO project, but I don't understand the first section.
--
Ed
"Jialiang Ge [MSFT]" wrote:
Hello Ed,
First, to be sure we are talking about the same thing, when I say
Designer,
I'm talking about when you open the solution in Visual Studio, and before
running it, e.g. you click on Sheet1.vb in the Solution Explorer, and you
see
a version of Sheet1 in your Excel spreadsheet that you can add VSTO code
to.
I have a book on VSTO written by 2 people who work for Microsoft on VSTO
("VSTO for Mere Mortals"), and that is what they call the Designer.
Yes, we are referring to the same thing by the workbook in the Designer,
namely, the one in the project folder instead of the one in
Bin\Debug(Release) folder.
Regarding your reply, if I have VSTO Excel Workbook project Test.xlsm, and
I
copy the contents from .../bin/Debug/ to, e.g., ...MyDocuments/Test/, and
open up Test/Test.xlsm directly with Excel (without VS), make
modifications
to it and save them, and then copy just the Test/Test.xlsm file back into
bin/Debug, and then open VS and look at and run it, I don't get any error
suggested by the above articles (i.e. ..."The document is already
customized."). Anyhow, if I did get the error, I understand how to
overcome
it.
Sorry if my last reply was not clear. I meant to copy the Test.xlsm file
back into "the project folder", to overwrite the workbook in the designer
after the modification. If you simply overwrite the one in the Bin/Debug
folder, the modification won't show up in our designer, and all the
modifications will be lost after we re-compile the VSTO project.
However, you say that "the behaviors of #2 and #3 are by design." This is
what I have a problem with. When end-users make modifcations to the file,
and I copy it back into the bin/Debug folder and pull it up, the
modifications do show up when I Run the application, but they do not show
up
in the Designer. Because they do not show up in the Designer, it makes it
more difficult to make any modifications to the spreadsheet and VSTO
coding.
If you want to modify the design and code of a spreadsheet, you want to
work
with the latest version of the spreadsheet, but the Designer does not show
the latest version of the spreadsheet. Again, the only way I know to do
this
would be to create a VS Excel Workbook New Project, and in the first
window,
use "Copy an existing project", and use the latest spreadsheet to copy.
However, like I suggested earlier, this creates numerous projects which
you
have to keep track of...maybe this is necessary, but I mostly want to
clarify
is whether this is how it is intended and if I understand everything
correctly, or if I am missing something. It seems to me there should be a
way to get the Designer to reflect the latest changes to the spreadsheet
without having to create a whole new project.
See my reply to the second question. I did not mean to overwrite the one in
the Bin\Debug folder. We need to replace the one in the project folder.
Incidentally, this problem occurs even in Run mode in VS. If I create a
VSTO Excel Workbook, and make changes uses the Designer, and then Run it,
and
then make changes directly to the spreadsheet in Run mode, and use
File-Save
to save them, and then Stop running the application to go back to design
mode, the changes I made during Run mode are not reflected in the
designer.
(Contraray to what I suggested in the first posting, if you click
File-Save
during Run mode, the changes will be saved even if you recompile, but if
you
neglect to File-Save before stopping the Run, a message box will pop up
warning you that you are about to lose your modifications.).
--
Ed
""Jialiang Ge [MSFT]"" wrote:
Hello Ed,
The behaviors of #2, #3 are by design. When we create a VSTO Excel
workbook
project, Visual Studio creates an Excel workbook file for us in the
project
folder /Visual Studio/Projects/{Project Name}/{ Project Name }/. This
Excel
workbook file is one we see in Visual Studio Designer. After we compile
the
project, VS will create a new copy from this workbook to the sub folder
/bin/Debug, as well as other files like manifest and dll assembly. The
new
workbook in the /bin/Debug folder is the one we see in debugging or after
the distribution.
For your question of how to update the workbook in the VSTO project
without
losing the end user modification, you may consider this step list:
1. Distribute the VSTO solution to the end user (the files in the bin
folder), and the end user makes the modifications in the workbook.
2. We get the new workbook file from the end user.
3. Remove the customizations of the workbook by calling
Workbook.RemoveCustomization from a Excel automation client (You may
build
such a client app by following the MSDN article
http://msdn.microsoft.com/en-us/library/bb772099.aspx)
4. Replace the workbook in the Visual Studio/Projects/{Project Name}/{
Project Name }/ folder with the resulting xlsx of step 3.
5. Open the VSTO project and continue our programming.
Please note that the step 3 is a must, otherwise, you may get the error
"An
error occurred while attempting to persiste the data. The message
returned
is: The document is already customized". The reason is explained at the
MSDN forum thread:
http://forums.microsoft.com/MSDN/Sho...74217&SiteID=1
Please have a try and let me know whether this step list fits your
requests. If you have any other concerns or questions, DON'T hesitate to
tell me.
Have a nice day!
Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you.
Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each
follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.