loop files in a folder

[itchymonkey]

hi,

 

i'm very new to macro express.

 

would like to ask

 

If i have 10 files in a folder.

 

one.xls

two.xls

three.xls..... so on

 

I would like to find a way to repeat the process (using excel to open):- to open the 1st file and type some text, save, continue with the 2nd file. (i wan the mouse to know which file i have copied and continue the next one till all files have been done.)

 

 

Thanks in advance for any help.

[stevecasper]

hi,

 

i'm very new to macro express.

 

would like to ask

 

If i have 10 files in a folder.

 

one.xls

two.xls

three.xls..... so on

 

I would like to find a way to repeat the process (using excel to open):- to open the 1st file and type some text, save, continue with the 2nd file. (i wan the mouse to know which file i have copied and continue the next one till all files have been done.)

 

 

Thanks in advance for any help.

Opening an .xls and typing into it is a pain. There is a much easier way to do what you are trying to do, through the Text File Process and/or ASCII File Process commands.

 

I recommend reading the Help files for these items. Also, if you haven't already done so, download the Samples.mex file from the Macro Express website (it should actually have been included automatically when you downloaded Macro Express), and run Process text file as well as Process a CSV delimited file to see how these macros work. I'd also suggest picking those macros apart (as well as any of the other sample macros) in order to get a really good idea of how Macro Express macros work, and how to build your own.

 

If you run into any trouble, which can happen easily enough when you're first starting out, we're happy to help in this forum. We just need more details about exactly what it is you are trying to do. If you can give examples, and even post some of the code you've already written so we know where you are in the process, it can really help us to help you.

 

You might also want to check out other posts in this forum that may be able to help you with this task. Recently I helped somebody else do something very similar to what it looks like you're trying to do. Check out post Need help creating macro for google search funtion.

[itchymonkey]

hi thanks for the response.

 

actually what i want is to know how to write a macro that can loop through the files in a directory.

 

"open file window" to get file name and continue with other macro steps.

 

(the part which mouse cursor move up and down in open file window and stop macro when everything finished.)

 

--------------------------------------

I've tried 'repeat folder' codes.

It will loop through all files but its more like a a screenie of all files.

 

I would like to run one file each time.

[stevecasper]

hi thanks for the response.

 

actually what i want is to know how to write a macro that can loop through the files in a directory.

 

"open file window" to get file name and continue with other macro steps.

 

(the part which mouse cursor move up and down in open file window and stop macro when everything finished.)

 

--------------------------------------

I've tried 'repeat folder' codes.

It will loop through all files but its more like a a screenie of all files.

 

I would like to run one file each time.

If I understand you correctly, you just want the names of the files. You don't actually want to open the files. In your original post you said you want to use Excel to open the files, but now it looks like you only want the file names. Forgive me if I'm still mistaken. It would really help to have more information about what it is exactly that you are trying to do.

 

If my understanding is right, then this is quite a bit easier, and can be done using Text Type commands to use the file short-cuts.

 

Example: For many applications Alt+F will open the File drop-down menu. Ctrl+s will save an open file. I think you'll find that the combination Alt+f+m will "Rename" a file in an explorer window.

 

So, if I have understood you, you've got your directory window open and a list of files. You want the macro to go through the files and read the names of each file - and perhaps rename the files.

 

If that is the case, then you can do something simple like this:

 

1. Open the directory - through Windows Explorer, for example - and navigate to the folder which contains the files you want to read/rename.

2. Run a macro that performs the following functions:

• Text Type: <ALT>fm - this will open up the selected item and highlight its name.
  
• Clipboard Copy - Now the name of that item is on the clipboard.
  
• Set Text Variable from Clipboard - this will put the name of the item into the T variable of your choosing (i.e. %T1%)
  
• Now you may use Macro Express manipulate the T variable, using commands such as "Modify Text Variable" to append text to the variable (among other things you could do).
  
• Once you've finished modifying the selected variable, you can use the Text Type command to type %T1% (keeping with the example). This will cause the macro to type over the name of the selected file, thereby renaming it.
  
• Next the macro will need to perform a Text Type <ENTER> command in order to complete the change (essentially saving the new file name)
  
• Finally, the macro can use a Text Type <ARROW DOWN> command to move on to the next item.
  

You could incorporate all of these commands into a Repeat Loop so that you only have to run the macro once and it will process through all of the files in the selected directory. Of course you'll either need to count the number of items before-hand and set the repeat loop to only process that number of files, or build some additional logic into the macro so that it can detect automatically when it has reached the end.

 

I've used macros like the one I have described above to rename photographs after moving them to my PC from my camera, since the camera gives them ridiculous names like 9003596626843.jpg which means nothing to me. Or music tracks from CDs that have complex track-names like: Evanescence_Open Door_Track 1_All That I'm Living For.mp3

 

I have a macro that strips all the underscores (_) and replaces them with spaces. Get's rid of the band name and the album name, removes "Track", and leaves me with: "1 All That I'm Living For.mp3"

 

Again, this may still not be what you are trying to do. If not, please provide a clear picture of what it is you are attempting, so that we can help more effectively.

[stan]

If I understand correctly, here is one way to do what you need.

 

Repeat with Folder

Program Launch: "EXCEL.EXE"

Wait For Window Title: "Excel"

***macro steps

Window Close: "Excel"

Repeat End

 

 

Use the Repeat with Folder command to obtain the first file in the folder. Use the entire file path and save to a variable such as T1.

Use the Program Launch command to launch Excel. Enter %T1% in the Program Parameters field to have the first file open when Excel loads. You may need to insert as "%T1%".

Have the macro wait until the Excel window opens.

Enter your text, etc. and save the changes - Text Type: <ALT>fs should save the file.

Close Excel and finish with a Repeat End to loop back to the next file.