# Automation using Excel Macro not working as expected

**URL:** <https://forums.toadworld.com/t/automation-using-excel-macro-not-working-as-expected/47196>\
**Category:** Toad Data Point & Toad Intelligence Central\
**Created:** [July 31, 2019, 6:36pm UTC](https://forums.toadworld.com/t/automation-using-excel-macro-not-working-as-expected/47196 "2019-07-31T18:36:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![frank.stumpo](https://avatars.discourse-cdn.com/v4/letter/f/838e76/32.png) [@frank.stumpo](https://forums.toadworld.com/u/frank.stumpo)\
**Post date:** [July 31, 2019, 6:36pm UTC](https://forums.toadworld.com/t/automation-using-excel-macro-not-working-as-expected/47196/1 "2019-07-31T18:36:10Z")

</div>

The code below is the beginning of my macro. I have set the automation script to clear data before import and this macro is set to run after export. I am expecting the macro to find the records returned by my query but it seems to run before the data is exported. This causes an exception since the column and row counts are set to 1. Why would the macro seem to run before the export when it is set to run after?

Sub FixUpSheet()  
Dim endRow, endCol As Long  
Dim m, r, c, i As Long  
Dim rng As Range

```
Set rng = Sheet1.UsedRange

ActiveWindow.FreezePanes = False
    
Cells(1, 1) = "Vendor Performance YTD for " & Year(Now())
Cells(1, 1).Font.Bold = True
Cells(1, 1).Font.Size = 15

Range("A4").Select
endCol = rng.Columns.Count
endRow = rng.Rows.Count

m = 1

For i = 3 To endCol Step 2
    
    Cells(3, i) = MonthName(m)
    Range(Cells(3, i), Cells(3, i + 1)).MergeCells = True
    Range(Cells(3, i), Cells(3, i + 1)).HorizontalAlignment = xlCenter
    Range(Cells(3, i), Cells(3, i + 1)).Font.Bold = True
    Cells(4, i) = "Cost"
    Cells(4, i).HorizontalAlignment = xlCenter
    Cells(4, i + 1) = "Qty"
    Cells(4, i + 1).HorizontalAlignment = xlCenter

    m = m + 1
    
Next i
```

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [August 1, 2019, 12:54pm UTC](https://forums.toadworld.com/t/automation-using-excel-macro-not-working-as-expected/47196/2 "2019-08-01T12:54:23Z")

</div>

I'm lost.

I get that you have a macro in Excel and it's not doing what (or when) you want it to.

Where does Toad fit into this picture?

-John

---

<div class="post-metadata">

**Author:** ![frank.stumpo](https://avatars.discourse-cdn.com/v4/letter/f/838e76/32.png) [@frank.stumpo](https://forums.toadworld.com/u/frank.stumpo)\
**Post date:** [August 1, 2019, 1:08pm UTC](https://forums.toadworld.com/t/automation-using-excel-macro-not-working-as-expected/47196/3 "2019-08-01T13:08:52Z")

</div>

I am using the Export Wizard in Toad Data Point.

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [August 1, 2019, 1:10pm UTC](https://forums.toadworld.com/t/automation-using-excel-macro-not-working-as-expected/47196/4 "2019-08-01T13:10:49Z")

</div>

Oh, OK. This message is in the Toad for Oracle forum. It's an entirely different product.

You should be able to edit the message and change the forum to Toad Data Point at the top. If you can't, let me know and I'll do it.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [August 2, 2019, 2:34am UTC](https://forums.toadworld.com/t/automation-using-excel-macro-not-working-as-expected/47196/5 "2019-08-02T02:34:05Z")

</div>

Can you turn on logging by adding /log=all to your shortcut. Run the export and post the Toad.log file that is located in your AppData Dir. There is link in the About box.

---

<div class="post-metadata">

**Author:** ![frank.stumpo](https://avatars.discourse-cdn.com/v4/letter/f/838e76/32.png) [@frank.stumpo](https://forums.toadworld.com/u/frank.stumpo)\
**Post date:** [August 2, 2019, 11:26am UTC](https://forums.toadworld.com/t/automation-using-excel-macro-not-working-as-expected/47196/7 "2019-08-02T11:26:14Z")

</div>

I will try to do this, but I have more information. The SQL running Creates and Drops several Temporary tables creating a PIVOT using several PREPARE statements, then presenting PIVOT in a final query using a SELECT that I was hoping would go to an Excel sheet.

I changed the SQL to create a permanent table of the detail records and insert those records to it. Then I run the Excel driven automation that simply selects those records from that table and create the PIVOT in Excel using the macro after. Everything works as expected. I would rather do the PIVOT in my SQL because I have more control over the formatting and presentation in Excel, over using the built-in Excel pivot.