Path changes in formula if path to server is mapped to drive letter

Aspose.Cells Product Family
1

Hi there. I have a problem:

I checked the XML of an Excel.xlsm file: A reference is stored as Target=“file:///\superstore\Public\Ref1\Ref.xlsx”

This is correct.

When I load the file without a mapping to the path (i.e. something like W:.…) everything works fine and the path is still correct in the formula when I access the cell.

When I map the folder \\superstore\Public\ to W:\ suddenly the formula changes when loading the file to an aspose.cells workbook. The formula then changes to:

=‘W:\Ref1[Ref.xlsx]Tabelle1’!$A$1

This is definitly not useful as I need the original path as the mapping can be different on every client, it can be W: or X: or whatever. The reason is that the path has to be extracted and it has to be the UNC path.

How can I turn off the automatic conversion to drive letters in aspose.cells? Or ist there a simple alternative that converts the path back to the original UNC? I do know that I could do a search and replace in the formula but this for sure is not what I want. I sure hope I can turn off this automatic conversion within aspose.cells.

Thanks in advance
Norman

It has a formula po

=‘D:\PAT-SU~1\ECC_CHECK~1\Ref1[Ref.xlsx]Tabelle1’!$A$3

2

@norman.neubert
Would you like to provide you sample file and executable console project? We will check it soon.

3

Hi John,

its not really the problem of an Excel file but more of where it is stored. You can reproduce this in your environment. All you need is a file on a network share where the share has no drive letter. Lets say

\\superstore\Public\Ref1\Ref.xlsx

Then you create an Excel file test.xlsx that has a formula in A1 referencing \superstore\Public\Ref1\Ref.xlsx, for example you like this :

=‘\superstore\Public\Ref1[Ref.xlsx]Tabelle1’!$B$5

I load test.xlsx in aspose.cells and then access A1 to read the formula.

dim c as Aspose.Cells.Cell
...
debug.print(c.formula)

if I run this code when \\Superstore\Public... is not mapped with a drive letter i get the following result:

='\\superstore\Public\Ref1\[Ref.xlsx]Tabelle1'!$B$5 --> This is correct

If I run the code and \\Superstore\Public for example is mapped to W: I get the following result (the file was not changed, it is still the same file):

='W:\Ref1\[Ref.xlsx]Tabelle1'!$A$1 → This is not what I want. I still want to get ='\\superstore\Public\Ref1\[Ref.xlsx]Tabelle1'!$B$5

Can the automatic conversion of the path turned off in aspose.cells?

Thanks in advance
Norman

4

@norman.neubert

What configuration did you set up to map \Superstore\Public to W:? I have created a sample file, and the correct result can be obtained by outputting the formula for cell A1. Please refer to the attachment. test.zip (6.8 KB)

The sample code as follows:

Workbook wb = new Workbook(filePath + "test.xlsx");
Cells cells = wb.Worksheets[0].Cells;
Console.WriteLine(cells["A1"].Formula);

The output:

='\\JOHN-HE\share\[a.xlsx]Sheet1'!$A$1
5

Hi John,

thanks for you reply.

I just connected a network drive via the windows explorer to \\Superstore\Public. See screenshot. When I do that I get ='W:\Ref1\[Ref.xlsx]Tabelle1'!$B$5 instead of ='\\superstore\Public\Ref1\[Ref.xlsx]Tabelle1'!$B$5 for .Formula.

image.png (15.5 KB)

6

@norman.neubert
After mapping the shared folder to a network drive, test it on the latest version v24.4 and run the provided sample code. The correct results can still be obtained. Please refer to the attachment. result.png (7.9 KB)

7

I did the update, was on 24.2

The problem still remains. Please look at my screenshot: You can see, that the xlsx file has \\superstore\public in “the binary”. But when I load it to aspose.cell it is converted to w:.

image.png (64.2 KB)

Here is the code for getting a line of cells, in the screenshot you can see that it is w: here.

    Function GetFormulaRangeLine(ByVal oxlwsheet As Aspose.Cells.Worksheet, row As Integer) As List(Of Cell)
        On Error Resume Next

        GetFormulaRangeLine = New List(Of Cell)
        Dim cell As Aspose.Cells.Cell = Nothing
        Dim c As Integer
        For c = oxlwsheet.Cells.MinColumn To oxlwsheet.Cells.MaxColumn
            cell = oxlwsheet.Cells(row, c)
            If cell IsNot Nothing Then
                If cell.Formula <> "" Then
                    GetFormulaRangeLine.Add(cell)
                End If
            End If
        Next
    End Function
8

LinkedFile.xlsx.zip (9.3 KB)

Here is the file. I only added .zip so you can directly look into the XML.

9

@norman.neubert
Sorry, we still cannot reproduce your issue. By using the sample file and the following sample code for testing on the latest version v24.4, we can still obtain the correct results.

The sample code as follows:

Workbook wb = new Workbook(filePath + "LinkedFile.xlsx");
Cells cells = wb.Worksheets[0].Cells;
Console.WriteLine(cells["A1"].Formula);

The output:

='\\superstore\Public\Ref1\[Ref.xlsx]Tabelle1'!$A$1
10

Hey John, I found the difference between what you are testing and what I am testing:

I stored LinkedFile.xlsx on C:\Temp → Result: ='\\superstore\Public\Ref1\[Ref.xlsx]Tabelle1'!$B$5 → OK

I stored LinkedFile.xlsx on \\Superstore\Public and opened it like this:

oxlwbook = New Workbook("\\Superstore\Public\LinkedFile.xlsx")

Result: ='\\superstore\Public\Ref1\[Ref.xlsx]Tabelle1'!$B$5 → OK

I stored LinkedFile.xlsx on \\Superstore\Public and opened it like this:

oxlwbook = New Workbook("W:\LinkedFile.xlsx")

Result: ='W:\Ref1\[Ref.xlsx]Tabelle1'!$B$5 → NOT OK or at least not as expected.

So the solution is: Open the file that has external references via UNC path and not via driveletter.

Case closed :slight_smile: Thanks for your great support!

Kind regards
Norman

11

@norman.neubert,

It seems that your issue is resolved now. Please feel free to write back to us if you have any further queries or comments. We will be happy to assist you soon.