Good Morning,
I am currently running Toad Data Point 5.2.1.bugs.1409(64 Bit). I have an automation that is running which exports 8 different SQL files to their individual worksheets within the macros enabled file. The first Export Wizard Automation runs a clear all VBA script before the export, which runs with no issues, then on the final Export Wizard, I am running a formatting VBA after the export which loops through 6 of the 8 tabs, and formats them to how the client wants. That is where the error is popping up. When it hits the Macros I get the following error "Cannot run the macro ''Milestones for RMS.xlsm'!MergeWS'. The macro may not be available in this workbook or all macros may be disabled". The VBA in question runs fine manually, but Toad doesn't seem to recognize that it is able to be used. I will respond to this topic with the report log and VBA
VBA:
Option Explicit
Option Compare Text
Sub MergeWS()
Application.DisplayAlerts = False
Dim WS As Worksheet
Dim RNG As Range
For Each WS In Sheets
If WS.name <> "Pivot Data" And WS.name <> "Master" Then
WS.Activate
Range("A1", Range("W" & Rows.Count).End(xlUp)).Borders.LineStyle = xlContinuous
MergeCells:
Range("A1", Range("C" & Rows.Count).End(xlUp)).Select
For Each RNG In Selection
If RNG.Value = RNG.Offset(1, 0).Value And RNG.Value <> "" Then
Range(RNG, RNG.Offset(1, 0)).Merge
Range(RNG, RNG.Offset(1, 0)).HorizontalAlignment = xlCenter
Range(RNG, RNG.Offset(1, 0)).VerticalAlignment = xlCenter
GoTo MergeCells:
End If
Next RNG
End If
Next WS
End Sub
1/14/2021 9:11:10 AM: Connection description = dbpartner1:55501,dbpartner2:55502 (R3p0t1ng),
1/14/2021 9:11:10 AM: Connection node found
1/14/2021 9:11:10 AM: Connection to: dbpartner1:55501,dbpartner2:55502 (R3p0t1ng),
1/14/2021 9:11:10 AM: Connection successful
1/14/2021 9:11:10 AM: Connection logon: R3p0t1ng
1/14/2021 9:11:10 AM: Last schema: exodus
1/14/2021 9:11:10 AM: Default schema:
1/14/2021 9:11:10 AM: Auto Commit: True
1/14/2021 9:11:10 AM: Running export template \nfrnas11\Reports\WFHM\Toad\VAM\Miestones for RMS\RMSDenial2.txp
1/14/2021 9:11:11 AM: Executing export using connection dbpartner1:55501,dbpartner2:55502 (R3p0t1ng), is not XQuery Connection
1/14/2021 9:11:11 AM: ValidateSuffix: Using base suffix:
1/14/2021 9:11:11 AM: ValidateSuffix: Suffix is predefined literal - returning empty suffix instead
1/14/2021 9:11:11 AM: Beginning export: <Quest.Toad.ImportExport.ExportEngine>=31 then '31+ days' when [Days At|Milestone] >= 16 then '16-30 Days' else '0-15 days' end as Days_At_Milestone_Bucket, [Estimate Description], [Bid Amount], [Milestone Type], [Bid|Status], [Detail], [Denial|Reason], [Milestone|Updated By], [LoanType], [Addr], [City], [ST], [Zip], [Asset Manager] FROM Milestones_CUST WHERE CustID = 'RMS' AND Bucket = 'ISN Denials' ORDER BY [Loan #], [WO #];]]></Quest.Toad.ImportExport.ExportEngine>
1/14/2021 9:11:11 AM: Export Started [1/14/2021 9:11:11 AM]
1/14/2021 9:11:11 AM: ** START **
1/14/2021 9:11:11 AM: Export using connection: dbpartner1:55501,dbpartner2:55502 (R3p0t1ng),
1/14/2021 9:11:11 AM: Export Wizard: Building object list
1/14/2021 9:11:11 AM: Build List done.
1/14/2021 9:11:11 AM: Exporting Data (1 of 1)
1/14/2021 9:11:11 AM: BEGIN Query :
1/14/2021 9:11:11 AM: Object SQL Query started.
1/14/2021 9:11:11 AM: InternalReadBackground - after ExecuteReader - command SELECT ID, [Loan #], [Last 2], [FHA Case|Number], [WO #], [Marketable Title Exp Date], [Loan Status], [Sub Status], [Bid Release Date], [Days At|Milestone], Case when [Days At|Milestone]>=31 then '31+ days' when [Days At|Milestone] >= 16 then '16-30 Days' else '0-15 days' end as Days_At_Milestone_Bucket, [Estimate Description], [Bid Amount], [Milestone Type], [Bid|Status], [Detail], [Denial|Reason], [Milestone|Updated By], [LoanType], [Addr], [City], [ST], [Zip], [Asset Manager] FROM Milestones_CUST WHERE CustID = 'RMS' AND Bucket = 'ISN Denials' ORDER BY [Loan #], [WO #]
1/14/2021 9:11:11 AM: Excel version = 14
1/14/2021 9:11:15 AM: DoneReadTreadWriter
1/14/2021 9:11:15 AM: Writing rows 0 - 129
1/14/2021 9:11:15 AM: Writing rows Finalize
1/14/2021 9:11:20 AM: Executing macros from Excel.
1/14/2021 9:11:20 AM: Loading Excel Assemblies
1/14/2021 9:11:20 AM: Trying Excel 2013
1/14/2021 9:11:20 AM: Copying macro file from \NfrNas11\Reports\WFHM\Toad\VAM\Miestones for RMS\Milestones for RMS.xlsm to C:\Users\esteele\AppData\Local\Temp\Milestones for RMS.xlsm...
1/14/2021 9:11:21 AM: Checking if copied file exists: True
1/14/2021 9:11:21 AM: Creating Dekstop folder for systemprofile: C:\Windows\system32\config\systemprofile\Desktop...
1/14/2021 9:11:21 AM: Creating Dekstop folder for systemprofile: C:\Windows\system32\config\systemprofile\Desktop...Failure
1/14/2021 9:11:21 AM: Opening file C:\Users\esteele\AppData\Local\Temp\Milestones for RMS.xlsm...
1/14/2021 9:11:23 AM: Obtaining original excel automation security...
1/14/2021 9:11:23 AM: Setting excel automation security to enable all macros...
1/14/2021 9:11:23 AM: Running macro: 'Milestones for RMS.xlsm'!MergeWS
1/14/2021 9:11:23 AM: First-time exception when invoking Run('Milestones for RMS.xlsm'!MergeWS)
1/14/2021 9:11:23 AM: Error executing macro
1/14/2021 9:11:23 AM: Exception has been thrown by the target of an invocation.
1/14/2021 9:11:23 AM: Cannot run the macro ''Milestones for RMS.xlsm'!MergeWS'. The macro may not be available in this workbook or all macros may be disabled.
1/14/2021 9:11:23 AM: The user account specified to run this script on a server is not configured to execute macros in Excel. To resolve this issue, see “Macro-Enabled Excel Files” in Toad Help.
1/14/2021 9:11:27 AM: Deleting existing temp file: C:\Users\esteele\AppData\Local\Temp\Milestones for RMS.xlsm...
1/14/2021 9:11:27 AM: Deleting existing temp file: C:\Users\esteele\AppData\Local\Temp\Milestones for RMS.xlsm... Success
Hello,
this looks like it's going need to open a support request. Can you also attach the excel file with the macro to the case? Thanks