Opening files in VB

[Stephen]

Hi All,

I've found in previous Posts on how to open all (excel, text etc
files, using VB, in a particular directory.

What I am looking for is to open all files in the current director
(except those already open) and asking me first (YES/NO option) if
want to open it. Is there an example anywhere I can find, or is ther
an easy way of doing this?

Please help

Stephe

 

[Bob Phillips]

Hi Stephen,

Here's some code

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim FSO As Object
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\myTest"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
If Not IsWbOpen(file.Name) Then
Workbooks.Open FileName:=file.Path
End If
End If
Next file

End If ' sFolder <> ""

End Sub

Function IsWbOpen(FileName As String) As Boolean
On Error Resume Next
IsWbOpen = CBool(Len(Workbooks(FileName).Name))
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stephen]

Hi Bob,

Thanks for the code, but this not completely what I was looking for.
What I need is to open the files in the current directory (could b
anywhere) and with each file it opens to ask (msgbox) if I want to ope
it or not (YES/NO option).

Stephen

 

[Bob Phillips]

Stephen,

BY current, do you mean the activeworkbook.

Version 2.

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim FSO As Object
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim ans As String

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = ActiveWorkbook.Path
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
If Not IsWbOpen(file.Name) Then
ans = MsgBox("Do you wnat to open " & file.Name,
vbYesNo)
If ans = vbYes Then
Workbooks.Open FileName:=file.Path
End If
End If
End If
Next file

End If ' sFolder <> ""

End Sub

Function IsWbOpen(FileName As String) As Boolean
On Error Resume Next
IsWbOpen = CBool(Len(Workbooks(FileName).Name))
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stephen]

Hi Bob,

I've tried the code and this is exactly what I was looking for. Than
you so much


Stephen

Stephen,

BY current, do you mean the activeworkbook.

Version 2.

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim FSO As Object
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim ans As String

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = ActiveWorkbook.Path
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
If Not IsWbOpen(file.Name) Then
ans = MsgBox("Do you wnat to open " & file.Name,
vbYesNo)
If ans = vbYes Then
Workbooks.Open FileName:=file.Path
End If
End If
End If
Next file

End If ' sFolder <> ""

End Sub

Function IsWbOpen(FileName As String) As Boolean
On Error Resume Next
IsWbOpen = CBool(Len(Workbooks(FileName).Name))
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct

 

[Tom Ogilvy]

I think you were lucky - perhaps it is what you want, but it sounds more
like you want

sFolder = curdir

--
Regards,
Tom Ogilvy

Hi Bob,

I've tried the code and this is exactly what I was looking for. Thank
you so much


Stephen

Stephen,

BY current, do you mean the activeworkbook.

Version 2.

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim FSO As Object
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim ans As String

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = ActiveWorkbook.Path
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
If Not IsWbOpen(file.Name) Then
ans = MsgBox("Do you wnat to open " & file.Name,
vbYesNo)
If ans = vbYes Then
Workbooks.Open FileName:=file.Path
End If
End If
End If
Next file

End If ' sFolder <> ""

End Sub

Function IsWbOpen(FileName As String) As Boolean
On Error Resume Next
IsWbOpen = CBool(Len(Workbooks(FileName).Name))
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)