referencing file with date/time stamp

[cabinetguy]

I have a workbook in Excel 2007 that, based on a dropdown selection, goes out and finds the selected "table" file, opens it, copies a cell range, pastes it into the workbook and then closes the table file.

My question is; can I make it more specific? I don't want someone to edit the table files, so I thought that if I could also specify the date/time stamp of the file, that it would alleviate anyone tampering with them.

Any suggestions?

[Fotis1991]

Hi

One idea is, to lock the range that you need, using Validation:

Data>>Validation>>Custom>>>Per example= Today()>>>OK.

Or any other condition that you prefer.

Hope to helps you.

[cabinetguy]

Maybe this will help... Or possibly I'm just not understanding your response, but wither way, here goes...

The dropdown has file names, i.e., cabinet1, cabinet2, cabinet3, etc (x60). When the selection is made, it references a lookup of that file name on a separate sheet. From there, the VBA code takes that exact file name and then goes out to the specified directory where the files are housed and opens that specific file, copies the data, pastes it back into the main workbook and closes the the file (cabinet1,2,3,etc).

What I want to do, instead of just specifying the name of the file, I also want to specify the modified date of that file. Ex. cabinet1.xlsx date modified 10/2/2011 8:33 PM.

This way, if someone modifies the file, it will error out, since the modified date has changed, even though the name is still the same.

[Fotis1991]

the VBA code takes that exact file name and then goes out to the specified directory where the files are housed and opens that specific file, copies the data, pastes it back into the main workbook and closes the the file (cabinet1,2,3,etc).

Ι don't know(almost) anything of VBA and in any case nothing for your workbooks.

I propose you to attach a workbook and i think you will find your solution.

[cabinetguy]

Ok, below is the code that goes out and gets the data from the specified files. When it goes to the "Code" sheet and gets the name of the sheet, I would like it to also make sure the modified date is proper. Imagine if the name is mistyped, it will error out. I want that same effect if the modified date is different than what is selected.

For instance, if I select "Cabinettttt1.xlsx", but the actual file name is "Cabinet1.xlsx", it will error out. I want that same affect to happen if the file has been modified from the date that I specify. So if I program the file name to be "Cabinet1.xlsx 12/2/2011 8:33 pm" and the actual file has been modified and resaved as "Cabinet1.xlsx 1/1/2012 1:00 am", it will error out because the names do not match.

Please Login or Register  to view this content.

[cabinetguy]

I'm thinking that I can use FileDateTime function, but am yet to wrap my head around it to be able to figure it out yet...

[cabinetguy]

Anyone have any suggestions on how I can specify the file with the modified date? I just need the format. Ex. "Cabinet1.xlsx 12-13-2011 8:33 pm". Can I do this?

[cabinetguy]

Ok, I got it figured out, in case anyone wants to do do somehting similar later on.

In the list of files that is available that I wanted error checked, I put the appropriate last modified date and time. I then did a lookup, based on the file selected and in the VBA code, I referenced that value.

Below is the actual code:

Please Login or Register  to view this content.

lcSheetDate is the date that was specified by the lookup.

Thansk again to everyone that has helped me out throughout this project. I was an Excel n00b but with your support, I have created something pretty amazing.

Thanks again!