Import Data From Excel: Geting an error Field F1 doesnot exit (error)2391 (1 Viewer)

sbaud2003

Member
Local time
Tomorrow, 03:38
Joined
Apr 5, 2020
Messages
170
my codes are as follows:

Function importscroll()

Dim nameList()
wkbookPath = "" & Application.CurrentProject.Path & "\Master\scroll.xlsx"
Dim duplicatescroll As Integer
Dim countscroll As Integer
On Error Resume Next
filePath = Dir(wkbookPath)
On Error GoTo 0
If filePath = "" Then
MsgBox "File not found in " & Application.CurrentProject.Path & "\Master\ folder. Please place employeemaster.xlsx in Master folder.", vbInformation, "File not Found"
Else
Dim XL As Object
Set XL = CreateObject("Excel.Application")
With XL
.Visible = False
.DisplayAlerts = False
.Workbooks.Open wkbookPath
For Each ws In XL.Worksheets
ReDim Preserve nameList(counter)
nameList(counter) = ws.NAME
counter = counter + 1
Next
.ActiveWorkbook.Close (True)
.Quit
End With
Set XL = Nothing
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from Tbl_Emergency_Linking"
DoCmd.SetWarnings True


MsgBox "Table Scroll Linking will be updated from " & Application.CurrentProject.Path & "\Master\Scroll.xlsx. Please place the Industrial Employee file at requisite location for importing data."

For i = LBound(nameList()) To UBound(nameList())
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Tbl_Emergency_Linking", "" & Application.CurrentProject.Path & "\Master\Scroll.xlsx", True, nameList(i) & "!A1:IU9999"
Next i

MsgBox "Scroll Updated Successfully!"


End If


End Function
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:08
Joined
Feb 28, 2001
Messages
27,001
Just as a suggestion for readability, move all of the embedded DIM statements to a place before the first executable statement.
You might also want to use the code tags ( that look like < / > in the menu bar for each post) to insert your code with indentation. It is very difficult to read as presented.

Finally, can you identify the line on which you encounter this error? Is it the DoCmd.TransferSpreadSheet acImport line? If so, that line is in a loop. Do you get that error in the first iteration of the loop or a later iteration?
 

Users who are viewing this thread

Top Bottom