Is there a way to modify TransferSpreadsheet Action in VBA?

[Guest]

I want to import excel files, but not be limited to having a set file name
like using the Action through the Macro function. I was wondering if you
could modify the transferspreadsheet action in VBA, making it more flexible?
Is there a way to perhaps, use something similiar to the QueryDef, but for
the Transfer Actions? Thanks.

 

[Guest]

Here is an example that does an export, but it would work the same for an
import with minor changes:
Private Sub cmdExport_Click()
Dim blnCancelSave As Boolean

On Error GoTo cmdExport_Err
Do Until blnCancelSave
'Set up the Default path and file
strDefaultDir = Left(varGetFileName, InStrRev(varGetFileName, "\"))
strFileName = "SAP Feed Matrix " & strCurrMonth & " " & strCurrYear
& ".xls"
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT
'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strfilter, _
filename:=strFileName, _
Flags:=lngFlags, _
DialogTitle:="Save SAP Feed Matrix")
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
blnCancelSave = MsgBox("Cancel File Save", vbQuestion + vbYesNo, _
"Save SAP Feed Matrix") = vbYes
Else
DoCmd.Hourglass True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"tblAdjustedActualsValidate", varGetFileName, True
blnCancelSave = True
End If
Loop
cmdExport_Exit:
DoCmd.Hourglass False
Exit Sub
cmdExport_Err:
MsgBox Error$
GoTo cmdExport_Exit
End Sub

You will need the API at this link:
http://www.mvps.org/access/api/api0001.htm