Run Excel Macro on all xls files in folder

Question

I am trying to run this code VB code but it giving me error which is mentioned below.

Any help will greatly appreciated as I am new to VB.

**Code :** 
Option Explicit
Public Sub ReadExcelFiles(FolderName As String)
Dim FileName As String

' Add trailing \ character if necessary
'
If Right(FolderName, 1) <> "\" Then FolderName = FolderName & "\"

FileName = Dir(FolderName & "*.xls")

Do While FileName <> ""
Workbooks.Open (FolderName & FileName)

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Anil"
    Range("A2").Select
End Sub

Workbooks(FileName).Close
FileName = Dir()
Loop
End Sub

Public Sub test()
ReadExcelFiles ("C:\Macro")
End Sub

command :cscript c:\macro\test.vbs

Result: error , line 2 , char 38 , expected ')'

score 1 · Answer 1 · answered Feb 09 '13 at 05:29

Your errors are because you are setting Foldername As String but you don't need "As String". However, there are more errors in your code. VBScript does not work exactly like Visual Basic or the macros in Excel. You need to actually call the function/subroutine for something to execute. Somewhere in your code (outside of your subroutines) you must Call test

Next, the Dir() function is not available in VBScript so you have to use something different. Something very comparable is Dim fso: set fso = CreateObject("Scripting.FileSystemObject") then use fso.FIleExists(FileName)

Next, you cannot access an Excel workbook like you traditionally do in a macro. You cannot use Workbooks.Open (FolderName & FileName). You can use Dim xl: set xl = CreateObject("Excel.application") and then use xl.Application.Workbooks.Open FileName

Here is my code to only OPEN an excel workbook from vbscript

Option Explicit

Call test

Sub ReadExcelFiles(FolderName)
    Dim FileName
    Dim fso: set fso = CreateObject("Scripting.FileSystemObject")
    Dim xl: set xl = CreateObject("Excel.application")

    If Right(FolderName, 1) <> "\" Then FolderName = FolderName & "\"

    FileName = (FolderName & "test" & ".xls")

    If (fso.FIleExists(FileName)) Then 
        xl.Application.Workbooks.Open FileName
        xl.Application.Visible = True
    End If
End Sub

Sub test()
    ReadExcelFiles ("C:\Users\Developer\Desktop\")
End Sub

Now modifying the spreadsheet will be your next task. This should get you in the right direction. Hope that helps.

Run Excel Macro on all xls files in folder

1 Answers1