Importing Excel Sheet into Access Table - First row Heading

[skahmed]

I am trying to import an excel worksheet into a new Access Table using the following code:

Private Sub ImportXLSheets()
Dim WrksheetName As String
Dim i As Integer
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Visible = True
xl.Workbooks.Open "T:\FINANCE\Man_Acc\Month end\CustPL Extracts\Upload Files\Sep11 v2.1.xls"
With xl
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).Name
DoCmd.TransferSpreadsheet (acImport), acspreadsheettypeexcel2003, WrksheetName, "T:\FINANCE\Man_Acc\Month end\CustPL Extracts\Upload Files\Sep11 v2.1.xls", yes
Next i
End With
End With
Set xl = Nothing
End Sub

Problem is that the first row of the excel worksheet is not appearing at the column header fields in the Access table.

Can someone help please?

Thanks

 

[vbaInet]

Yes is not boolean. If you CBool(Yes) it will return False, hence, your TransferSpreadsheet code is translating your Yes to, "does not have Column Headers". Change Yes to True.

DoCmd.TransferSpreadsheet (acImport), acspreadsheettypeexcel2003, WrksheetName, "T:\FINANCE\Man_Acc\Month end\CustPL Extracts\Upload Files\Sep11 v2.1.xls", [B][COLOR=darkgreen]True[/COLOR][/B]

Always use True or False (generic) or -1 and 0 (VBA specific).