importing a worksheet (excel)



I have written a function that alows one to import a worksheet into
an access table. The problems is that i want to start importing the worksheet
from row 4. I have tried this by using the method AbsolutePosition but VBA
doesn't seem to like that. Below is the code:

Dim pstrTitle As String, pstrFilter As String, pstrInitialDir As String,
InFile As String

pstrTitle = "Select Worksheet"
pstrFilter = "Excel (xls)|*.xls"
pstrInitialDir = "F:\ABCDATA\Downloads"
InFile = OpenFileNameDlg(pstrTitle, pstrFilter, pstrInitialDir)
If InFile = "" Then Exit Function

Dim dbExcel As Database
Dim thisdb As Recordset
Dim db As Database
Dim rsimp As Recordset
Dim i As Integer
Dim intpressedcancel As Integer
Dim strName As Variant
Dim criteria As String

Dim strsql As String

Set db = CurrentDb
Set rsimp = db.OpenRecordset("tblCharges", dbOpenDynaset)

'Open excel sheet as database rather than as excel object
Set dbExcel = OpenDatabase(InFile, False, True, "Excel 8.0")

'Lookup name of first sheet
strName = dbExcel.TableDefs(i).Name
Set thisdb = dbExcel.OpenRecordset(strName, dbOpenSnapshot)

'code to skip importing of headings
thisdb.AbsolutePosition = 4
Do While Not thisdb.EOF
If Not IsNull(thisdb.Fields(0).Value) Then

If Not IsNumeric(thisdb.Fields(0)) Then
rsimp!PhoneNumber = thisdb.Fields(0)
If Not IsNumeric(thisdb.Fields(1)) Then
rsimp!SimCard = thisdb.Fields(1)
If Not IsNumeric(thisdb.Fields(2)) Then
rsimp![Service Class] = thisdb.Fields(2)
If Not IsNumeric(thisdb.Fields(3)) Then
rsimp!Category = thisdb.Fields(3)
If Not IsNumeric(thisdb.Fields(4)) Then
rsimp!Service = thisdb.Fields(4)
If Not IsNumeric(thisdb.Fields(5)) Then
rsimp!From = thisdb.Fields(5)
If Not IsNumeric(thisdb.Fields(6)) Then rsimp!To
= thisdb.Fields(6)
If IsNumeric(thisdb.Fields(7)) Then
rsimp!NrofCalls = thisdb.Fields(7)
If IsNumeric(thisdb.Fields(8)) Then rsimp!KBytes
= thisdb.Fields(8)
If IsNumeric(thisdb.Fields(9)) Then rsimp!Amount
= thisdb.Fields(9)

i = i + 1
End If

Set thisdb = Nothing
Set rsimp = Nothing

MsgBox i & " record(s) imported."

Does anyome knows the correct syntax to start importing
from a specific row ina worksheet. Is this possibel at all?


Hi Eric,

You can just iterate through the first four rows, e.g.

For i = 1 to 4

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question