I need some VBA code to open all excel files (matching a criteria) within a folder. Can anyone help? Thanks Richard
The sample code expects two text boxes txtFolder and txtFileSpec which will be populated with the folder and the file spec. The
routine checks for the existance of a matching file if found it opens it and then closes it. You would do some sort of processing at
this point. If you want the files open and left open you can use Shell command
Syntax
Shell(pathname[,windowstyle])
The Shell function syntax has thesenamed arguments:
Part Description
pathname Required; Variant (String). Name of the program to execute and any requiredarguments orcommand-line switches; may include
directory or folder and drive.
windowstyle Optional. Variant (Integer) corresponding to the style of the window in which the program is to be run. If windowstyle
is omitted, the program is started minimized with focus.
For my code you need to make a reference to the Excel type library. For shell you just use the shell command instead of managing
Excel your self
Shell Me.txtFolder & strFile, vbNormalNoFocus
Hope this Helps
Michael McCaffrey
michael@mamccaffrey.com
Private Sub cmdProcessExcel_Click()
Dim oExcel As Excel.Application
Dim strFile As String
Set oExcel = New Excel.Application
’ me.txtfolder = “D:WebBasedQandA”
’ me.txtFileSpec = “*.xls”
’ you would provide the user with a way to alter these
’ Get First matching file
oExcel.Visible = True
strFile = Dir(Me.txtFolder & Me.txtFileSpec)
Do While strFile <> “”
oExcel.Workbooks.Open Me.txtFolder & strFile
'Process the file here or call a routine to process it
'oExcel.ActiveWorkbook.Close True
oExcel.ActiveWorkbook.Close False
strFile = Dir
Loop
oExcel.Quit
Set oExcel = Nothing
End Sub
Private Sub Form_Load()
Me.txtFolder = “D:WebBasedQandA”
Me.txtFileSpec = “*.xls”
End Sub
