Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Process all Excel files in a folder: open, modify, save
#1
Quote:In one folder, I have 500 EXCEL files.

All the EXCEL files have the same structure (same fields, same design).

I would like to make some changes to all of the EXCEL files (add a field, change a formula).

The changes will be the same for all the 500 EXCEL files.

My question is:

Is the software capable of opening the first EXCEL file, making the changes, saving the file.
Then it opens the second EXCEL file in the folder, makes the changes, saves the file.
Then it opens the third EXCEL file in the folder, makes the changes, saves the file.
And continues to modify all the 500 files in the folder without any user intervention (Like a batch process) ?
#2
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")
#3
I tried this code...but there was an error in the follow line:

FileExists(file2)) del- file2 ;;delete if exists, or Excel will ask

=/
#4
Upgrade QM or replace word FileExists to dir.


Forum Jump:


Users browsing this thread: 1 Guest(s)