Import Excel file to SQL table

Failing this here is some code to get you started;

Firstly to read from the excel sheet you will need some OLE, are the sheets in the same format each time,  if not you may need to do some serious thinking.

Read Excel Sheet:-
Dim xl As New Excel.Application
   Dim xlw As Excel.Workbook

   ' Open the excel file.
   ' replace the "c:\myDir\book1.xls" below with your excel file name.
   Set xlw = xl.Workbooks.Open("c:\myDir\book1.xls")

   ' replace "Sheet1" with the sheet that you want to get the data from it.
   xlw.Sheets("Sheet1").Select

   ' Get the value from cell(2,3) of the sheet.
   MsgBox xlw.Application.Cells(2, 3).Value

   ' Close worksheet without save changes.
   ' If you want to save changes, replace the False below with True
   xlw.Close False

   ' free memory
   Set xlw = Nothing
   Set xl = Nothing

'Code from Ark Q.20191560.

Then to put the data into a SQL server database, here is some code I have used successfully:-


Public Function AddData(ExcelData As String, ServerGroup As String, DatabaseName, login, Password)
Dim dbsData As Database, strConnect As String
Dim strData As String

strConnect = "ODBC;Driver=SQL Server;Server=" & ServerGroup & ";DATABASE=" & DatabaseName & ";UID=" & login & ";PWD=" & Password
                 
Set dbsData = OpenDatabase("", False, False, strConnect)
    strData = "insert into TableName (FieldNames) VALUES (" & Chr(39) & ExcelData & Chr(39) & ")"
    With dbsData
           .Execute strData, dbSQLPassThrough
           
    End With
    dbsData.Close
    Set dbsData = Nothing
If Errors.Count > 0 Then
   Dim loError As Error

   For Each loError In Errors
       MsgBox "Error: " & loError.Number & vbCrLf & "Description: " & loError.Description

   Next
End If

End Function