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