Process all Excel files in a folder: open, modify, save

[Gintaras]

Macro Macro2433

Code:

Copy      Help

;change these values
str folder1="$documents$\test" ;;the files are in this folder
str files="*.xls" ;;filename wildcard. Change to "*.xlsx" if need.
str folder2="$documents$\test2" ;;this macro will save processed files in this folder, not replacing the original files in folder1
;_______________________

mkdir folder2 ;;create the destination folder if does not exist

ARRAY(str) a
GetFilesInFolder a folder1 files ;;get full paths of Excel files in folder1
;out a

ExcelSheet e
int i hwnd
e.Init("" 16); err e.Init("" 8|16); hwnd=e.ExcelHwnd ;;activate or run Excel

for i 0 a.len ;;for each file
,e.Init("" 4 a[i]) ;;open file in this Excel window
,
,sel(mes(F"Process this file?[][]{a[i]}" "" "YNC")) case 'N' continue; case 'C' break ;;show message box. Later remove this line.
,
,str file2=F"{folder2}\{_s.getfilename(a[i] 1)}" ;;we'll save to this file in folder2
,if(!(file2~a[i]) and FileExists(file2)) del- file2 ;;delete if exists, or Excel will ask
,sub.ProcessDocument e a[i] ;;call the below sub-function that does something with the open document
,e.Save(file2) ;;save the document in folder2
,e.Close(2) ;;close the document

if(hwnd) clo hwnd ;;close Excel if was not running

;;you may want to delete folder1 and rename folder2 to folder1. Enable the following 2 lines.
;del folder1 ;;move folder1 to Recycle Bin
;ren folder2 folder1 ;;rename


#sub ProcessDocument
function ExcelSheet&e str&sFile ;;parameters; you can use them or not

;put here your code that does something with the open document
;example:
str s=e.Cell("A1")
e.SetCell(s "H1")