Is there a way to modify TransferSpreadsheet Action in VBA?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Back
Top