CODEWITHSUNDEEP
excelvba
Uģis
Uģis

Reputation: 5

Text cell reference in setting workbook path

I have a VBA script that opens a different workbook located on my desktop. There are two variables in the path that determines the location of file I wish to open: (1) the user of the PC; (2) the name of the file. The user of the PC variable is defined on cell J3, but the the name of the file on J2, both on the same sheet. The code is as follows:

Option Explicit

Sub Chakars()

    Dim BeiguSheet As Worksheet
    Dim FileJauda As String

    Set BeiguSheet = ThisWorkbook.Sheets("Final")

        FileJauda = "C:\Users\ugikam\Desktop\" & "Jauda_" & BeiguSheet.Range("J2").Value & ".xlsm"

    Workbooks.Open (FileJauda)

End Sub

In the line where FileJauda is defined, you can see the reference to cell J2 which works properly. However, when I try to replace another variable in path (namely, user which is "ugikam") by using the line below it could not locate the file anymore. Where is the issue? Could it be related to the fact that "ugikam" is a string of text?

FileJauda = "C:\" & BeiguSheet.Range("J3").Value & "\Desktop\" & "Jauda_" & BeiguSheet.Range("J2").Value & ".xlsm"

Upvotes: 0

Views: 154

Answers (1)

Error 1004
Error 1004

Reputation: 8240

The problem may occurred from invisible characters. i try to clean both strings with the below code. Additionally, you could debug and check one by one cell value using intimidate window. Lastly, you try to open workbook with name FilePath which is not defined anywhere. try replacing Workbooks.Open (FilePath) with Workbooks.Open (FileJauda)

Option Explicit

Sub Chakars()

    Dim BeiguSheet As Worksheet
    Dim FileJauda As String

    Set BeiguSheet = ThisWorkbook.Sheets("Final")

        With BeiguSheet
            FileJauda = "C:\" & _
                        Application.Clean(Application.Trim(.Range("J3").Value)) & _
                        "\Desktop\Jauda_" & _
                        Application.Clean(Application.Trim(.Range("J2").Value)) & _
                        ".xlsm"
        End With

    Workbooks.Open (FileJauda)

End Sub

Upvotes: 0

Related Questions