Open & process multiple worksheets then move them to different folder.

P

Prasad Vanka

Hi,

I am new to VBA Excel but not new to programming.

I receive multiple worksheets every day through email (1 worksheet per
email). Then I do the following process for each of the worksheets:

1. Save the worksheet as workfile.xls to the folder "H:/To be
processed/".
2. Process the worksheet.
3. Move the worksheet to the folder "H:/Processed".

So basically my code just works only when the name of the file is
workfile.xls and when it is situated in so and so folder. But I want
to automate this process and want the code to pick up not one but all
worksheets, one after other independent of their names, process each
of them and once processing is done then move each of those to a
different folder. I don't want to hardcode the folder name but want
the code to pick it up as parameter or take it from somewhere.

I think it is possible to do the above. I need some
help/suggestions/example codes I can refer to.

Thanks in advance,
Prasad Vanka
 
J

Jan Karel Pieterse

Hi Prasad,
So basically my code just works only when the name of the file is
workfile.xls and when it is situated in so and so folder. But I want
to automate this process and want the code to pick up not one but all
worksheets, one after other independent of their names, process each
of them and once processing is done then move each of those to a
different folder. I don't want to hardcode the folder name but want
the code to pick it up as parameter or take it from somewhere.
Click to expand...

Something like this?

Sub GetOpenFileNameExample2()
Dim vFilename As Variant
Dim sPath As String
Dim lFilecount As Long
Dim lCount As Long
sPath = "c:\windows\temp\"
ChDrive sPath
ChDir sPath
vFilename = Application.GetOpenFilename("Microsoft Excel files
(*.xls),*.xls", , "Please select the file(s) to process", , True)
If TypeName(vFilename) = "Boolean" Then Exit Sub
For lCount = 1 To UBound(vFilename)
MsgBox vFilename(lCount)
'Process your file(s) here
Next
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Top