VBA Get File Then Open

[Pinaceous]

Hi All,

I'm working on a code that checks to see if a file exists; then I would like it to open that file.

Right now, it tells me that it exists but how do I set it to open that file?

Sub getfiles()

    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object, sf
    Dim i As Integer, colFolders As New Collection, ws As Worksheet
    'Dim folderPath As String
    'Dim wb As Workbook
    Dim fileName As String
    
    Set ws = ActiveSheet
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("C:\Users\Documents\New folder\")
    
    colFolders.Add oFolder          'start with this folder
    
    Do While colFolders.Count > 0      'process all folders
        Set oFolder = colFolders(1)    'get a folder to process
        colFolders.Remove 1            'remove item at index 1
    
    'Do While fileName <> ""
    
        For Each oFile In oFolder.Files
            If oFile.DateLastModified > Now - 7 Then
                ws.Cells(i + 1, 1) = oFolder.path
                ws.Cells(i + 1, 2) = oFile.Name
                'ws.Cells(i + 1, 3) = "RO"
                ws.Cells(i + 1, 3) = oFile.DateLastModified
                i = i + 1
            End If
        Next oFile

        'add any subfolders to the collection for processing
        For Each sf In oFolder.subFolders
            colFolders.Add sf
        Next sf
    Loop

'Loop

fileName = Dir(folderPath & "BookOne.xlsx")

If strFileExists = "BookOne.xlsx" Then
        MsgBox "The selected file doesn't exist"
    Else
        MsgBox "The selected file exists"
        


        [B]'Workbooks.Open "BookOne.xlsx"[/B]
      [B]  'THIS IS THE PART I NEED HELP WITH!![/B]


        
    End If
    
End Sub

Please help me if you can I would really appreciate it!

Thank you!
pinaceous

 

[Larry Haydn]

To open the file, you will need the full path - that is, the folder path + the filename.
Might be something like this:

If strFileExists = "BookOne.xlsx" Then
        MsgBox "The selected file doesn't exist"
    Else
        MsgBox "The selected file exists"
    


        [B]'Workbooks.Open "BookOne.xlsx"[/B]
      [B]  'THIS IS THE PART I NEED HELP WITH!![/B]
 
    '======================================
    '    Add a full path variable at the top of your module
    '======================================
   Dim fullPath As String
 
   '=============================
   '   Form the full path (folder + filename)
   '=============================
   fullPath = oFolder & oFile.Name

   '=============
   '   Open the file
   '=============
   Workbooks.Open fullPath

    
End If

 

[Pinaceous]

Hey Larry Haydn!

Many thanks for sharing your experience with me on this sub.

I am getting an error with:

Workbooks.Open fullPath

in providing your instructions.

Do you get an error as well?

Please let me know.

Thank you,
pinaceous

 

[Larry Haydn]

OK, I am trying to run the whole macro...
wait a while.

 

[Larry Haydn]

Here is a simple code that helps to check whether a file exists.
If the file exists then open it.
You can work around this code to fit your code.
Tested: It works.

Sub CheckFileExists()
 
Dim strFileName As String
Dim strFileExists As String
 
    strFileName = "C:\TestFolder\TestFile.xlsx"
    strFileExists = Dir(strFileName)
 
   If strFileExists = "" Then
        MsgBox "The selected file doesn't exist"
    Else
        Workbooks.Open strFileName
    End If
 
End Sub

 

[Pinaceous]

I appreciate that you posted this last sub but if the pathway is not really known and I find the file in using this first sub, is there any way for me to use this information to then apply the concept that your introduced in your last posted sub CheckFileExists() into the first sub getfiles()?

 

[frabulator]

I appreciate that you posted this last sub but if the pathway is not really known and I find the file in using this first sub, is there any way for me to use this information to then apply the concept that your introduced in your last posted sub CheckFileExists() into the first sub getfiles()?

You can convert the code into a function.

(adopted from larrys code)

Function CheckIfExist(FileStr As String) As Boolean
    Dim strFileName As String
    Dim strFileExists As String
   
    strFileName = FileStr
    strFileExists = Dir(strFileName)
   
    If strFileExists = "" Then
        CheckIfExist = False
    Else
        CheckIfExist = True
    End If
End Function



Sub Run()

    Dim FullPath As String
    FullPath = "C:\Bob.xlsx"
    If CheckIfExist(FullPath) = True Then Workbooks.Open FullPath

End Sub