How to use macros copy data from different file name

EileenJohn

Board Regular
Joined
Nov 23, 2016
Messages
53
Hi, i need help with this, i have a system tht will download the data per day.
Eg: I have .csv file:
2dec2016
3dec2016
4dec2016
....
.....
Then i want to copy them to record.xlsx. i want to use macros to copy the data per day. But only copy and paste the latest download data. Is it possible?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
will need more information
will the file record.xlsx have the daily csv files pasted into one sheet or a separate sheet for each day?
will the daily csv files rename static or is data being added to them periodically?
 

EileenJohn

Board Regular
Joined
Nov 23, 2016
Messages
53
will need more information
will the file record.xlsx have the daily csv files pasted into one sheet or a separate sheet for each day?
will the daily csv files rename static or is data being added to them periodically?
It is an attendance tracker system. So the csv data will be updated based on per day. Different day different file name. Record.xlsx has all data from separate sheet for each day.
 

EileenJohn

Board Regular
Joined
Nov 23, 2016
Messages
53
will need more information
will the file record.xlsx have the daily csv files pasted into one sheet or a separate sheet for each day?
will the daily csv files rename static or is data being added to them periodically?


Here is my code, but it copy all the files in that folder.


Sub GetSheets()
Path=""
filename= Dir(Path & "&.csv")
Do While Filename <> " "
Workbooks.Open Filename :=Path & Filename, ReadOnly :=True
For Each Sheet In ActiveWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub


I'm not a programmer so i'm not sure
1. at which part i can add timer? make it auto update ..
2. then copy only workbook with latest date
 

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
Okay. I can see you want this sub to run in the background. The problem is that you can't use Excel while the sub is running because it is modal. You need a real programming language to accomplish your goal. You could write a program in VB.NET that watches a folder for file changes and calls the excel macro every time a new file is added. It's a bigger project but totally doable.

If this is a "daily" file you should just run it once a day.

To track what one you are on you could make it write a list of files processed to a "tracking" sheet. Then it could compare the filename in the loop to the filenames on the tracking sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,368
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top