I have an Excel 2007 workbook that contains an ODBC data connection (to FoxPro, if that matters). The connection is set to "refresh data when opening the file."
When I go into File Explorer and open the workbook, the data populates into the spreadsheet as it should. However, when I execute a function in Access VBA that opens the workbook, the data from the ODBC connection does not populate.
Why would it make a difference which way the workbook is opened? And more importantly, how can I get the data to populate when the workbook is opened via Access VBA?
Here is the Access VBA code that opens the workbook:
Public Sub Subform_cmdOpenFile_Click(frm As Form)
Dim rs As Recordset
Dim ftiSuperclass As FilingTemplateInterface
Set rs = frm.RecordsetClone
If (rs.BOF Or rs.EOF) Then GoTo PROC_EXIT
Set ftiSuperclass = New FilingTemplateInterface
ftiSuperclass.ShowWorkbook rs!Directory & frm!Filename
PROC_EXIT:
On Error Resume Next
rs.Close
Set rs = Nothing
ftiSuperclass.QuitExcel
Set ftiSuperclass = Nothing
Exit Sub
PROC_ERROR:
Resume PROC_EXIT
End Sub
Friend Sub ShowWorkbook(strFilename As String)
Dim fso As New Scripting.FileSystemObject
Dim appExcel As New Excel.Application
appExcel.Workbooks.Open Filename:=strFilename, AddToMRU:=True
appExcel.visible = True
Set appExcel = Nothing
End Sub