Upload data from Excel via a button into Access DB

[ketangarg86]

Hi, I have a excel file and want a button in the sheet which would transfer a certain range of data in a defined excel sheet to an existing access db table. How to do about doing that.

Please help. I am new to this.

 

[apr pillai]

The easiest way to bring Excel data into Access (2007 or above) is to copy and paste into the Access Tables' Navigation Panel..

1. Keep Excel and MS-Access in Open state.
2. Highlight the Excel Range (including header labels, if exists).
3. Right-click the highlighted Range and select Copy.
4. Select Access window and select Tables' Navigation Panel.
5. Right-Click on the Tables Navigation Panel and Select Paste. A dialog box will appear and select Yes or No, based on the select with/without the header labels.
6. The Data will be created as a Table with the Name of your Excel Sheet name such Sheet1 etc.

 

[ketangarg86]

Thanks but I need a button in an excel, which is linked to a macro, that does the same thing.. Could you please help me with the macro..

 

[Frothingslosh]

http://msdn.microsoft.com/en-us/library/bb177401(v=office.12).aspx

You can also use the TransferSpreadsheet function instead if you'd rather use VBA (which generally gives more control than macros). If your import is more complicated, then there are a couple threads discussing VBA solutions in the Code Repository, as well.

 

[ketangarg86]

Thanks. I looked at it. I think the Transfer Spreadsheet is a macro in Access. I need a macro in Excel so that the user after updating the info in the excel sheet can click a button which would automatically transfer the data to an access table via a macro. The user is not given access to access database.

 

[Frothingslosh]

That would be far easier to do from Access.

I did find a discussion HERE that would give you a starting point for doing it from Excel, but you'll have to modify the code, since as-written it creates a new database.