VBACode-import sreadsheet, user prompt for filename, specified range, add primary key

[AlGreko]

Hi

I'm really new to using the modules and VBA in access so would appreciate some really step by step guidance.

I have used a TransferSpreadsheet macro to import data from spreadsheet. However it does not go as far as I would like. The database will be used by others, and I would like Access to prompt them for a file path for the spreadsheet to be imported.

The spreadsheet imported will always be in the same format (as it is being downloaded from an online source). So I would like to specify the range, which starts at A2, however the length is variable and ends at AF##.

Finally, I'd like the imported table to inlcude a primary key.

Would be so grateful for some help on this

 

[nathansav]

Something like this, got some isses at the moment so cant test

Public Sub b()
Dim a As TableDef
Dim f As Field
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Sheet1", "c:\c", True, "A1:C100"
Set a = CurrentDb.TableDefs("tblFile")
Set f = a.CreateField("n", dbLong)
f.Attributes = f.Attributes Or dbAutoIncrField
a.Fields.Append f
a.Fields.Refresh
End Sub