Import Excel Sheet via VBA into Access

[junkie8]

Can someone tell me how to import excel sheet into access? I know how to import a text file, but do not know how to do it for excel

Dim strname As String
Dim DataRead, db, rst, Counter, iPos
Dim arList
Dim d As Date
Dim dList
Dim num As Integer
Dim str As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM PDALOGS")

With rst
strname = "C:\Documents and Settings\me\DeskTop\NavStructure.txt"
num = 8

Open strname For Input As #1
While Not EOF(1)
Line Input #1, DataRead
arList = Split(DataRead, " ")
.AddNew
iPos = 0
For Counter = 0 To num
If Counter = 1 Then
.Fields(Counter) = txtSessionDate
ElseIf Counter = 4 Then
.Fields(Counter) = CDate(arList(iPos))
iPos = iPos + 1
Else
If arList(iPos) <> "" Then
.Fields(Counter) = Val(arList(iPos))
iPos = iPos + 1
End If
End If
Next
.Update
Wend
Close #1
.Close
End With

DoCmd.OpenTable "INTERACTIONS", acViewNormal, acReadOnly
DoCmd.Close acTable, "INTERACTIONS", acSaveYes

 

[PHV]

Have a look at the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[junkie8]

Thanks that worked, but I am only able to import first excel sheet. How do I modify my code to get other sheets as well.

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 8, "Interactions", _
"C:\Documents and Settings\bchaudry\DeskTop\Logs\NavStructure.xlsm", True, "A1:E1307"
End Sub

 

[PHV]

You may try either this:
, "Sheet2!A1:E1307"

or this:
, "Sheet2$"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886