Can you please help. I need to import/Link excel sheet(specific range)in ms access us

[Exlq]

Can you please help. I need to import/Link excel sheet(specific range)in ms access. The range varies everytime we get the new excel sheet due to addition and deletion of records so want to make the range dynamic and import the rows contain data only.

[twinnyfo]

What have you tried so far?

With constantly changing ranges in Excel, this sounds like a strange thing to do, as how will you tell the DB what the values represent?

I think your first step is to standardize the spreadsheet format first--this will make your import much easier.

[Exlq]

Thanks for the response. I have tried to name the range in excel and while creating linked table using "DoCmd.TransferSpreadsheet" selected the same range in "Show Named Ranges" but when my excel spreadsheet changes, the rows get changed (columns remain unchanged) and it either add blank rows in linked table or do not pick the rows that exceed the set range. Please help

[zmbd]

Are you using named ranges in the worksheet?

For example:

Sub zimportrangename()
DoCmd.TransferSpreadsheet _
    transfertype:=acImport, _
    spreadsheettype:=acSpreadsheetTypeExcel12, _
    tablename:="TestInput", _
    FileName:="C:\Users\myusername\Documents\book2.xlsm", _
    HasFieldNames:=False, _
    Range:="importme"
End Sub

Now no matter where the data is, one can simply name the range "importme" (or what have you) and the date will transfer to the table...
NOTE: you will need to validate the field values etc... which is the downfall. I usually import into a testing table that has some generic text fields and then parse the imported data for numerics etc...

Your other option is application automation wherein you could open an instance of the workbook and then search for your data... sortof kludgy though... like a 20#Maul for a carpet tack.