Toad World® Forums

Automation using Excel Macro not working as expected

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

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

I am using the Export Wizard in Toad Data Point.

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.

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.

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.