none
VBA - File System Object - Get Current Workbook Folder Path RRS feed

  • Question

  • Hi friends,

     

    How do I set the File system object to the current  folder the excel file is in?

     

     

     Set objFSO = CreateObject("Scripting.FileSystemObject")         'FileSystemObject

      Set objFolder = objFSO.GetFolder(ThisWorkbook.FullName)  'Get the folder object    X

     

      Set objFolder = objFSO.GetFolder(ThisWorkbook.FullName).path   X

     

     

     

    It is  part of this code I am trying to adapt

     

     
      
       
    Sub ListFiles()
    
      'http://software-solutions-online.com/list-all-files-in-a-folder-and-create-hyperlink-to-files-excel-vba/
    
     Dim objFSO As Object
        Dim objFolder As Object
        Dim objFile As Object
        Dim i As Integer
        
        
        MyPath = ThisWorkbook.Path
       
        Set objFSO = CreateObject("Scripting.FileSystemObject")         'FileSystemObject
      
        Set objFolder = objFSO.GetFolder(ThisWorkbook.FullName)  'Get the folder object
        
     
        
        i = 1
        For Each objFile In objFolder.Files
        
    
            ' List File Names  -  in Column  4
            Range(Cells(i + 1, 4), Cells(i + 1, 4)).Select
            
            'Create hyperlinks
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            objFile.Path, _
            TextToDisplay:=objFile.Name
            i = i + 1
        Next objFile
      
        End Sub

     

     

    Thank you for any tips

     


    Cheers Dan :)

    Thursday, November 17, 2016 12:50 PM

Answers

  • Hi ,

    Change ThisWorkbook.FullName to ThisWorkbook.Path because full name also includes the file name which is not a valid Folder Path hence you were getting the error.

    Sub ListFiles()
    
      'http://software-solutions-online.com/list-all-files-in-a-folder-and-create-hyperlink-to-files-excel-vba/
    
     Dim objFSO As Object
        Dim objFolder As Object
        Dim objFile As Object
        Dim i As Integer
        
        
        MyPath = ThisWorkbook.Path
       
        Set objFSO = CreateObject("Scripting.FileSystemObject")         'FileSystemObject
      
        Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)  'Get the folder object
        
     
        
        i = 1
        For Each objFile In objFolder.Files
        
    
            ' List File Names  -  in Column  4
            Range(Cells(i + 1, 4), Cells(i + 1, 4)).Select
            
            'Create hyperlinks
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            objFile.Path, _
            TextToDisplay:=objFile.Name
            i = i + 1
        Next objFile
      
        End Sub


    Vish Mishra

    • Marked as answer by Dan_CS Thursday, November 17, 2016 1:59 PM
    Thursday, November 17, 2016 1:04 PM

All replies

  • Hi ,

    Change ThisWorkbook.FullName to ThisWorkbook.Path because full name also includes the file name which is not a valid Folder Path hence you were getting the error.

    Sub ListFiles()
    
      'http://software-solutions-online.com/list-all-files-in-a-folder-and-create-hyperlink-to-files-excel-vba/
    
     Dim objFSO As Object
        Dim objFolder As Object
        Dim objFile As Object
        Dim i As Integer
        
        
        MyPath = ThisWorkbook.Path
       
        Set objFSO = CreateObject("Scripting.FileSystemObject")         'FileSystemObject
      
        Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)  'Get the folder object
        
     
        
        i = 1
        For Each objFile In objFolder.Files
        
    
            ' List File Names  -  in Column  4
            Range(Cells(i + 1, 4), Cells(i + 1, 4)).Select
            
            'Create hyperlinks
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            objFile.Path, _
            TextToDisplay:=objFile.Name
            i = i + 1
        Next objFile
      
        End Sub


    Vish Mishra

    • Marked as answer by Dan_CS Thursday, November 17, 2016 1:59 PM
    Thursday, November 17, 2016 1:04 PM
  • Hello Vish,

    it was meant  to be that easy  :) 

    - not to me

     

    https://msdn.microsoft.com/en-us/library/aa711216(v=vs.71).aspx

     

    I looked every where but alas  - it was not to be

     

    Thank you for this point in the right direction

     

    Have a great day now!


    Cheers Dan :)

    Thursday, November 17, 2016 1:59 PM