Auto Excel Import

[Pete490h]

Hi,
A customer of ours has asked that we look at importing data from their excel spreadsheets into our access database system
They issue unique individual spreadsheets which we store in a master directory in Excel

CUSTOMER XLSX
716.xlsx
719.xlsx
725.xlsx
740.xlsx
etc etc

In our system, we store their numbers 716, 719, 725, 740 in separate works orders with a field called Cirn and wonder if there’s a way to use that link from access to pull their data into a table via a form using a changeable path

The trial button on my form has an OnClick event procedure that currently reads

'IMPORTING SHEET
DoCmd.TransferSpreadsheet acImport, , "FeatureData2", "C:\Users\Peter1\Desktop\719.xlsx", False

Is it possible to code it so it uses the relevant form field

Hope I’ve explained it enough

Many thanks
Peter

 

[Gasman]

Yes, use concatenation.
Try

DoCmd.TransferSpreadsheet acImport, , "FeatureData2", "C:\Users\Peter1\Desktop\" & Me.txtCirn & ".xlsx", False

 

[Pete490h]

Yes, use concatenation.
Try

DoCmd.TransferSpreadsheet acImport, , "FeatureData2", "C:\Users\Peter1\Desktop\" & Me.txtCirn & ".xlsx", False

Thanks Gasman, I’ll give that a go later when back home