Why there is always some empty rows transfered to Access ?

[msand]

I used following code developed by one of friends here to transfer data from Excel to Access. However, no matter how many data rows in my excel spreadsheet include, when the transfer is done. The first 28 rows in access are always empty. I am really confused about this. Anyone has similar experience or where is the problem from? Really appreciate your guidance.


Here is the code:
--------------------------------------------------------------------------------------
Sub DataTransfer()

'Transfer data from Excel to Access
Dim appAccess As Access.Application
Set appAccess = New Access.Application
ActiveWorkbook.Names.Add Name:="TransferRange", RefersToLocal:=Range("A1:T" & Range("A65536").End(xlUp).Row)


appAccess.OpenCurrentDatabase "C:\Documents and Settings\msand\Desktop\Monthly Report Database.mdb"

appAccess.DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, _
"tblmtlyReport", _
ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, _
True, "TransferRange"


appAccess.CloseCurrentDatabase
appAccess.Quit

MsgBox "Transfer Complete", vbOKOnly, "Data Transfer"

End Sub
-------------------------------------------------------------------------------------

 

[bat17]

I cant see anything wrong with the code but I am not familiar with the "RefersToLocal" when creating a name.
Are the 28 blank rows additional rows or missing data?
Are you sure that the 28 blank rows are at the start of your data and not the last 28 rows of the previous import?
A couple of things that might be worth trying out to narrow down where the problem is coming from is setting the range manually and "Remming" out the line of code that creates the named range. If the data transfers correctly now then the problem is in the range creation.
Having created the range try closing and saving the workbook and then running the TransferSpreadsheet from Access itself.

HTH

Peter