I can find all sorts of ways to do this in excel, but not in Access. Getting the Excel file inported is easy:
Code:
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:
Code:
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