How do I use a text box control value in DoCmd.TransferSpreadsheet

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I have a form with a button and 4 text boxes on it, the button has the
following click event:

Private Sub cmdImportFiles_Click()
GetExcelFileData Me
End Sub
I want to loop through the text boxes on the, which are going to contain
file paths, and do DoCmd.TransferSpreadsheet to import excel files into my
database. I am have some problems with this, especially with "ctrl.Value" in
the following code.

Function GetExcelFileData(frmForm as Form)
Dim ctrl as Control

For Each ctrl In frmForm
If TypeName(ctrl) = "TextBox" And ctrl.Name="txtInscope" Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,"Inscope
Table", ctrl.Value,True
End if
Next ctrl
End Function

Any ideas how to do this?
 
Omit the loop because you already know the control name you want to use.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,"[Inscope
Table]", Me.txtInscope.Value,True
 
Back
Top