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
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