Open an Excel file with macro

[benjamin132]

Hello guys, i need some help.
I have to open an Excel file with a macro thanks to a path.
I explained :
I have, store in a variable, this :
[LLD] -[SD2022_LP34.3_WDM_P1_CTA_BRO SAVOIE_EXTENSION_OMEGA]
[LLD] - [NOE]- [SD2022_WDM_P0_NOE_Raccordement-Belfort-RIP-Altitude-Doubs-Dijon_CREATION_RIP]
[LLD]-[SD2021 FO Eqts CTA raccordement MO Chambéry_Omega]
I have to open the Excel using the information contained in this str.
If we take the exemple of the first str : [LLD] -[SD2022_LP34.3_WDM_P1_CTA_BRO SAVOIE_EXTENSION_OMEGA]
First of all, need to select the root of the path, it is always : \PARTAGES\1501-1550\M01514\PUBLIC\1 - SP équipementier
then need to select the region that can be only : CTA, MED, NOE, SWT, WSF,WST,IDF and in the str only one of this list can be inside. Here it is CTA.

then need to select the years here it is 2022:

And select the folder thanks to the end of the str :

Finaly open the only one Excel inside the folder so perhaps open "*.xlsx" should works
If you need more informations, I can give more. Thank you :D

 

[rlv01]

A possible method.

Sub TestConcept()
    Dim Variable1 As String, FolderPath As String, Region As String, RegionList As String
    Dim FSO As Object, FFolder As Object, FFile As Object
    Dim X As Variant
    
    'Data from your post
       Variable1 = "[LLD] -[SD2022_LP34.3_WDM_P1_CTA_BRO SAVOIE_EXTENSION_OMEGA]"
    
       FolderPath = "\PARTAGES\1501-1550\M01514\PUBLIC\1 - SP équipementier"
    
       RegionList = "CTA$,MED$,NOE$,SWT$,WSF$,WST$,IDF$"
    
    'Get region
    Region = ""
    For Each X In Split(Variable1, "_")
        If InStr(RegionList, X & "$") > 0 Then
            Region = CStr(X)
        End If
    Next X
    
    If Region <> "" Then
    
        'Get folder path
        X = Split(Replace(Variable1, "]", ""), "[")
        FolderPath = FolderPath & "\" & Region & "\" & Region & "_SD2022\" & X(UBound(X))
        
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FolderExists(FolderPath) Then
            Set FFolder = FSO.GetFolder(FolderPath)
            
            'Open first non macro-enabled workbook found since there should only be one file in the folder
            For Each FFile In FFolder.Files
                If (UCase(Right(FFile.Path, 5)) = ".XLSX") Then
                    Application.Workbooks.Open Filename:=FFile.Path
                    Exit Sub
                End If
            Next FFile
            MsgBox "No .xlsx files found in folder:" & vbCr & vbCr & FolderPath
        Else
            MsgBox "Folder:" & vbCr & vbCr & FolderPath & vbCr & vbCr & "does not exist"
        End If
    Else
        MsgBox "Valid Region not found in Variable: " & vbCr & vbCr & Variable1
    End If
End Sub

 

[benjamin132]

Hi, your method is awesome the path is good but there is a problem.
I don't know why and tried many things to fix the problem but it doesn't work.
As you can see in the next picture, the path is exactly the same than the real path but this "if" doesn't work. He never enter inside the IF but when I delete this condition, that open my Excel file normally because the path is good However it crash after because of the other files that can't be opened because it is not Excel File and I don't want it. So i need to fix this "if".
Do you know what can I do ?

 

[rlv01]

I suspect it did not work because you changed the code. Here is what I posted:

          If (UCase(Right(FFile.Path, 5)) = ".XLSX") Then

Here is how you changed it

       If (UCase(Right(FFile.Path, 5)) = ".xlsx") Then

It will not work for lower case ."xlsx". You should change it back.

Please do not post pictures of your code. Instead post the actual code and use code tags as I did above. It makes it easier to work with

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

Keep in mind that I tested the code before I posted it yesterday, and it works if the path is valid and the folder contains and excel file with the file extension ".xlsx". So if it is not working for you, you should provide a more detailed explanation and include some examples, such as the name of the the file that you are tying to open. The XL2BB tool is a good way to do that.

Example:

Book2
	A	B
1	Variable	File to be opened
2	[LLD] -[SD2022_LP34.3_WDM_P1_CTA_BRO SAVOIE_EXTENSION_OMEGA]	MyExcelFile.xlsx
Sheet1

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[benjamin132]

Okay thank you a lot, yeah i did somes changes and that create a bug and you find it, thanks a lot
Here is my final code :

Sub TestConcept()
    Dim Variable1 As String, FolderPath As String, Region As String, RegionList As String
    Dim FSO As Object, FFolder As Object, FFile As Object
    Dim X As Variant
    
    'Data from your post
       Variable1 = "[LLD] -[SD2022_LP34.3_WDM_P1_CTA_BRO SAVOIE_EXTENSION_OMEGA]"
    
       FolderPath = "\\bt0d0000\partages\1501-1550\M01514\PUBLIC\1 - SP équipementier"
    
       RegionList = "CTA$,MED$,NOE$,SWT$,WSF$,WST$,IDF$"
    
    'Get region
    Region = ""
    For Each X In Split(Variable1, "_")
        If InStr(RegionList, X & "$") > 0 Then
            Region = CStr(X)
        End If
    Next X
    
    If Region <> "" Then
    
        'Get folder path
        X = Split(Replace(Variable1, "]", ""), "[")
        FolderPath = FolderPath & "\" & Region & "\" & Region & "_SD2022\" & X(UBound(X)) & "\" & "00 - LEP"
        
        FolderPath = Application.WorksheetFunction.Substitute(FolderPath, Chr(10), "")
        
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FolderExists(FolderPath) Then
            Set FFolder = FSO.GetFolder(FolderPath)
            
            'Open first non macro-enabled workbook found since there should only be one file in the folder
            For Each FFile In FFolder.Files
                MsgBox FFile.Path
                If (UCase(Right(FFile.Path, 5)) = ".XLSX") Then
                    Application.Workbooks.Open Filename:=FFile.Path
                    Exit Sub
                End If
            Next FFile
            MsgBox "No .xlsx files found in folder:" & vbCr & vbCr & FolderPath
        Else
            MsgBox "Folder:" & vbCr & vbCr & FolderPath & vbCr & vbCr & "does not exist"
        End If
    Else
        MsgBox "Valid Region not found in Variable: " & vbCr & vbCr & Variable1
    End If
End Sub

Thanks for your comments, I'll take them into account next time.