Ok - I could not remember the exact thing - when I was just looking for it - so I called my buddy.
Go to Trust Center - Trust Center Settings... - File Block Settings - then Check to Open for Excel 2-4 Worksheets and Workbooks, also Excel 2-4 Macrosheets!
That should do it!!!
On 9/23/2016 4:37 PM, Kurt at VR-FX wrote:
You're security crap sounds VERY Familiar! Like something we had a problem with and a QA buddy of mine found the resolution when he was testing something else. Let me get back to you shortly with hopefully an answer!
-K-
On 9/23/2016 4:14 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2016-09-21 10:38, Kurt Wendt wrote:
Mike - Here you go.
Here's some of the code in our system: tab2_macro = ssMacroName+"!Module3.Tab2" oExcel.WINDOWS(JUSTFNAME(ssName)).ACTIVATE oExcel.Sheets("Account Level").SELECT oExcel.RANGE("A2").SELECT
After the above code runs - then this is the call to the Macro: oExcel.APPLICATION.RUN(tab2_macro)
When I ran my adaptation, I got an error about Security settings, despite my Excel setting being to Allow macros to be run (least secure). Do you still do this today or is this logic dated perhaps to a time prior to M$ locking everything down?
I also got out my famous Microsoft Office Automation With Visual FoxPro book by Tamar and Della (edited by our very own Ted Roche) and was trying to use the example on pages 206-207 whereby I created the macro code into a text file and then added it in and ran it, but with the same error about security as well.
:-(
I basically ended up running loops to create the following commands which I stuffed into Call_It and then let it used my routine which worked fine. But this isn't the automation I was hoping to achieve. I'm sure it can be done; I'm just still searching for the way around the "security" error roadblocks.
Sub Call_it() Application.DisplayAlerts = False
Excel.Workbooks.Add Call Create_OP_File("210001", "v_op_210001_1", "1", False) Call Create_OP_File("210001", "v_op_210001_2", "2", False) Call Create_OP_File("210001", "v_op_210001_3", "3", True) ActiveWorkbook.Close Excel.Workbooks.Add Call Create_OP_File("210002", "v_op_210002_1", "1", False) Call Create_OP_File("210002", "v_op_210002_2", "2", False) Call Create_OP_File("210002", "v_op_210002_3", "3", True) ActiveWorkbook.Close' ...and this goes on for 53 more different provider numbers, each with 3 file type calls like the examples above
End Sub
Sub Create_OP_File(ByVal tcProvNum As String, ByVal tcView AsString, _ ByVal tcRecType As String, _ ByVal EOFFLag As Boolean) ' load the outpatient record types for given provider Dim lcSQL As String Dim lcFilename As String lcSQL = "SELECT * FROM " + tcView With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DATABASE=mydatabase;DESCRIPTION=MyData;DSN=My_DSH;OPTION=69533696;PORT=3306;SERVER=10.8.20.109;UID=userid;", Destination:=Range("$A$1")).QueryTable .CommandType = 2 .CommandText = Array(lcSQL) .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceConnectionFile = "G:\Mike\outpatient.odc" .Refresh BackgroundQuery:=False End With ActiveSheet.Name = "Record Type " + tcRecType If Not EOFFLag Then Sheets.Add After:=ActiveSheet Else lcFilename = "c:\crap" + tcProvNum + "_op.xlsx" ActiveWorkbook.SaveAs Filename:=lcFilename, FileFormat:=xlOpenXMLWorkbook, Password:="mypwd", CreateBackup:=False End If End Sub
[excessive quoting removed by server]