VBA code(transfer data from excel)

[yn19832]

I want to transfer an excel spreadsheet to Access, which contains 9 sheets.I can not directly import the excel data, as I need to dispaly data with different forms in Access. I have almost done all the codes with VBA, the problem now is I want to creat a loop to call a function dealing with different sheets in the Excel.

My codes are as following:


Sub UpdateIndexes()
Dim rec As DAO.Recordset, inad1 As Address, i As Integer,
Set db = CurrentDb
Set rec = db.OpenRecordset("tblIndex")
inad1.Wbook = "\\c\file\*.xls"
Excel.Workbooks.Open inad1.Wbook, False, True
With ActiveWorkbook

I do not know about this part. The excel contains 9 sheets, for each
sheet, I want to call a function I have already defined . I do not know how to call that function within each sheet.


End With
End Sub


I would be grateful if anyone can help me sort it out.

[OBP]

I use this to import Worksheets, so it should work to step through them to do what you want.
Being Access orientated I would have imported the data in to Access in to temprorary tables and then used Queries to put the Data in to the relevant Tables.


Set Wkb = AppExcel.Workbooks.Open(excelName)
For Each Wksh In Wkb.Worksheets
Set xlRng = Wksh.UsedRange
DoCmd.TransferSpreadsheet acImport, , "tbl_Temp", Me.selFileName, True, Range:=Wksh.name & "!a1:co500"
Next Wksh