VFP9SP2. Data is in MySQL (MariaDB) database on a different machine (at
10.8.20.109). Trying to use VFP to generate Excel files for 55
different outputs (providers), with each workbook/file having a tab for
each record type (which is usually 3 types: 1, 2, and 3). My initial
concern is how to deal with the named parameters in the first
ListObjects.Add line, as VFP doesn't work like that. Note that the _ is
a continuation character for VBA code (like the semi-colon is for VFP
code), so that first WITH line goes to the QueryTable word. I used VFP
to dynamically create the views for each of the 55 providers, so there's
about 165 views. Trying to do all this via automation for ease and
accuracy. Nobody wants to create 165 imports and 55 saves manually!!!
I already created a ODBC connection called tim_dsh to connect to the
remote database. (Yes, using the root user remotely isn't good
practice. Move on from that. This is internal!)
For this example, provider is 210001 and views are
v_op_<provnum>_<rectype>.
VBA code:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DATABASE=tim_dsh;DESCRIPTION=Tim's DSH
data;DSN=Tim_DSH;OPTION=69533696;PORT=3306;SERVER=10.8.20.109;UID=root;"
_
, Destination:=Range("$A$1")).QueryTable
.CommandType = 0
.CommandText = Array("SELECT * FROM `tim_dsh`.`v_op_210001_1`")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\mbabcock\Documents\My Data Sources\tim_dsh
v_op_210001_1.odc"
.ListObject.DisplayName = "Table_tim_dsh_v_op_210001_1"
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Record Type 1"
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DATABASE=tim_dsh;DESCRIPTION=Tim Forry's DSH
data;DSN=Tim_DSH;OPTION=69533696;PORT=3306;SERVER=10.8.20.109;UID=root;"
_
, Destination:=Range("$A$1")).QueryTable
.CommandType = 0
.CommandText = Array("SELECT * FROM `tim_dsh`.`v_op_210001_2`")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\mbabcock\Documents\My Data Sources\tim_dsh
v_op_210001_2.odc"
.ListObject.DisplayName = "Table_tim_dsh_v_op_210001_2"
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Record Type 2"
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DATABASE=tim_dsh;DESCRIPTION=Tim Forry's DSH
data;DSN=Tim_DSH;OPTION=69533696;PORT=3306;SERVER=10.8.20.109;UID=root;"
_
, Destination:=Range("$A$1")).QueryTable
.CommandType = 0
.CommandText = Array("SELECT * FROM `tim_dsh`.`v_op_210001_3`")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\mbabcock\Documents\My Data Sources\tim_dsh
v_op_210001_3.odc"
.ListObject.DisplayName = "Table_tim_dsh_v_op_210001_3"
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Record Type 3"
ChDir "G:\somepath"
ActiveWorkbook.SaveAs Filename:= _
"G:\somepath\21001_outpatient.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Tips/ideas appreciated. Thanks!!!
--Mike
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/6628541a317dae2a4b46309438b43...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
