CODEWITHSUNDEEP
excelvbams-access
Raphael Rodrigues
Raphael Rodrigues

Reputation: 23

How to declare a dynamic range when exporting Excel data to Access?

I want to create a dynamic range selection to export to an Access database.

I know how to get my range, and I know how to export to Access with a fixed range such as Range:="Sheet2$A1:B10".

My goal is to transfer the content starting on A3 until the last value of column E, then to transfer it to Access. The second part of this is skipping duplicate values on my table if applicable (the criteria will be the dates, e.g. 2019-11-01 01:00). And yes, it has to be done in Excel.

I have done numerous researches, but I didn't find a solution.

The code I have so far.

Sub Transfer()

Dim sht As Worksheet
Dim lastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim test As Excel.Range
Dim rUsed As Excel.Range
Set sht = Worksheets("Sheet2")
Set StartCell = Range("A3")

'Find Last Row and Column
lastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

'Method 1 to get my dynamic range
Set test = sht.Range(StartCell, sht.Cells(lastRow, LastColumn))

' Method 2 to get my dynamic range, just for testing
Set rUsed = Intersect(Range("A:AE"), Range(StartCell, sht.Cells(lastRow, LastColumn)))

sht.Range("A1:E" & lastRow & "").Name = "xlRange1"

Dim acc As New Access.Application
acc.OpenCurrentDatabase "D:\RR\Testing\RESOP_DB.accdb"
acc.DoCmd.TransferSpreadsheet _
  TransferType:=acImport, _
  SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
  TableName:="Test_Asite", _
  Filename:=Application.ActiveWorkbook.FullName, _
  HasFieldNames:=True, _
  Range:="xlRange1"  '** here is where I donn't undertand, as far as I know I am passing a range, no?**
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing

End Sub

I am a beginner VBA programmer.

Upvotes: 0

Views: 275

Answers (2)

Raphael Rodrigues
Raphael Rodrigues

Reputation: 23

A lack of defined range caused the error that was showing it was 2391, which I believe it. I was trying different methods to find my range, that is why I wasn't using the last column anymore.

June7 WOW brother!! Your suggestion was spot-on. That is the working code now

I am doing more research to solve the second problem. I guess instead of avoiding the duplicate values (a column will define that with the date and other with a name), I am trying to remove them and insert the new values again, for example.

enter image description here

If I was transferring the data from this table, and the date 2019-11-02 12:00:00 AM and House A were existing on my table, I would remove it first and transfer the new value.

Thanks very much, guys,

Sub transfer_data()


Dim sht As Worksheet
Dim lastRow As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet2")
Set StartCell = Range("A2")

'Find Last Row and Column
    lastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row

'define range
    sht.Range("A2:E" & lastRow & "").Name = "xlRange1"

'transfer the range
    Dim acc As New Access.Application
    acc.OpenCurrentDatabase "D:\RR\Testing\RESOP_DB.accdb"
    acc.DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="Test_Asite", _
        Filename:=Application.ActiveWorkbook.FullName, _
        HasFieldNames:=True, _
        Range:="xlRange1"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing

MsgBox "Conclusion", vbInformation, "Transfer Complete"

End Sub

Upvotes: 0

June7
June7

Reputation: 21370

If you want to export from range A3 and third row includes field headers, then change "A1" to "A3" in the following line:
sht.Range("A1:E" & lastRow & "").Name = "xlRange1"

If you want to use StartCell and LastColumn variables:
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Name = "xlRange1"

These alternatives do not use range name.
Range:="Sheet2!A3:E" & LastRow
Range:="Sheet2!" & StartCell & ":" & "E" & LastRow
Range:="Sheet2!" & Replace(StartCell.Address & ":" & sht.Cells(LastRow, LastColumn).Address, "$", "")

Upvotes: 1

Related Questions