you still needed the first part of your code, to select the files, but
if you just want all the files in a specific directory, then you do not need to use getopenfilename
change the
loop of xlfiles to
vb Code:
srcfolder = ' your path with trailing \
fname = dir(srcfolder & "*.xlsx") ' get first file name to match pattern
do until len(fname) = 0 ' stop loop when no more files found
set wbk = workbooks.open(srcfolder & fname)
set sht = wbk.sheets("sheet1") ' set sheet object, change sheet name to suit, or use index
' do all processing with workbook
wbk.saveas savfolder & fname
wbk.close
fname = dir ' get next file name
loop
use wbk or sht objects, at all times to refer to the workbook you are processing, avoid using activeworkbook as this can lead to errors, all ranges or cells are then ranges within the sht object
also use trailing backslash for savfolder
saveFolder = "C:\Users\DTurcotte\Desktop\VBA Target\"
you can then use like
vb Code:
with sht
If .Cells(1, 1).Value <> "ROOM #" Then .Cells(1, 1).Value = "ROOM #"
'etc
end with
if you always look to see if the value is not a value then set it to a value, it is probably faster to just set the value whether it is or not the value already, you should also turn off screenupdating too
application.screenupdating = false.
if you want to use totalrows variable it should be assigned a value for each workbook after it is open
Originally Posted by westconn1
Reply With Quote
