Opening files simultaneously

[entwined]

Hi experts,

I want to open all the files with the first 3 characters of the filenames are the same.

Example:
I have a folderpath "D:\passed" so "passed" is the name of the folder and located in D:\.

Inside of it there are files (mine are excel files):

NLG-1_P.xls
NLG-1A (done).observation.xls
NLG-1B_observation.xls
NLH-1_F.xls
NLH-1C (with observation).xls
NLH-1B_observation.xls
NLI-1_P.xls
NLI-1B_observation.xls

Now, I need a code that will open all the files starting with "NLG" only so that should open NLG-1_P.xls, NLG-1A (done).observation.xls and NLG-1B_observation.xls simultaneously.

Any help would be very much appreciated. Thanks much...... :)

[Trebor76]

Hi entwined,

As long as the all the files that start with characters in the 'varMyChars' variable below are all Excel based files, this should do the trick:


Option Explicit
Sub Macro1()
'http://www.vbaexpress.com/forum/showthread.php?t=42895

'Open all files in the 'strMyDir' that start with with 'varMyChars'

Dim strMyDir As String
Dim varMyChars As Variant
Dim objFSO As Object, _
objFile As Object

strMyDir = "D:\passed\" 'Directory to look in. Don't forget the trailing backslash.
varMyChars = "NLG" 'Starting characters for which to open file if found. Change to suit - can be text or numeric.

Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each objFile In objFSO.GetFolder(strMyDir).Files
If Left(objFile.Name, Len(varMyChars)) = varMyChars Then
Workbooks.Open Filename:=strMyDir & objFile.Name
End If
Next objFile

Set objFSO = Nothing
Set objFile = Nothing

End Sub

Regards,

Robert

[entwined]

That worked so well Robert! But I'm sorry with my previous post, I think I missed one thing. :)
The folder also contains a few non-excel files. Mine contains .rtf files so there are files named "NLG-1_P.rtf", "NLH-1_F.rtf" so on. Only excel files should be opened and nothing else.

Thanks again!... :)

[Kenneth Hobs]

' Tools > References > Microsoft Scripting Runtime
Sub Macro1()
'Open all files in the 'strMyDir' that start with with 'varMyChars'
Dim strMyDir As String
Dim objFSO As Scripting.FileSystemObject, objFile As File

strMyDir = "x:\fso\" 'Directory to look in. Don't forget the trailing backslash.
Set objFSO = New Scripting.FileSystemObject

For Each objFile In objFSO.GetFolder(strMyDir).Files
If Left(objFile.Type, 15) = "Microsoft Excel" Then
Workbooks.Open Filename:=strMyDir & objFile.Name
End If
Next objFile
End Sub

[Trebor76]

As well as Kenneth's clever solution, as we're already using FileSystemObject we can use its GetExtensionName function as so:


Option Explicit
Sub Macro1()

'http://www.vbaexpress.com/forum/showthread.php?t=42895

'Open all files in the 'strMyDir' that start with with 'varMyChars'

Dim strMyDir As String
Dim varMyChars As Variant
Dim objFSO As Object, _
objFile As Object

strMyDir = "D:\passed\" 'Directory to look in. Don't forget the trailing backslash.
varMyChars = "NLG" 'Starting characters for which to open file if found. Change to suit - can be text or numeric.

Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each objFile In objFSO.GetFolder(strMyDir).Files
If InStr(objFSO.getExtensionName(strMyDir & objFile.Name), "xls") > 0 Then 'Only work with Excel files.
If Left(objFile.Name, Len(varMyChars)) = varMyChars Then
Workbooks.Open Filename:=strMyDir & objFile.Name
End If
End If
Next objFile

Set objFSO = Nothing
Set objFile = Nothing

End Sub

Regards,

Robert

[snb]

or
sub snb()
c00="D:\passed\"
c01=Dir(c00 & "NLG*.xls")

do until c01=""
workbooks.add c00 & c01
c01=Dir
loop
end sub

or a oneliner:

Sub snb()
Workbooks.Add Filter(Split("D:\passed\" & Replace(CreateObject("wscript.shell").exec("cmd /c Dir D:\passed\NLG*.xls /b").stdout.readall, vbCrLf, vbCr & "D:\passed\"), vbCr), ".")
End Sub