?
Solved

Running a routine in windows

Posted on 2011-05-11
9
Medium Priority
?
273 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:fitaliano
8 Comments
 
LVL 2

Expert Comment

by:__ST
ID: 35743324
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
 
LVL 11

Expert Comment

by:TheGorby
ID: 35745882
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
 

Author Comment

by:fitaliano
ID: 35748899
Do you have any idea how to write the script?

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:TheGorby
ID: 35749066
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
 

Accepted Solution

by:
fitaliano earned 0 total points
ID: 35749276
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
 

Author Comment

by:fitaliano
ID: 35901810
Thanks
0
 

Author Comment

by:fitaliano
ID: 35901811
Thanks
0
 
LVL 59

Expert Comment

by:LeeTutor
ID: 35929275
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this modest contribution, I want to share with the IT community (especially system administrators, IT Support Engineers and IT Help Desks) about Windows crashes/hangs and how to deal with these particular problems.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question