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 As
String, _
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
