VB.NET/Automation

Advertisement


Question
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.

Regards,
Hassan Raza

Answer
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)
         Next
       Else
         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.

VB.NET

All Answers


Answers by Expert:


Ask Experts

Volunteer


Syed Rizwan Muhammad Rizvi

Expertise

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

Experience

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.