COPY AND PASTE A FILE FROM MY DOCUMENTS ON FORM OPEN EVENT

[Shafique]

Hello Everyone
.
i am looking for a solution in my access database on form open event.
e.g
i receive a file from a network computer many times in a day in a shared folder (my documents) named ORDERS.xlsx
i linked this file with one of my database's table. Due to linking this file with table the sending user can't paste the new ORDERS.xlsx file until i close my database.
So i want an Event Procedure code to copy the file and paste it to another location on open a specific form and link it from there. i think this will not force me to close the database on arrival new ORDERS.

 

[welshgasman]

So just copy that data to another file, which is the actual file you are linked to, or directly into your table?
Use a query to do it.

This code/method is courtesy of CJ_London over on AWF.

SELECT XL.*
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\path\filename.XLSX'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])  AS XL;

Amend names to suit your data.

 

[Micron]

This shouldn't be an issue unless there is a lock on the linked table/sheet, and how often should these two events collide? If your db always has a form or query open that is based on the table, or the actual table is open, you can't open or over-write the workbook file. The lock would be the main issue?

Perhaps if you explain the process a better idea will come up.

 

[Shafique]

This shouldn't be an issue unless there is a lock on the linked table/sheet, and how often should these two events collide? If your db always has a form or query open that is based on the table, or the actual table is open, you can't open or over-write the workbook file. The lock would be the main issue?

Perhaps if you explain the process a better idea will come up.

In my db i set a main form (frmmain) that always be opened because that form shows me ORDERS list in a subform view. the subform data is linked with a pending.xlsx file located in C:\Users\Purchase\OneDrive\Documents\pending.xlsx.
this .xlsx file is overwrite many times a day due to new coming ORDERS. So due to frmmain opened all time the pendings.xlsx can't over-written that's why i want a code that i will set on frmmain open event to copy pending.xlsx file to another location and new location will be link with subform.

 

[Micron]

IMO, bad idea and I have no other suggestions except to say either only open the form when you want to see the data (& that's ok if that is when the db is opened, but close the form soon after) OR have a way to send a notice to someone to close the form temporarily OR to move the Excel process into your db. I doubt you will like the latter idea.

I think the main problem is that you don't realize that your idea requires that the source property to the new linked table/sheet has to be edited, or the whole linked table deleted and relinked AND the subform recordsource would have to be edited. To do that on any sort of permanent basis requires you to put the subform in design view one way or another - which would be dumb because that means the form is basically re-opened anyway. Too much code and bother when there is a simpler solution, which would be to reopen the form and have Access refresh the data automatically, but that's just my opinion I guess.

 

[Shafique]

IMO, bad idea and I have no other suggestions except to say either only open the form when you want to see the data (& that's ok if that is when the db is opened, but close the form soon after) OR have a way to send a notice to someone to close the form temporarily OR to move the Excel process into your db. I doubt you will like the latter idea.

I think the main problem is that you don't realize that your idea requires that the source property to the new linked table/sheet has to be edited, or the whole linked table deleted and relinked AND the subform recordsource would have to be edited. To do that on any sort of permanent basis requires you to put the subform in design view one way or another - which would be dumb because that means the form is basically re-opened anyway. Too much code and bother when there is a simpler solution, which would be to reopen the form and have Access refresh the data automatically, but that's just my opinion I guess.

oh.....
just a code to perform copy and paste........
anyway thanks to you to giving me your valuable Idea and priceless time. May Allah(S.W.T) want like that.
and if you don't mind take a look on another access post right under this.
once again thanks

 

[Shafique]

So just copy that data to another file, which is the actual file you are linked to, or directly into your table?
Use a query to do it.

This code/method is courtesy of CJ_London over on AWF.

SELECT XL.*
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\path\filename.XLSX'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])  AS XL;

Amend names to suit your data.

i am very sorry to say that.
the code that you sent to me is very hard to understand me. let me explain the question may it help to solve

In my db i set a main form (frmmain) that always be opened because that form shows me ORDERS list in a subform view. the subform data is linked with a pending.xlsx file located in C:\Users\Purchase\OneDrive\Documents\pending.xlsx.
this .xlsx file is overwrite many times a day due to new coming ORDERS. So due to frmmain opened all time the pendings.xlsx can't over-written that's why i want a code that i will set on frmmain open event to copy pending.xlsx file to another location.

 

[welshgasman]

i am very sorry to say that.
the code that you sent to me is very hard to understand me. let me explain the question may it help to solve

In my db i set a main form (frmmain) that always be opened because that form shows me ORDERS list in a subform view. the subform data is linked with a pending.xlsx file located in C:\Users\Purchase\OneDrive\Documents\pending.xlsx.
this .xlsx file is overwrite many times a day due to new coming ORDERS. So due to frmmain opened all time the pendings.xlsx can't over-written that's why i want a code that i will set on frmmain open event to copy pending.xlsx file to another location.

What is there that is hard to understand? You just change the names to suit. That code allows to to get the data from the workbook without PERMANENTLY locking the file, just when it is in use by that code.
My thoughts were you extract the data from orders.xlsx into your linked orders excel sheet, or even directly into the table.
Once you have retrieved the data, the file is no longer locked, anyone can overwrite it with new data.?

Rinse and repeat.