(Solved) – Import Excel in Access with custom dynamic range – Range declaration issue

  • by
(solved)-–-import-excel-in-access-with-custom-dynamic-range-–-range-declaration-issue

I am struggling with TransferSpreadsheet and dynamic range on Excel VBA. I have done numerous researches, but I don’t seem to find a solution to my problem.

Anyways, I am a beginner VBA programmer; I want to create a dynamic rage 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 collum 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.

Thanks in advance for all the help. Here is the code that 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:RRTestingRESOP_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

Leave a Reply

Your email address will not be published. Required fields are marked *