Forum Discussion
Append Excel data into MS Access though macro
Hi All,
I have a task to automate the process of appending the table in MS Access with the data in an Excel file.
I have created the Excel file, imported the data in Access.
But the task requires the data to be appended from Excel itself, rather than opening Access table/Database.
Could you please guide me how to achieve this.
Thanks and Regards
- SQL = "SELECT * INTO " & AccessTable & " FROM [Excel 12.0;Database=" & ExcelFile & "].["
& ExcelTable & "]"
Conn.Execute SQL- Thank You; Let me try this and update you.
Help us understand the scenario. Your organization requires you to append data into an Access table from Excel, from Excel? Why would they impose that rule? Are you not allowed to use Access in your job?
If this is critical to your job, you might want to ask in an Excel forum. I know there are ways to import data from Excel. Unfortunately, initiating the transfer from Excel seems not to be very common; I can't find any resources describing that.
- Hi George
This is a part of a project, in which data needs to be stored in Access; so that it can be retrieved in the future.
The scenario is, I have a macro that collates the data from multiple Excel files into one Excel file. This collated data has to be moved to Access.Okay, so there is no business rule involved, it's just the fact that the data is first collected into an Excel workbook.
In that case, there are multiple good ways to import data from Excel into Access; I am not aware of any easy way to export data from Excel into Access.
Therefore, I would recommend that, once you get the data collected in your workbooks, start Access and import the data from Access. That's a very common procedure and there are many resources available to describe methods for doing so.
