Running a routine in windows

I have over 100 heavy Excel files in a folder. For each file I need to:

-Open
-Enable Macro
-Save
-Close

Is there a way to automate this process?
fitalianoAsked:
Who is Participating?
 
fitalianoConnect With a Mentor Author Commented:
I found this solution on Microsoft...  It seems to work fine.  It opens one file at the time

Sub OpenMultipleFiles()

   Dim filenames As Variant
   Dim counter As Integer

   ' set the array to a variable and the True is for multi-select
   filenames = Application.GetOpenFilename(, , , , True)

      counter = 1

      ' ubound determines how many items in the array
      While counter <= UBound(filenames)

         'Opens the selected files
         Workbooks.Open filenames(counter)
         
         'Displays file name in a message box
         'MsgBox filenames(counter)

         'increment counter
         counter = counter + 1

         'Close the selected files
         ActiveWorkbook.Close (True)

      Wend
End Sub
0
 
__STCommented:
Try AutoIT.  http://www.autoitscript.com/site/autoit/

It's an easy scripting language that can handle GUIs and includes features for recording mouse movements / keystrokes.  You could record the process once and script it to open each file and change the options however you want.
0
 
TheGorbyCommented:
Did you mean to say "run" the macro? Because when you enable macros in Excel, that setting applies to all future documents opened, it doesn't need to be set for each individual file.

Otherwise I agree, AutoIT would be good for this.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
fitalianoAuthor Commented:
Do you have any idea how to write the script?

0
 
TheGorbyCommented:
It would look something like this:
#include <Excel.au3>

;change to the folder containing the excel files
Dim $folder="C:\Location\Of\Excel\Files"

FileChangeDir($folder)
$search=FileFindFirstFile("*.xls")
While 1
	$file=FileFindNextFile($search)
	If @error Then ExitLoop
	$excelobj=_ExcelBookOpen($file)
	$hnd=WinWait($file)
	WinActivate($hnd)
	
	;what keys to press to make the macro run, example is CTRL+F5
	Send("{CTRLDOWN}{F5}{CTRLUP}")
	
	;wait 2 seconds for macro to run
	Sleep(2000)

	_ExcelBookClose($excelobj)
WEnd

Open in new window

0
 
fitalianoAuthor Commented:
Thanks
0
 
fitalianoAuthor Commented:
Thanks
0
 
LeeTutorretiredCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.