changing the location of external spread sheet in unigraphics NX 7.5 2

[defaultrush]

hi, i have a large assembly with many parts and sub assemblies, the dimensions in the part and assemblies are derived from expressions , many of which are linked to a external spreadsheet ,the problem is if the entire folder is transferred to another computer ,the default location of the spreadsheet in the expression remains the same,that is c:\users\"username"\desktop\"folder name"\"sub-folder name"\"spreadsheet name".xlsx .Is there a option in unigraphics NX7.5 to change the location of the external spreadsheet from which ug reads the value from .....it would be impossible to edit the location of the spreadsheet in expressions manually .

 

[JohnRBaker]

Well, if this is a one-time only issue, your easiest approach would be to go into the expression system and at the top of the dialog you will find an icon labeled 'Export Expressions to File'. Select that icon and enter a file name and hit OK. Now open this file using a regular text editor and then using the normal 'replace text' utility replacing all occurances of the text string showing the old file path with the new one. Now save this edited filed and then go back in NX open the Expression dialog again, find the icon labeled 'Import Expressions from File' (it will be next to icon you selected in the steps above), select it, browse to where the edited text file is, select it and hit OK. Now hit OK to accept and close the Expression dialog and you should be good to go.

Now if this is something that is going to be a common issue with you, then perhaps a little preplanning would be worth your time and effort.

The easiest of these approaches would be to create a String Expression equal to the path to the folder where the spreadsheet is located and then use this variable in all of your expressions which referencs the spreadsheet, as shown below:

Now all you have to do is edit a single expression, 'sheet', to change the paths to all of the other expressions.

Now if you want to get really elegant, and you're doing this all of the time and you's like the files to always be correct no matter where you moved the part files to, try this scenario:

Create a folder somewhere where you will place ALL of the spreadsheets which will be referenced by any of your part models and let's say the path to this folder is 'D:\User_files\Spreadsheets'. Now create a unique Environment Variable in your system or user profile similar to (be sure to include that last '\'):

NX_SPREADSHEETS=D:\User_files\Spreadsheets\

Now whenever you create a Part file where you're going to be using one of the spreadsheets in this common folder, just create an expressions which references this Environment Variable (you could pre-define this expression in you modeling templated is you wish) which will now look like:

Now no matter where the part files are located as long as the spreadsheets remain in that same common location, you should be good to go.

John R. Baker, P.E.
Product 'Evangelist'
Product Engineering Software
Siemens PLM Software Inc.
Industry Sector
Cypress, CA
Siemens PLM:
UG/NX Museum:

To an Engineer, the glass is twice as big as it needs to be.

 

[cowski]

it would be impossible to edit the location of the spreadsheet in expressions manually .

I think the process is more tedious than impossible. Sounds like a good time to use a journal.

The code below will gather up the file names of the spreadsheets referenced in your file and prompt for replacement files (cell/range references within the file are left as-is). The code could even be modified to implement John's excellent suggestion. I'll leave that as an exercise for the reader.

[COLOR=green]' NXJournaling.com[/color]
[COLOR=green]' June 11, 2012[/color]
[COLOR=green]' NX 7.5.0.32[/color]
[COLOR=green]' find expressions that reference an external excel file, prompt for replacement file[/color]

[COLOR=blue]Option[/color] [COLOR=blue]Strict[/color] [COLOR=blue]Off[/color]  
[COLOR=blue]Imports[/color] System  
[COLOR=blue]Imports[/color] NXOpen  
[COLOR=blue]Imports[/color] System.Text.RegularExpressions  
[COLOR=blue]Imports[/color] System.Collections.Generic  
[COLOR=blue]Imports[/color] System.IO  
[COLOR=blue]Imports[/color] System.Windows.Forms  

[COLOR=blue]Module[/color] Module1  

    [COLOR=blue]Sub[/color] Main()  

        [COLOR=blue]Dim[/color] theSession [COLOR=blue]As[/color] Session [COLOR=blue]=[/color] Session.GetSession()  
        [COLOR=blue]Dim[/color] workPart [COLOR=blue]As[/color] Part [COLOR=blue]=[/color] theSession.Parts.Work  
        [COLOR=blue]Dim[/color] displayPart [COLOR=blue]As[/color] Part [COLOR=blue]=[/color] theSession.Parts.Display  

        [COLOR=blue]Dim[/color] lw [COLOR=blue]As[/color] ListingWindow [COLOR=blue]=[/color] theSession.ListingWindow  
        [COLOR=blue]Dim[/color] i [COLOR=blue]As[/color] [COLOR=blue]Integer[/color] [COLOR=blue]=[/color] 0  

        [COLOR=blue]Dim[/color] re [COLOR=blue]As[/color] [COLOR=blue]New[/color] Regex("(ug_excel_read|ug_cell_read|ug_cell_hlookup|ug_cell_vlookup)\s?\(\s?\""(.+\.xls(x|m)?)")  
        [COLOR=blue]Dim[/color] excelFile [COLOR=blue]As[/color] [COLOR=blue]String[/color]  
        [COLOR=blue]Dim[/color] newExcelFile [COLOR=blue]As[/color] [COLOR=blue]String[/color]  
        [COLOR=blue]Dim[/color] originalExcelFiles [COLOR=blue]As[/color] [COLOR=blue]New[/color] List(Of [COLOR=blue]String[/color])  
        [COLOR=blue]Dim[/color] myExcelFiles [COLOR=blue]As[/color] [COLOR=blue]New[/color] Dictionary(Of String, [COLOR=blue]String[/color])  

        [COLOR=blue]Dim[/color] myExpressions [COLOR=blue]As[/color] Expression()  
        [COLOR=blue]Dim[/color] myExcelExpressions [COLOR=blue]As[/color] [COLOR=blue]New[/color] Dictionary(Of Expression, [COLOR=blue]String[/color])  

        myExpressions [COLOR=blue]=[/color] workPart.Expressions.ToArray()  

        lw.Open()  
        [COLOR=blue]For[/color] [COLOR=blue]Each[/color] myExp [COLOR=blue]As[/color] Expression [COLOR=blue]In[/color] myExpressions  
            [COLOR=blue]If[/color] re.IsMatch(myExp.RightHandSide) [COLOR=blue]Then[/color]  
                excelFile [COLOR=blue]=[/color] re.Match(myExp.RightHandSide, 0).Groups(2).ToString  
                myExcelExpressions.Add(myExp, excelFile)  
                [COLOR=blue]If[/color] [COLOR=blue]Not[/color] originalExcelFiles.Contains(excelFile) [COLOR=blue]Then[/color]  
                    originalExcelFiles.Add(excelFile)  
                End [COLOR=blue]If[/color]  
                i [COLOR=blue]+=[/color] 1  
            End [COLOR=blue]If[/color]  
        [COLOR=blue]Next[/color]  

        [COLOR=blue]For[/color] [COLOR=blue]Each[/color] Str [COLOR=blue]As[/color] [COLOR=blue]String[/color] [COLOR=blue]In[/color] originalExcelFiles  
            newExcelFile [COLOR=blue]=[/color] ReplacementFile(Str)  
            [COLOR=blue]If[/color] newExcelFile <> "" [COLOR=blue]Then[/color]  
                myExcelFiles.Add(Str, newExcelFile)  
            [COLOR=blue]Else[/color]  
                MsgBox("Cancel pressed, journal exiting.", MsgBoxStyle.OkOnly, "Journal canceled")  
            End [COLOR=blue]If[/color]  
        [COLOR=blue]Next[/color]  

        [COLOR=blue]Dim[/color] pair [COLOR=blue]As[/color] KeyValuePair(Of Expression, [COLOR=blue]String[/color])  
        [COLOR=blue]For[/color] [COLOR=blue]Each[/color] pair [COLOR=blue]In[/color] myExcelExpressions  
            lw.WriteLine("Old: " [COLOR=blue]&[/color] pair.Key.Name [COLOR=blue]&[/color] " [COLOR=blue]=[/color] " [COLOR=blue]&[/color] pair.Key.RightHandSide)  
            lw.WriteLine("New: " [COLOR=blue]&[/color] pair.Key.Name [COLOR=blue]&[/color] " [COLOR=blue]=[/color] " [COLOR=blue]&[/color] Replace(pair.Key.RightHandSide, pair.Value, myExcelFiles(pair.Value)))  
            lw.WriteLine("")  
            pair.Key.RightHandSide [COLOR=blue]=[/color] Replace(pair.Key.RightHandSide, pair.Value, myExcelFiles(pair.Value))  
        [COLOR=blue]Next[/color]  

        [COLOR=blue]If[/color] i [COLOR=blue]=[/color] 0 [COLOR=blue]Then[/color]  
            lw.WriteLine("No expression names matched the specified regular expression")  
        End [COLOR=blue]If[/color]  

        lw.WriteLine("Expression processing finished.")  

        lw.Close()  

    End [COLOR=blue]Sub[/color]  

    [COLOR=blue]Function[/color] ReplacementFile(ByVal oldFile [COLOR=blue]As[/color] [COLOR=blue]String[/color]) [COLOR=blue]As[/color] [COLOR=blue]String[/color]  

        [COLOR=blue]Dim[/color] fdlg [COLOR=blue]As[/color] OpenFileDialog [COLOR=blue]=[/color] [COLOR=blue]New[/color] OpenFileDialog()  
        fdlg.Title [COLOR=blue]=[/color] "Select replacement for: " [COLOR=blue]&[/color] oldFile  
        fdlg.InitialDirectory [COLOR=blue]=[/color] "C:\"  
        fdlg.Filter [COLOR=blue]=[/color] "Excel Files(*.XLS;*.XLSX;*.XLSM)|*.XLS;*.XLSX;*.XLSM"  
        fdlg.FilterIndex [COLOR=blue]=[/color] 2  
        fdlg.RestoreDirectory [COLOR=blue]=[/color] [COLOR=blue]True[/color]  
        [COLOR=blue]If[/color] fdlg.ShowDialog() [COLOR=blue]=[/color] DialogResult.OK [COLOR=blue]Then[/color]  
            [COLOR=blue]Return[/color] fdlg.FileName  
        [COLOR=blue]Else[/color]  
            [COLOR=blue]Return[/color] ""  
        End [COLOR=blue]If[/color]  

    End [COLOR=blue]Function[/color]  

    [COLOR=blue]Public[/color] [COLOR=blue]Function[/color] GetUnloadOption(ByVal dummy [COLOR=blue]As[/color] [COLOR=blue]String[/color]) [COLOR=blue]As[/color] [COLOR=blue]Integer[/color]  

 [COLOR=green]'Unloads the image when the NX session terminates[/color]
        GetUnloadOption [COLOR=blue]=[/color] NXOpen.Session.LibraryUnloadOption.AtTermination  

    End [COLOR=blue]Function[/color]  

End [COLOR=blue]Module[/color]

www.nxjournaling.com

 

[cowski]

Sorry, I caught a typo after posting.

The section of code:

[COLOR=blue]Else[/color]  
    MsgBox("Cancel pressed, journal exiting.", MsgBoxStyle.OkOnly, "Journal canceled")  
End [COLOR=blue]If[/color]

Should read:

[COLOR=blue]Else[/color]  
    MsgBox("Cancel pressed, journal exiting.", MsgBoxStyle.OkOnly, "Journal canceled")  
    [highlight][COLOR=blue]Exit[/color] [COLOR=blue]Sub[/color][/highlight]
End [COLOR=blue]If[/color]

If you don't add that line, the journal will keep going after you press cancel (if you should choose to do so) and may run into problems as a result.

www.nxjournaling.com

 

[defaultrush]

thank you cowski.......works as intended.