I want my excel spreadsheet to automatically detect when a new file is placed in a

I have an issue ive been trying to figure this out for some time now. Basically I want excell to take data from any new file i have in a specified folder. So when a file is added i want excell to automatically recognize it then paste it into my main workbook. It would be ideal for the data to be placed in the same area so that the formulas for the worksheet will still tie in.

I see a few different ways to do this:

Store the current update date and:
Look at the file modified date, or
Look at the file created date

or
Store a list of files with their modified dates and compare them to the current folder contents

You could then write a routine to execute the import function on workbook open, or you could have it on a timer (leaving the spreadsheet open), or both.

Post back indicating your preferences...

When you say paste it into your main workbook, can you give some details on that?

Hey wallyeye thanks a lot for your help. that doesnt sound hard at all the thing is the files are automatically loaded into the folder. Then i have to take the information loaded and tie it into excell. If you can be a little more specific that would be amazing. Could i make it so there is a button i push in excell which opens a choose file screen were i can choose the file data i want to upload.

Best
DT

---------- Post added at 04:27 PM ---------- Previous post was at 04:19 PM ----------

O yeah i wanted the data that i take from one file to be placed in another file automatically

You originally indicated you wanted the file to be brought in automatically. Are you now saying that you want to select a file from a list of files? If you want it to be automatic, then let me know how you want to detect a new file. To select a file, you would just use Application.GetOpenFilename with some parameters.

And for the data you want to transfer from the new file to the main workbook, I need some details:

Sheet name in new file
Range of data to import from new file
Sheet name in main workbook where you would like the new data placed
Range in the main workbook sheet to place the data

Sheet name: In main book is LiveReturns
Data ranges from A1:G10(Sometimes some of it will be blank but thats ok this would be a great max range)
Data ranges in main workbook: A200:G210
Name of sheet to pull data from: Allocation_Claughton_080812_#1.csv

which would be easier to set up either would be great i think a botton to manually load the data would be a good option to avoid future changes and problems.

,

THanks So much,
Dt

So, you could have a button on your worksheet tied to a macro. Go to the VBA IDE (alt-F11), insert a new module, and paste in this code:

Please Login or Register  to view this content.

Modify the btnImport_Click routine to point to your actual worksheet name and the destination range, the actual source range, and a default path for your .csv files. I've put in what you have on your post, except the default path. Because this is a .csv file, I'm assuming there is only one worksheet, so it will put the data from the first worksheet in the .csv file.

thats amazing im going to try it at work on monday if this works im very impressed good job on the macro build seriously thanks man ill let you know by monday

hey Wallyeye i actually got to trying the macro code you wrote it actually worked very well. I still have some kinks to resolve like how to get each sheet i open to list the data and not delete it and overwrite the cells but ill figure it out. Do you know if i can directly add a line to this macro to have that task done?

I'm not following you. You want to "list the data and not delete it"? Your original post indicated you wanted to put it in the same location so your formulas would continue to work. Are you wanting something slightly different now? Do you want to append the data to the bottom of your main workbook?

yeah as long as every time i load a file in it keeps the data and lists it. would that be possible. I tried to tweek the macro you wrote i just cant figure it out as easy as i thought it would be

Here is a sample piece of the data i have uploaded already i tried the macro you wrote it gave me a error on this line [intCol = rngDest.col]
This is the last step until it is done the file i send you is the testing file im using to make sure this works before i start the process of building my final version

I can't test this without building up a set of test files, so I just put the code in the right places:

Please Login or Register  to view this content.

Can you post up one of your data .csv files? Maybe in Excel format? I just don't have the energy to re-create your data to test this.

Im sorry sure i can youve been so helpful you dont even know. Im amazed at how good you are at this stuff.. Here is a sample CSV file for you to test Im going to test what you gave me so far as well to thanks again

Im sorry sure i can youve been so helpful you dont even know. Im amazed at how good you are at this stuff.. Here is a sample CSV file for you to test Im going to test what you gave me so far as well to thanks again

Alrighty, that did it. A couple more tweaks:

Please Login or Register  to view this content.

I found a couple of errors, first intcol=rngdest.column (instead of .col), and rngdest.offset(lnglastrow-rngdest.row +1) (adding the +1). It allows you to select a .csv file, then imports it into the first available rows in the Allocations sheet. You will want to tweak the last parameter on the _click procedure to point to the actual folder (Q:\something I think).

Hey wallyeye so close it lets me open the file but it flashes the data for a sec and then it stays blank i am looking at the code is there anything i am missing . the path were i want to search for the files is Q:\Daily Allocation. Did i do something wrong. im going to keep looking at it maybe i can figure it out. if you can get back to me whenever you have a chance

Hey wallyeye i got the macro to work for a second and uploaded data from three lines and it worked now it is back to making the screen flash a sec and not loading anything do you know what it could be?

You would have to step through the code, looking at the values in the variables. I suspect you have some malformed data. Set a breakpoint on the intCol = rngDest.Column line (select the line and press F9), then run the code and step through it by pressing F8. This will allow you to see exactly what it is doing at each step, both in the sheet and in the variables.

Wallyeye it actually worked great what happened was it tied in witha older macro i had wrote and basically there was some kind of interuption in the process. Thanks so much for everything you an excel G

Cool! Glad to be of assistance. You should mark the thread as solved...

@ dtaganovic16

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.