NomduClasseur=ActiveWorkbook.Name
Dim lWorkbook As Workbook
Dim lFound As Boolean
lFound = False
For Each lWorkbook In Workbooks
If lWorkbook.Name = "NW" Then
lFound = True
Exit For
End If
Next
If lFound Then
<Cas ou le fichier "NW" existe>
End If
Sub es()
Dim fichier As String, x As Workbook
fichier = "MJ13.xls"
On Error Resume Next
Set x = Workbooks(fichier)
If Err = 0 Then
MsgBox fichier & " ouvert"
'ton code
Else
MsgBox fichier & " fermer"
'ton code
End If
On Error GoTo 0
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim NomW As String, NomWP As String, NomF As String, NW As Workbook
NomWP = Cells(1, 2) & "\" & Cells(ActiveCell.Row, 1).Value
NomW = Cells(ActiveCell.Row, 1)
NomF = ActiveCell.Value
'http://www.commentcamarche.net/forum/affich-452291-vba-excel-savoir-si-un-fichier-est-ouvert
Dim lWorkbook As Workbook
Dim lFound As Boolean
lFound = False
For Each lWorkbook In Workbooks
If lWorkbook.Name = NomW Then
lFound = True
Exit For
End If
Next
If lFound Then GoTo Suite
'End If
Workbooks.Open Filename:=NomWP
Suite:
Workbooks(NomW).Activate
'Active toutes les feuilles
nc = ActiveWorkbook.Sheets.Count
For N = nc To 1 Step -1
Sheets(N).Visible = True
Sheets(N).Activate
'If ActiveWorkbook.Sheets(N).Type <> 3 Then Cells(1, 1).Select
Next
'sélectionne la feuille
Sheets(NomF).Select
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim NomW As String, NomWP As String, NomF As String, NW As Workbook
Dim N As Byte
NomW = Cells(ActiveCell.Row, 1)
NomWP = Cells(1, 2) & "\" & NomW
NomF = ActiveCell.Value
If NomF = "" Then Exit Sub
Dim lWorkbook As Workbook
For Each lWorkbook In Workbooks
If lWorkbook.Name = NomW Then GoTo Suite
Next
Workbooks.Open Filename:=NomWP
Suite:
Workbooks(NomW).Activate
For N = 1 To ActiveWorkbook.Sheets.Count
Sheets(N).Visible = True 'Affiche toutes les feuilles
Next
Sheets(NomF).Select 'sélectionne la feuille
End Sub
Option Explicit
Sub Test()
'http://forum.hardware.fr/hfr/Programmation/VB-VBA-VBS/liste-fichiers-repertoire-sujet_57846_1.htm
'ChDir "C:\...Mon chemin....\Mes documents"
Range("2:100").Clear
Dim i As Byte, z As String
ChDrive Left(Cells(1, 2), 1)
ChDir Cells(1, 2).Value
i = 1
z = Dir("*.xls", 1)
While z <> ""
ActiveSheet.Cells(i + 1, 1).Value = z
i = i + 1
z = Dir
Wend
End Sub
Sub Liste_feuilles_Selection()
Dim cell As Object, FAO As String, i As Integer, N As Integer, nc As Integer
For Each cell In Selection
FAO = Cells(1, 2) & "\" & cell.Value
Workbooks.Open (FAO)
nc = ActiveWorkbook.Sheets.Count
For N = nc To 1 Step -1
Sheets(N).Visible = True
Sheets(N).Activate
If Sheets(N).Type <> 3 Then Cells(1, 1).Select
Next
For i = 1 To nc ' Step -1
cell.Offset(0, i) = Sheets(i).Name
Next
ActiveWorkbook.Close 0
Next
'MEF centrer etenvoie à la ligne auto
Selection.CurrentRegion.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
End Sub