unction importData()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ws As Excel.Application
Dim i As Long
'***************************************************************************************
'The following code was created by Trevor G
'In Jan 2011
'The purpose of the code is to repopulate the temp import table named "tblTempImport"
'***************************************************************************************
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set ws = CreateObject("Excel.Application")
With ws
.Workbooks.Open CurrentProject.Path & "\CSIINPUT.xls"
.Visible = True
End With
Dim lngCount As Long
rst.Open "tblTempImport", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
ws.Sheets("upload sheet").Select
ws.Range("a2").Select
Do Until ws.ActiveCell.Value = ""
With rst
.AddNew
.Fields("CUST KEY").Value = ws.ActiveCell.Offset(0, 0).Value
.Fields("BU KEY").Value = ws.ActiveCell.Offset(0, 1).Value
.Fields("AM KEY").Value = ws.ActiveCell.Offset(0, 2).Value
.Fields("AREA").Value = ws.ActiveCell.Offset(0, 3).Value
.Fields("QUESTION NO").Value = ws.ActiveCell.Offset(0, 4).Value
.Fields("QUESTION").Value = ws.ActiveCell.Offset(0, 5).Value
.Fields("IMPORTANCE").Value = ws.ActiveCell.Offset(0, 6).Value
.Fields("ACF PERFORMANCE").Value = ws.ActiveCell.Offset(0, 7).Value
.Fields("COMPETITOR").Value = ws.ActiveCell.Offset(0, 8).Value
.Fields("IMPROVED / DECLINED").Value = ws.ActiveCell.Offset(0, 9).Value
.Fields("YES / NO").Value = ws.ActiveCell.Offset(0, 10).Value
.Fields("COMMENTS").Value = ws.ActiveCell.Offset(0, 11).Value
.Update
lngCount = lngCount + 1
End With
ws.ActiveCell.Offset(1, 0).Select
Loop
ws.Range("A1").Select
rst.Close
ws.Quit
End Function
