locked
How to do dynamic Excel export to EXISTING Excel document RRS feed

  • Question

  • I know how to do a dynamic export to Excel, but that creates a brand-new Excel document. I have some existing Excel spreadsheets with a LOT of formulas, customizations, macros, etc. I don't want to have to re-invent the wheel each time.

    Does anyone know how to do a dynamic export into an EXISTING Excel document?

    I've tried exporting, then copy, paste-special, link, but it doesn't work. And anyways, then I'd have two documents to worry about.

    My workaround now is to export to a static worksheet, then copy and paste the data into my existing sheet (I created a new tab at the beginning and lined up the columns to match what comes out of CRM). It works, but each time I make a change in CRM, I have to do a new export, then copy and paste.

    Friday, March 26, 2010 3:58 AM

Answers

  • To hook up to your new data sheet you could move your old non-dynamic data sheet to a new book, all the links will move with it.  Rename the dynamic sheet to the name of the data sheet you just moved.

    Then you can edit links and change the source links back to new dynamic sheet in the original workbook and all should be good.

    • Marked as answer by BrianSharp Wednesday, March 31, 2010 3:30 AM
    Tuesday, March 30, 2010 11:06 PM

All replies

  • Why do you not just refresh the data? So export, do your formulas, macros... save the file and refresh as needed?
    Friday, March 26, 2010 9:14 AM
  • Export as usual. Open the one you want the spreadsheet in. Right-click on the name at the bottom of the exported worksheet and select Move or Copy then move the worksheet to the other Excel workbook.

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/ 
    or hear me tweet @leontribe


    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    Friday, March 26, 2010 1:22 PM
  • Export as usual. Open the one you want the spreadsheet in. Right-click on the name at the bottom of the exported worksheet and select Move or Copy then move the worksheet to the other Excel workbook.

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/ 
    or hear me tweet @leontribe


    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Friday, March 26, 2010 1:23 PM
    Friday, March 26, 2010 1:22 PM
  • Thanks, that's very close. Your solution does get the data into my existing workbook, and it does refresh the data when I change data in CRM. So that's a great start, but it doesn't link the data to my existing cell references.

    The first tab on my existing worksheet is labelled "Data." That's where I would manually type in client name, property address, price, etc. Then on the following tabs, it would link back to that first page for the data.  = Data!A3, for example.

    So now I have the first tab full of data from CRM. How can I link up all the existing links? I know how to do it manually, but we are talking about a LOT of cell references.

    I tried a find and replace, but it didn't look within cell references.

    Tuesday, March 30, 2010 7:54 PM
  • Because I have over 50 tabs of customizations, macros, etc. that I'd rather not have to re-do every time I export. I just want to pull the data out of CRM, dump it into my existing workbook template, then save for that client, and have the data update.
    Tuesday, March 30, 2010 7:56 PM
  • Hi,

    Set up your query in advanced find with the columns in the order you want.  Press export to excel, select Dynamic Worksheet and press export then Open the sheet.

    Right click in the body of the data sheet and select "Data Range Properties", you can then select the options you want for refresh, formulas adjacent, column headings etc.

    Change the tab name to "data" or whatever.  Right click on the tab name and move the sheet to your monster workbook.  Hook up the macros formulas etc to reference your new dynamic sheet.

    Because it is a dynamic workbook every time you open or your refresh rule kicks in the formulas will automatically update with the new data, no more copy paste values or whatever you have to do :).

    Aaron.

    Tuesday, March 30, 2010 11:01 PM
  • To hook up to your new data sheet you could move your old non-dynamic data sheet to a new book, all the links will move with it.  Rename the dynamic sheet to the name of the data sheet you just moved.

    Then you can edit links and change the source links back to new dynamic sheet in the original workbook and all should be good.

    • Marked as answer by BrianSharp Wednesday, March 31, 2010 3:30 AM
    Tuesday, March 30, 2010 11:06 PM
  • I'm still not understanding how I "hook up" the new dynamic data to my existing links. The existing links are looking for the "data" tab. I can move the dynamic data into the existing worksheet as a new tab, but I can't name it "data" because I already have a tab called "data." If I delete the old "data" tab first, it kills all the links, and I get a REF# error. So even if I re-name the dynamic tab to "data," it's too late, because all the links are now broken.

    Is there way to point all references that point to the old "data" tab look at the new dynamic tab, but in the same cell locations (A1, A2, etc.)?

    Wednesday, March 31, 2010 3:11 AM
  • Thanks, once I opened Excel and tried your answer it worked. It just didn't make sense in my head until I was in Excel actually doing it.
    Wednesday, March 31, 2010 3:33 AM