Ouvrir explorateur pour choisir fichier excel Résolu

Question

Bonjour a tous,

Je veux ajuster un code afin de pouvoir choisir le fichier excel que je veux ouvrir sur mon ordinateur.

En ce moment, je dois donner le chemin exacte, mais je veux qu'il m'ouvre l'explorateur afin de choisir un fichier excel.

Comment faire s.v.p?

Voici mon code:
Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
 
    Set xlObject = New Excel.Application
 
   Set xlWB = xlObject.Workbooks.Open("C:\Documents and Settings\xxxxxxx\Desktop\Order Template.xls") 'Open your book here
              
   Clipboard.Clear
   With xlObject.ActiveWorkbook.ActiveSheet
       .Range("A1:z200").Copy 'Set selection to Copy
   End With
   With MSFlexGrid1
       .Redraw = False     'Dont draw until the end, so we avoid that flash
       .Row = 0            'Paste from first cell
       .Col = 0
       .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
       .ColSel = .Cols - 1
       .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
       .Col = 1            'Just to remove that blue selection from Flexgrid
       .Redraw = True      'Now draw
   End With
      
   xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
   'Close Excel
   xlWB.Close
   xlObject.Application.Quit
   Set xlWB = Nothing
    Set xlObject = Nothing

Merci pour votre aide.

avyrex · Accepted Answer

Super, avec vos idées, voici le résultat:

Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
On Error GoTo MyErrHandler

    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (*.xls)|*.xls"
        .InitDir = App.Path
        .ShowOpen
        If Not .FileName = "" Then
            Set xlObject = New Excel.Application
            Set xlWB = xlObject.Workbooks.Open(.FileName)
          LakeShore.Command2.Visible = True
   Clipboard.Clear
  
   With xlObject.ActiveWorkbook.ActiveSheet
       .Range("A1:z200").Copy 'Set selection to Copy
   End With
   With MSFlexGrid1
       .Redraw = False     'Dont draw until the end, so we avoid that flash
       .Row = 0            'Paste from first cell
       .Col = 0
       .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
       .ColSel = .Cols - 1
       .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
       .Col = 1            'Just to remove that blue selection from Flexgrid
       .Redraw = True      'Now draw
   End With
      
   xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
   'Close Excel
   xlWB.Close
   xlObject.Application.Quit
   Set xlWB = Nothing
    Set xlObject = Nothing 
    
     End If
    End With
    
    Exit Sub
MyErrHandler:
    Err.Clear

cs_Jack · Answer

Salut
Ajoute un CommonDialog et sa méthode .ShowOpen pour choisir un fichier.

Vala
Jack, MVP VB
NB : Je ne répondrai pas aux messages privés

Le savoir est la seule matière qui s'accroit quand on la partage (Socrate)

Renfield · Answer

en VBA:

Application.GetOpenFileName

Renfield - Admin CodeS-SourceS - MVP Visual Basic

c148270 · Answer

bonjour

Une autre méthode.
Inclule module et déclarations suivants

Private Const BIF_BROWSEINCLUDEFILES = &H4000
Private Const MAX_PATH = 260
Private Type BROWSEINFO
   hwndOwner      As Long
   pidlRoot       As Long
   pszDisplayName As Long
   lpszTitle      As Long
   ulFlags        As Long
   lpfnCallback   As Long
   lParam         As Long
   iImage         As Long
End Type

Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BROWSEINFO) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Public Function OpenDirectoryTV()
   Dim lpIDList As Long
   Dim sBuffer As String
   Dim szTitle As String
   Dim tBrowseInfo As BROWSEINFO
   szTitle = odtvTitle
   With tBrowseInfo
      .hwndOwner = 0
      .lpszTitle = lstrcat(szTitle, "")
      .ulFlags = BIF_BROWSEINCLUDEFILES
   End With
   lpIDList = SHBrowseForFolder(tBrowseInfo)
   If (lpIDList) Then
      sBuffer = Space(MAX_PATH)
      SHGetPathFromIDList lpIDList, sBuffer
      sBuffer = Left(sBuffer, InStr(sBuffer, vbNullChar) - 1)
      OpenDirectoryTV = sBuffer
   End If
End Function

bonne journée

Renfield · Answer

Mais c'est intégré !
pas besoin de joindre une API ou un module...


Dim zFilePath As Variant
    zFilePath = Application.GetOpenFilename()
    If VarType(zFilePath) = vbString Then
        MsgBox zFilePath
    End If

Renfield - Admin CodeS-SourceS - MVP Visual Basic

c148270 · Answer

C'est vrai.
J'utilise les api car je peut utiliser le module tel quel quelque soit l'application (access, excel,..)

Ouvrir explorateur pour choisir fichier excel

6 réponses

Votre réponse

Discussions similaires