Exporting a dynamic range of rows from Excel to Access

[Anticor]

All,

As the title suggests I'm looking to export rows (the total of which can vary) of data from Excel to an Access database table. While, through trial and error and lots of google-fu, I've been able to figure out how to send a specific row of data I'm not quite sure how to get over the next hurdle.

Scenario:
A user enters rows of data starting with cell A8 and the record spanning to M8, the next record being A9..M9, and so on. The total number of records/rows can vary depending and there are additional populated fields outside of the A-M columns that I do not wish to capture. Additionally with each row/record I want to append the value from A2, the reason for this is in A2 I've written the user name (Range("A2").Value = Environ("UserName") ). This allows me to see which specific user is associated to the record being written to my Access DB...and maybe that's not needed maybe I can capture that on the Access side of this process, not something I've explored at this point.


To grab the A8-M8 row/record I perform the following:

Sub SendRecord()
'Writes a new record in the myTable in my Access DB


Dim cn As Object
Dim strQuery As String
Dim EN As String
Dim DW As Date
Dim ST As String
Dim ET As String
Dim CS As String
Dim CL As String
Dim IN As String
Dim IS As String
Dim RS As String
Dim RL As String
Dim OE As String
Dim ME As String
Dim WT As String
Dim TH As Double
Dim myDB As String


'Initialize Variables
EN = ActiveSheet.Range("A2").Value
DW = ActiveSheet.Range("A8").Value
ST = ActiveSheet.Range("B8").Value
ET = ActiveSheet.Range("C8").Value
CS = ActiveSheet.Range("D8").Value
CL = ActiveSheet.Range("E8").Value
IN = ActiveSheet.Range("F8").Value
IS = ActiveSheet.Range("G8").Value
RS = ActiveSheet.Range("H8").Value
RL = ActiveSheet.Range("I8").Value
OE = ActiveSheet.Range("J8").Value
ME = ActiveSheet.Range("K8").Value
WT = ActiveSheet.Range("L8").Value
TH = ActiveSheet.Range("M8").Value



'Database location
myDB = "file path to my DB"


'Connect to my database
Set cn = CreateObject("ADODB.Connection")


With cn
.Provider = "Microsoft.ACE.OLEDB.12.0" 'For *.ACCDB Databases
.ConnectionString = myDB
.Open
End With

'Create a new record in my table
strQuery = "INSERT INTO myTable ([myEN], [myDW], [myST], [myET], [myCS], [myCL], [myIN], [myIS], [myRS], [myRL], [myOE], [myME], [myWT], [myTH])" & _
"VALUES ('" & EN & "', " & DW & ", " & ST & ", " & ET & ", '" & CS & "', '" & CL & "', '" & IN & "', '" & IS & "', '" & RS & "', '" & RL & "', '" & OE & "', '" & ME & "', '" & WT & "', " & TH & " ); "


cn.Execute strQuery
cn.Close
Set cn = Nothing



End Sub

Using the above I've proven to myself that the Excel spreadsheet will capture the expected first row/record that I want (A8-M8) and it does a great job of writing that to my target DB/table. The next step is where I need some guidance. How do I write every A-M row containing, at a minimum data in A (my table in Access allows for blanks in the other fields).

I'm guessing I'm going to first identify the last row and then from there determine what to send...and I've done that in the past using:

Dim LastRow As Long, ws As Worksheet


'Work in the current worksheet
Set ws = ActiveWorkbook.ActiveSheet

'Finds the last blank row in the summary worksheet, sets the variable LastRow with that value.
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1


But from there I'm not sure how to tackle the remainder (grabbing all A-M rows where A contains data and sending them as records, with A2 value appended, to my target DB/table).

Hopefully I'm not rambling too terribly much and I've provided at least some insight as to what I'm hoping to accomplish so that someone can give me a next step or a breadcrumb to pursue. From there I'm sure I'll have additional asks but those will be for new threads as I learn more about Excel/Access/VBA and I eat this elephant one bite at a time. Thanks in advance.

 

[Anticor]

Got it!

Sub SendRecord()
'Writes a new record in the OnCall Access DB


    Dim cn As Object
    Dim strQuery As String
    Dim myDB As String
    Dim i As Long
 
     'Database location
    myDB = "file path to my DB"


    Set cn = CreateObject("ADODB.Connection")


    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"    'For *.ACCDB Databases
        .ConnectionString = myDB
        .Open
    End With
    


'Define my first row where I expect to find data
i = 8


Do While (Cells(i, 1) <> "")


  
    strQuery = "INSERT INTO myTable ([myEN], [myDW], [myST], [myET], [myCS], [myCL], [myIN], [myIS], [myRS], [myRL], [myOE], [myME], [myWT], [myTH])" & _
               "VALUES ('" & Cells(2, 1) & "', #" & Cells(i, 1) & "#, " & Cells(i, 2) & ", " & Cells(i, 3) & ", '" & Cells(i, 4) & "', '" & Cells(i, 5) & "', '" & Cells(i, 6) & "', '" & Cells(i, 7) & "', '" & Cells(i, 8) & "', '" & Cells(i, 9) & "', '" & Cells(i, 10) & "', '" & Cells(i, 11) & "', '" & Cells(i, 12) & "', " & Cells(i, 13) & "); "


    cn.Execute strQuery
    
    i = i + 1
Loop
    
    cn.Close
    Set cn = Nothing


End Sub