Last Row Count, Last Column Name and Number

[Thompyt]

I can find all sorts of ways to do this in excel, but not in Access. Getting the Excel file inported is easy:

Option Explicit
Private Sub Start_Click()


Dim rS As DAO.Recordset
Dim F As Object
Dim VarItem As Variant
Dim c As Integer
Dim Last_Row_Num As Long, Last_Col_Num As Long
Dim Last_Col As String, , strFile As String

Set F = Application.FileDialog(1)
       F.AllowMultiSelect = False
    
    If F.Show Then
        For Each VarItem In F.SelectedItems
            strFile = Dir(VarItem)
            strFolder = Left(VarItem, Len(VarItem) - Len(strFile))
        Next
    End If

 DoCmd.TransferSpreadsheet 0, acSpreadsheetTypeExcel12, "Data", strFile, 1

For c = 2 To Last_Col_Num
        If Data.ColHeaders(1, c) = "Unit" Then 
            col_BN = c
        End If
        If Data.ColHeaders(1, c) = "TOE Title" Then 
            col_Co = c
        End If...………………………….
Next c

I am trying to convert:

Last_Col_Num = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column  'this gets the last column by number
Last_Col = Split(Cells(1, Last_Col_Num).Address, "$")(1)  'this gets the column letter
Last_Row = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row 'Finds last cell in Column B with data.

Into Access VBA

Dlast?

Its easy to do in a query with
Col 1: Data.Unit As BN
Col 2: Data.[TOE Title] as CO

[Gicu]

Dcount("*","[Data]") or CurrentDb.TableDefs("Data").RecordCount would give you the number of "rows" (=records) and CurrentDb.TableDefs("Data").Fields.Count would give you the column count (fields).

Cheers,
Vlad

[Thompyt]

Thanks Gicu,
I used the current......count worked like a charm. I figured I didn't need the String for the fieldnames and did it another way.

[Gicu]

You're welcome!