Exporting a dynamic range of rows from Excel to Access

Anticor

New Member
Joined
Aug 15, 2017
Messages
8
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.

 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Got it!

Rich (BB code):
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top