I have written the following to extract data from specific cells in an Excel Workbook into an Access form. It works until I try to write the extracted values to the form fields. I get a Compile Error "Methos or Data Member Not Found" on "Me.FormFieldName.Value = CellVal". How can I correct this?
Gale
Code:
"Private Sub btnImportShortForm_Click()
'This sub imports data from the FFWB Short Form
Dim FilePath As String, SheetName As String, CellName As String, FormFieldName As String
Dim CellVal As Variant
Dim xl As Object
Dim wb As Object
Dim ws As Object
Dim FromCell As Variant, ToFormField As Variant
Dim n As Integer
FromCell = Array("D6", "D7", "D8", "D11", "D19", "D23", "D25", "D26", "D33", "D44", "D46")
ToFormField = Array("Txt_Group", "Txt_Customer", "Txt_ProjNum", "Txt_OppNum", "Txt_Product", "Txt_EngPlannerName", _
"Txt_SalesName", "Txt_BusUnit", "Txt_Amount", "Txt_InterIntra", "Txt_SWC")
'Path to Short Form & Sheet name. Sheet name never changes but the path will be selscted each time.
FilePath = "S:\2019\AccessTestFolder\OR1940044 FFWB SF.xlsm"
SheetName = "FFWB SF"
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(FilePath)
Set ws = wb.Sheets(SheetName)
For n = 0 To 10
'Get Value from Workbook
CellName = FromCell(n)
CellVal = ws.Range(CellName)
'MsgBox "Cell " & CellName
'MsgBox "Value " & CellVal
'Get Form Field Name from array
FormFieldName = ToFormField(n)
'Write value to Form Field
Me.FormFieldName.Value = CellVal <--- Error here
Next n
wb.Close
xl.Quit
Set ws = Nothing
Set wb = Nothing
Set xl = Nothing
End Sub
