Using EM to populate an existing spreadsheet

1

I was wanting to know if EasyMorph can pull data from a sql query and export to an existing spreadsheet which already has the column names and logo, or can this be done with the Report tab in EasyMorph. I have not used the Report section yet, but it looks like it's almost exactly what I need. I can attach a mockup of what I wanted it to look like.
BLANK New Account Report.xlsx (22.8 KB)

I was hoping to use a sql query to pull the data, then write it to the attached spreadshet starting in row 3. Also include a Client name from the data in the Title along with Start/End dates. I've done this in SSRS back in the day. I was hoping to use EM for this. Any advice?

2

Yes, it is possible. The principle is as follows:

  • Create a new sheet for unformatted (raw data). Create a table in it.
  • Using PowerQuery in Excel, create a query from that table, and insert its result into your main (formatted) sheet as another table
  • Update the raw data sheet with EasyMorph

Here is how you can do it.

  1. In the spreadsheet create a new sheet (e.g. "Raw data").

  2. In that new sheet insert a table. You can create only column headers. Or put some dummy data in it, doesn't matter.

    image

  3. Go to menu Data and create a query from that table.

    image
    image1781×588 71.5 KB

Now the most important part - create on your main sheet another table that is linked to the table on the "Raw data" sheet:

  1. Choose "Close & Load To..." in the query editor.

    image

  2. Now, insert the new table in your main sheet. It will be linked to the table on the "Raw data" sheet.

    image
    image822×546 67.2 KB

  3. Open Connection Properties for this table:

    image
    image719×287 27.2 KB

  4. Check "Refresh data when opening the file" - this will force Excel to copy data from the table on the "Raw data" sheet to the table on the main sheet.

    image
    image474×540 50.5 KB

    Save the spreadsheet and close Excel.

  5. Create an EasyMorph project that pulls the necessary data from wherever is necessary. Use the "Export to Excel file" action in the "Replace table in existing file" mode. Select the "Raw data" sheet.

    image

    Let's run it.

    image

Let's open the spreadsheet. It worked!

image

In a similar fashion, it's possible to update charts and pivot tables too. They should use (as a source) queries that copy data from sheets with unformatted data that are updated with EasyMorph.

1 Like
3

Thank you so much for your time, that was really great. I appreciate it.

4

Is it possible to retain the column widths from the main data sheet? I can get the data to load into the main sheet ok, but something wants to resize the column widths in the main sheet when I run the EM model. Ex. Column A, my logo width (Same as original) is wider than Column A in the new result

5

I believe it should be possible. Maybe, there is an option "Auto-fit to contents" that should be switched off.

6

I'll take a look at the open settings, it looks good when it first opens but when it gets the data, it changes the column sizes.

7

I figured it out.

To Stop Linked table from changing column widths:
• Open your spreadsheet with the mapped table in it.
• Click inside the column of the table

• Click on table design
• In the External Table Data, click on Properties

• In the External Table Data, click on Properties
• Uncheck adjust column width
• Click Ok

2 Likes
8

Thank you for sharing the solution!

9

Is there a way to export the refreshed data without the Raw Data tab?

10

I'm not aware of any better solution. Probably something can be done using VBA macros that are triggered by the "Excel command" action in EasyMorph, but this is based on outdated COM-automation that is finicky and frequently doesn't work on EasyMorph Server.

You can use formula references to the Raw Data sheet instead of queries, but they don't update automatically on workbook opening.

What's wrong with using queries in Excel?

11

Thank you for your response. I was trying to avoid using a macro, but before I saw your solution I put a solution together using a macro. I basically open the template spreadsheet up, then write my raw data to a new tab. From there the macro does everything and it's really fast. Just harder for folks to understand. I also had to replace some text with data in the header kind of like parameters. It woks fine, and does everything I wanted it to. You definately have more control with the macro, I just liked the idea of your solution because it's simpler to follow. I'll attach my macro. I'm not a VBA guroo, I just GTS (Google that sh!t) what I'm looking for. I've programmed in many different languages in my time, so I know the ideas of what need to be done, I just have to understand the syntax.
Macor_Test.txt (2.1 KB)
Let me know what you think of the macro. Keep in mind, I don't know VBA that well :slight_smile:

12

It's best to ask @CraigT about anything related to macros :slight_smile: