none
Get greater than 255 characters in string RRS feed

  • Question

  • Hi All,

    I want to consolidate two string whose total length is greater than 255 characters.

    Excel is taking only value upto 255.

    I want to open new spreadsheet using that consolidated string by using

    Workbooks.Open(ConsolidatedString)

    Can anyone help me




    Tuesday, June 4, 2013 10:38 AM

Answers

  • You can't do it that way.

    You can break up the string into individual folders and follow the path sequentially using code like below, called by

    OpenLongName ConsolidatedString

    Sub OpenLongName(strN As String)
        Dim strP As Variant
        Dim i As Integer
        
        strP = Split(strN, "\")

        For i = LBound(strP) To UBound(strP) - 1
            If i = LBound(strP) Then
                ChDir strP(i) & "\"
            Else
                ChDir ".\" & strP(i)
            End If

        Next i
        Workbooks.Open strP(UBound(strP))
    End Sub


    Tuesday, June 4, 2013 2:03 PM

All replies

  • You can't do it that way.

    You can break up the string into individual folders and follow the path sequentially using code like below, called by

    OpenLongName ConsolidatedString

    Sub OpenLongName(strN As String)
        Dim strP As Variant
        Dim i As Integer
        
        strP = Split(strN, "\")

        For i = LBound(strP) To UBound(strP) - 1
            If i = LBound(strP) Then
                ChDir strP(i) & "\"
            Else
                ChDir ".\" & strP(i)
            End If

        Next i
        Workbooks.Open strP(UBound(strP))
    End Sub


    Tuesday, June 4, 2013 2:03 PM
  • Thanx Bernie

    Problem with workbooks.Open is that it is not taking URL greater than 255 charecters

    Wednesday, June 5, 2013 7:38 AM
  • You might try this approach, which uses the DOS Subst command to assign a drive letter to a path.  I haven't tested it with a long path like yours but it works for me as shown below with a short path.  Pick a drive letter that is no in use on your machine of course.  Here I map c:\aaa to i:

    Sub TestOpen()
        MakeTempPath "c:\aaa", "i"
        Workbooks.Open "i:\new.xlsm"
    End Sub

    Sub MakeTempPath(PathStr As String, DrvLtr As String)
        Shell Environ$("comspec") & " /c Subst " & DrvLtr & ": " & PathStr, vbHide
        Application.Wait DateAdd("s", 1, Now)
    End Sub

    Sub TestRemove()
        RemoveTempPath "i"
    End Sub

    Sub RemoveTempPath(DrvLtr As String)
        Shell Environ$("comspec") & " /c Subst " & DrvLtr & ": /d", vbHide
    End Sub

    Wednesday, June 5, 2013 1:44 PM