Loop not entirely working 1

[hsingh1981]

Hi all,

i have several excel sheets in my folder. I tell ms access database to open each file and copy the data in to the access table. This only works for the first file...i can't seem to copy the data and append to the table. I seem to going wrong somewhere. Could some one have a look and see where i've gone wrong?

Dim MyProperPath As String
Dim myRec As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlWrksht As Excel.worksheet

'Name of Table
Set myRec = CurrentDb.OpenRecordset("T_ExcelData")
'Excel sheet creat and finding path
Set xlApp = CreateObject("Excel.Application")


'Make sure the path is in the right format
If Right(Directory, 1) <> "\" Then
  MyProperPath = Directory & "\"
Else
  MyProperPath = Directory
End If

'Make sure the path is in the right format
MyProperPath = "U:\My Documents\Databases\ExcelFiles\"


'Find the first file in the directory
MyFileName = Dir(MyProperPath & "*.xls*")


Do While MyFileName <> ""


Set xlWrksht = xlApp.Workbooks.Open(MyProperPath & MyFileName).Worksheets("Tot ward mon")
MsgBox MyFileName
myRec.AddNew

myRec.Fields("HandHygieneDate") = xlWrksht.cells(4, "A")
myRec.Fields("Ward") = xlWrksht.cells(4, "B")

myRec.Fields("Nurses_ObsHandHygiene") = xlWrksht.cells(4, "C")
myRec.Fields("Nurses_Opportunities") = xlWrksht.cells(4, "D")
myRec.Fields("Nurse_DailyCompliance") = xlWrksht.cells(4, "E")



MyFileName = Dir

Loop

myRec.Update
MsgBox "updated"

End Sub

many thanks

 

[Remou]

You have update outside the loop.

<...>


myRec.Update
MsgBox "updated"

MyFileName = Dir

Loop

 

[hsingh1981]

Am such muppet....almost there too. Many thanks for your reply.