Loop through recordset and find+copy a file for each record

[harmankardon]

Hi all,

I've been tasked with taking a table of part numbers and finding the autocad drawing for each part number in a network folder and copying it to another location.

Right now the table is a local Access (2010) table with a simple table structure of just 1 column called "Part_Num".

Each part number has an autocad drawing in a network folder. The file names are the part numbers (part numbers are unique strings).

I'd like to loop through the table (or a subset of the table using a query/recordset) and for each part number, search the network folder, find it's autocad drawing and then copy it to another folder.

I have done some reading on the basics of looping through a recordset but I'm not really sure how to accomplish the finding and copying of the file.

Can anyone point me in the right direction?

 

[vbaInet]

Ok, it sounds like you know some VBA and are confident enough to get stuck in so here are some useful links.

File and Folder functions in VBA:
http://www.exceltip.com/st/Basic_file_and_folder_examples_using_VBA_in_Microsoft_Excel/443.html
http://www.afralisp.net/visual-basic-for-applications/tutorials/file-functions.php

Finding a file using Dir():
http://www.techonthenet.com/excel/formulas/dir.php

Recordsets:
http://allenbrowne.com/ser-29.html

Worth looking at:
http://allenbrowne.com/ser-59.html

 

[harmankardon]

Thank you sir! I'll have a look at this and post back when I come up with a solution.

 

[vbaInet]

Oki doki. Let us know how it goes.

 

[harmankardon]

Just thought I'd follow up on this post, I ended up cheating a bit and used excel spreadsheet to give me a semi-colon delimited string of part numbers that I just threw into regular ol' Windows XP Search. Not the most elegant solution but after I discovered that you can search for multiple files at one time with XP Search, I decided to do that. Took about 2 minutes to do vs a few hours of programming/researching.

I feel guilty and ashamed but my boss is happy

 

[vbaInet]

Took about 2 minutes to do vs a few hours of programming/researching.

You could have learnt something from the process though.

I feel guilty and ashamed but my boss is happy

It's imperative to keep the boss happy.

Thanks for following up the thread.