Import Specific cells from Excel based on selected file.

[Only4Access]

Hello Programmers,



I am a newbie in Access. I am using Access 2007 version.

I have 3 tables. Sheet3Table, Sheet4Table, Sheet5Table

I have a excel file (xlsx) which use a separate file for everyday. This xlsx has 5 sheets. Sheet1 to Sheet5. I want import records from sheet3,4,5 to appropriate tables in Access, but based on the selected date.

Sheet3 7 Sheet4 has 10 columns. A:J
Sheet5 has 12 columns A:L

I will try to explain how to import.

I have a field in a form which is a text data format as date. So whenever I select a field will have a date picker. So I can select a date here.

When I click on Import button, need to ask for a file (exactly the same way like import external data in Access) So users can browse the file, then in that file look in Sheet3, Sheet4. If there is/are any records for the date selected in a date field, need to copy those records to appropriate tables. Column h is the date entered in xlsx file.

Copy range for Sheet3 & Sheet4

where is the selected date located in Sheet3 column H:Total nof records found for this selected date
A:J

Copy range for Sheet5,

where is the selected date located in Sheet5 column J:Total nof records found for this selected date
A:L

Import those records to Sheet3Table. same for Sheet4Table.

Eg: I select a date 2/21/2012

When I click Import ask for a file to browse, If the date is located in Sheet3!H20:H41, so copy A20:J41 to Sheet3Table,

In Sheet4 If the selected date is located in Sheet4!H50:H51, so copy A50:J51 to sheet4Table

In Sheet5, if the selected date is in Sheet5!J20:J60, so copy A20:L60 to Sheet5Table.

If the records is zero in a sheet msgbox No records found for the selected date.

I am sorry for the long question.

I really appreciate your help.

Thank you all for your time

Raj

[June7]

Can't open workbook and let user browse and select cells. Code can check content of specified cells and based on what is found, perform required action.

Is it always the same spreadsheet by name in same folder location? Is it always the same cell references?

[Only4Access]

Thank you for your reply June7,

Can't open workbook and let user browse and select cells...

I like to avoid this If possible, because of the basic users are dealing with this file. I wonder they don't like it this way, also may not select all the cells, if cells are not sorted.

Is it always the same spreadsheet by name in same folder location?

Every month is creating a new folder for that month. Today will enter all the data happened in a spreadsheet & will store it in a folder (today's month & year) Tomorrow will start to enter under the yesterdays data will store that file to the folder, day after tomorrow will enter the data down... etc..Every day will have a new file. can't keep one file for whole records for some other reasons.

Is it always the same cell references

No. i just used that is an example. Code needs to check where date located on each sheet & copy those rows.

Thank you for your time.

[June7]

That will be very complicated coding. You will have to learn the VBA to accomplish or go find a programmer to pay.

Common topic, search forum or Google.

Why not data entry into Access?

[Only4Access]

Thank you again June7. Is this possible this way.

Open the xlsx file, then in access form enter a date in the field. Code look for this date entries in Sheet3 (Col_H), Sheet4 (Col_H), Sheet5 (Col_J) & import those records to appropriate tables Sheet3Table, Sheet4Table, Sheet5Table.

Appreciate your assistance.

[June7]

Yes, certainly possible. The necessary code will be complex. There are many examples of Access/Excel interaction but nothing that is ready-to-use for your situation. Just to give you some idea of what can be involved, here is a simple one that shows exporting from Access to Excel http://forums.aspfree.com/microsoft-...el-413629.html