Import part of the data from Excel to Access use VBA

[caiyan]

I tried to find related topic to solve this problem but cannot find, hope someone can help me and give some suggestion.
Recently I am using this code to import the data in a excel sheet into Access table:

 DoCmd.TransferText acImport, , strcTableName, strFullPath, True

But this will import all the data in that worksheet into the Access table.
What I want to implement is to import part of the data. For example, the data I need is start from Row30 till the end, thus I don't need the data from Row1 to Row29.
Is there a way for me to implement this? Thanks very much for your help if possible.

[June7]

You can import a specified range http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Or set a link to the worksheet and use the linked sheet as a data source just like a table (except can't edit the worksheet).

If linking to the worksheet is not practical, the only other approach I know is to open the worksheet as an object in VBA and read in the data one cell at a time.

[caiyan]

Hi June7, I think read in data one cell at a time is a good way to start. Actually I implement the code which can import the data start from a defined row, fro example in the code I implement it start the data from A22. However, I still have no idea how to define the end point. In the code, all of the data start from A22 will be imported into Access table, but for example I only want the data from row 22 to raw 33. Do you have any idea how implement this so that I not only can define the start point but also the end point? The code I developed is shown as:

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
blnEXCEL = False

Const strcPath As String = _
"C:\Documents and Settings\YuC\My Documents\Downloads\"
Const strcNewPath As String = _
"C:\Documents and Settings\YuC\My Documents\Downloads\saved csv file\"

Const strcTableName As String = "RawData"

Dim strPath As String
Dim strNewPath As String
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim strFullPath As String
Dim strFullNewPath As String

' See if path constant ends in a backslash:
If Right(strcPath, 1) = "\" Then
strPath = strcPath
Else
strPath = strcPath & "\"
End If
' See if new path constant ends in a backslash:
If Right(strcNewPath, 1) = "\" Then
strNewPath = strcNewPath
Else
strNewPath = strcNewPath & "\"
End If

' Loop through the Excel files in the folder
' (if any) and build file list:
strFile = Dir(strPath & "*.xls")
While strFile <> ""
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend

' See if any files were found:
If intFile = 0 Then
Application.Quit
End If

' Loop through the list of files:
For intFile = 1 To UBound(strFileList)

' Initialise paths:
strFullPath = strPath & strFileList(intFile)
strFullNewPath = strNewPath & strFileList(intFile)

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = False

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open(strFullPath, , True) ' opens in read-only mode


' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("Sample")


' Replace A1 with the cell reference from which the first data value
' (non-header information) is to be read
Set xlc = xls.Range("A22") ' this is the first cell that contains data
Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("RawData", dbOpenDynaset, dbAppendOnly)

' write data to the recordset
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

' Close the EXCEL file without saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

' Copy file to new location:
FileCopy strFullPath, strFullNewPath
' Delete old file:
Kill strFullPath
Next
Application.Quit
Exit_Import_From_Excel:
Exit Function

[June7]

Why stop at row 33? You want only 11 records? Will this always be the case? You can do a For Next loop.

For i = 1 to 11

or

For I = 22 to 33

[caiyan]

Yeah, the case is that I only need specified rows'data, do u know how to implement the for next loop in this code:

' write data to the recordset
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

As what I get the code from "google" and not quite understand it. Is it possible to implement the for next loop inside to get what I need? Thanks for your help if possible.

[June7]

Instead of the Do and Loop lines:
For i = 1 to 11
...
Next

[caiyan]

Thanks so much, it works very well.