Dear Rizwan,

Hope you will be fine.

My work is to format excel files from system generated format to another predefined format. I have macros to to do my task rapidly. This macro formats data and then paste it into that template.

I have to run this macro on more than 200 files(Existing in a folder). All i want to do,

* In order to decrease the effort of running the macros 200 times how can i select all file which are present in that particluar folder and format(paste on Template) by running macro once?

* is it possible using VB.net?

All i want is if you can guide me that how this idea can be implemented on system. Insha Allah i will try using VS2010.

Hassan Raza

Sub ListAllFiles()
   Dim fs As FileSearch, ws As Worksheet, i As Long
   Set fs = Application.FileSearch
   With fs
       .SearchSubFolders = False ' set to true if you want sub-folders included
       .FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
       .LookIn = "C:\" 'modify this to where you want to serach
       If .Execute > 0 Then
         Set ws = Worksheets.Add
         For i = 1 To .FoundFiles.Count
         ws.Cells(i, 1) = .FoundFiles(i)
         MsgBox "No files found"
       End If
   End With
End Sub

the above is taken from: http://www.ozgrid.com/forum/showthread.php?t=65530

with some modification you should be able to open all files in excel and apply your macro over the data and paste into your template.


All Answers

Answers by Expert:

Ask Experts


Syed Rizwan Muhammad Rizvi


I can answers questions regarding web based and desktop based programming in VB.Net. Which can include SOAP, XML, Custom Controls, COM Interoperability etc.


Have been working in this specific area for last 2 years previously I was a VB 6 Developer with experties in other languages as well. Total 10 years of programming experience.

©2016 About.com. All rights reserved.