Running commands in and reading from the command line - possible?

So let's start with a giant pile of files that need to be renamed that have unpredictable filenames and file extensions.

Let's say I can pull a report in excel format out of the system that generated those files. It will tell me the (unique) first five characters of the unpredictable filename and includes some other data that I'm putting in the filename to make it human-readable.

I can, and already do, use a series of vlookups and concatenate functions to generate command line "ren" commands to get sensible filenames. (e.g. ="ren """&A1&"."&B1&""" """&A1&" - "&C1&" - "&D1&"."&B1&"""" yields a value of ren "asdfg.doc" "asdfg - file #10 - the quick brown fox.doc") which I then copy back into the command line. But I'd like to automate it further if possible.

Problem #1: Can I open a command line at the location of the active workbook, cd into a subdirectory, run a dir /b, and feed the results back into column A of the active workbook?
Problem #2: Can I automatically send the previously discussed ren commands back to the command line?

Thanks in advance

Hi Shanyn,

See the file associated with post #2 in the following thread. It creates a list of file using the dir /b command and can retrieve the results in a separate Excel Sheet. http://www.excelforum.com/excel-prog...h-results.html

Lewis

Thanks very mcuh to both of you. These look like they will solve my problems brilliantly.

Hi Shanyn,

See the following workbook which contains the code that follows this text. It simplifies the code in the link I provided above so it should be easier for you to follow the code. See Sheet 'Annotated' and the code module 'modAnnotated'.

Note that some Utility routines are not listed below, but are in the attached file.

Lewis

Please Login or Register  to view this content.