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