locked
Copying A Worksheet From Addin To Active Workbook (vb.net) RRS feed

  • Question

  • I'm new to VSTO but have a reasonable understanding of VBA.

    I'm migrating an add-in that was written in VBA to VSTO and need a little help with parts of it.

    In my old add-in I have template worksheets stored within the add-in itself.

    I'd like to keep a workbook within my VSTO addin and use .net to copy a worksheet from that workbook (within my new VSTO addin) into the activeworkbook.

    Is this difficult to do? Little help?



    Thursday, August 8, 2013 10:32 AM

Answers

  • Basically, yes, that's how it works.

    Right-click on the project name in the Project Explorer and choose to Add a New Folder; give it a meaningful name. Drag the file from Windows Explorer onto the folder in the PRoject Explorer.

    Make sure the file is selected, then in the Proporties window:

    - Build Action: Content
    - Copy to Output directory: Copy always

    When you build your solution, VSTO will then include the file and recreate the folder and the file during installation.

    You get the path while your code is running something like the following, except that this example from an Add-in I have with a list of graphics in the folder ObstacleObjects:

    fileName = String.Format("{0}{1}{2}", Globals.ThisAddIn.filePath, "\ObstacleObjects\", ObstacleName.Remove(0, 3) + ".emf")


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by DoctorExcel Friday, August 9, 2013 9:53 PM
    Thursday, August 8, 2013 2:20 PM

All replies

  • How do you envision "keep a workbook within my VSTO add-in"? Do you mean you'd want to distribute a workbook as part of your solution?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, August 8, 2013 12:14 PM
  • I'm guessing, but I suspect its wrapped up in the installation files and then is deployed into an installation folder with the other project files. Then I need to refer to the file, open it, copy the sheet into the activeworkbook, and then close the excel workbook again.

    Any simpler way?

    and if not, how do i go about doing that?

    Thursday, August 8, 2013 12:59 PM
  • Basically, yes, that's how it works.

    Right-click on the project name in the Project Explorer and choose to Add a New Folder; give it a meaningful name. Drag the file from Windows Explorer onto the folder in the PRoject Explorer.

    Make sure the file is selected, then in the Proporties window:

    - Build Action: Content
    - Copy to Output directory: Copy always

    When you build your solution, VSTO will then include the file and recreate the folder and the file during installation.

    You get the path while your code is running something like the following, except that this example from an Add-in I have with a list of graphics in the folder ObstacleObjects:

    fileName = String.Format("{0}{1}{2}", Globals.ThisAddIn.filePath, "\ObstacleObjects\", ObstacleName.Remove(0, 3) + ".emf")


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by DoctorExcel Friday, August 9, 2013 9:53 PM
    Thursday, August 8, 2013 2:20 PM
  • Hi Cindy

    That's great. As I'd like the experience to be as seamless as possible, I'd like to open my file as an addin. I'll probably save the file as an xlam so that when the workbook opens the user doesn't see it loading or as another open file.

    How would I then refer to that file, ideally as a workbook object.

    and if I can be cheeky and push my luck further - if I lock the project of that add-in file with a password, can I still extract the worksheet I want with my VSTO add-in.

    Are there any books you would recommend I read to get myself a little up the curve on VSTO?

    So many around but most get bad reviews. I specifically want to learn vb.net (easier transition for me) in VSTO and I'll only be working with Excel files.

    Thanks

    Thursday, August 8, 2013 8:44 PM
  • As you can see from my signature line, Excel isn't my area of expertise, more likely yours :-)

    VSTO doesn't change how an Office application works, it only extends parts of what an application provides. That means, whatever you can do with Excel using VBA you should be able to do also from within VSTO. VSTO doesn't do a blessed thing to or with xlam. So...

    How would you do what you propose if this were VBA? Can you copy a sheet from a (protected) xlam?

    Books: For VB.NET, "VSTO for Mere Mortals". It was written for 2005, but for transitioning it should be adequate as a starter. All the basics remain the same, it just won't cover anything introduced at a later point.

    For a "nitty gritty" view of the technology, "VIsual Studio Tools for Office 2007". The code is C#, which I've never found a problem. And that doesn't change the "inside view" the book provides about what's happening "under the hood". If you run into any C# you need to understand and can't figure out, you can ask here and we can "translate" to VB-speak.

    Those are the only two I've ever encountered that do the topic justice...


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, August 9, 2013 3:05 PM
  • Hi Cindy

    Thanks for your help - that's useful.

    I can refer to to the add-in in VBA and I can copy the worksheet into the active workbook so I can hopefully do it in VSTO too. :)

    I've already ordered that book you mentioned - hope it helps me.

    Cheers

    Erik

    Friday, August 9, 2013 9:55 PM
  • Hi Erik

    <<I can refer to to the add-in in VBA and I can copy the worksheet into the active workbook so I can hopefully do it in VSTO too. :)>>

    Then it should work :-) Just try with the same basic code as you'd use with VBA. It may need some more full qualification, but the basic principle should still apply.


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, August 10, 2013 3:10 PM