VB Code to export data from excel to Access

Hello:

Please refer to attached file.
I need a VB Code to export certain data from excel to MS Access.
I am using below code after doing google search and trying to implement however i am getting error at certain point.

Please Login or Register  to view this content.

Below is my requirement

Sheet from where data is to be extracted to be exported : EmpHours
Data Range A5 to End of month (in this case look for last day of month 11/30/2015)


Name of Access File Test1.mdb
Location C:\Temp\ ' This will be the same path as the excel file.
Table Name : EmpHour2


Let me know if you have any questions.
Thanks.

Hi AllKey:

The problem is that i have lots of workbook and in each workbook there are lots of data in different range which needs to be sent to Access for further evaluation.
I thought exporting the data would be the best solution as the macro will define the certain data range and will define which Sheets to be used etc.
The above code seems to error out at J=3 for some reason.
If I can pass that error than hopefully it should work somewhat.
Please let me know if you have any questions.
Thanks.

Riz

Hi alan:

Thanks a lot for your reply.
Please help me with the below.
I have attached excel sheet.
Here i have number of sheets.
To start off with i need to Import data into Access table tblEmpHour as follows.
First i need to import data from "EmpInOut" Sheet from Cell A120 down until column D with non-error data. In this case it is upto row 340.
When i import next time, i would need to append to the same table with the same requirement, Cell A120 down until column D with non-error data.

Please refer to attached file.
Let me know if you have any questions.
Thanks.

Riz

Hi Riz,

I import excel data all the time into Access. There are two primary ways I do it. The first is using the inbuilt import function in vba:

Please Login or Register  to view this content.

The Access table MUST contain the exact same columns as your excel sheet. It's a lot faster than any other method, but there are some severe limitations.

Although it might be a bit slower, I almost always use an Excel to Array, Array to Access function. Where you might have multiple sheets, you can simply use loop for each sheet. Here, I get the filename from a dialogue them pass it to this function.

Please Login or Register  to view this content.

The Array contains the column headings. From here, you can simply use those headings to define the Access fields you want populated. Then loop the data into your table. You can also add other information into the table at the same time if you like. I almost always use an array because I want a great deal more control over the information that ends up in the table. For example, I'll use a loop like this to build information into related tables, such as ID's.

I use the name of the sheet from which the data came to determine which table the data should go to: e.g

Please Login or Register  to view this content.

And loop in the array:

Please Login or Register  to view this content.

You should be able to figure out how to loop your data from here. It does require that you modify your code to your application, but this will give you an idea about how you can do it.

Hope this helps