Open & process multiple worksheets then move them to different folder.
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 |
Open & process multiple worksheets then move them to different folder.
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. 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 |
All times are GMT +1. The time now is 06:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com