Add rows and/or columns to embedded excel sheets

I am using the following;

Microstation V8i

Excel 2010

Windows 7

 

Issue:

I have linked and embedded excel workbooks into a 2D sheet in Microstation. I am wondering if it is possible to change the number of rows or columns after it has been placed or if I will have to detach and re-attach the link every time?

For example, I currently have 19 rows, I want to add in another row to have an even 20, is that possible? Or, I have 20 rows and I want to remove one so only 19 rows show up in Microstation, is that possible?

Parents
  • Taylor2357

    in MicroStation select Edit>DDE Links... in the Dialog box, select the entry you want to change, then poke Edit Link... this should show Program: Topic: and Item... the Item shows the Row and Column Range, which you can edit... the the Column and Row are swapped and Columns use numbers instead of letters, so range A1:A10 will show as R1C1:R10C1... if you modify it to R1C1:R11C1 then hit OK it will update the link to display the extra row.

    Brent

    Answer Verified By: Taylor2357 

  • Brent

    Taylor2357 mentioned that he had Linked and Embedded. Your answer dealt with the Embedded option.

    If the Excel Workbook is Linked, the command is:

    Edit> Links
    You'll see the Links window (long paths tend to overlap text making it hard to see the Link because the window cannot be resized (another MS bug) )
    Select the Link you wish to fix and Pick "Change Source"
    If you have the latest version of MS, you'll see an "Item Name:" at bottom of "Change Source" dialogue.
    If this is blank, you can enter the area you wish to reference thus:
    SHEET!R1C1:R20C10 (Replace SHEET with the Worksheet name and R=Row Number, C = Column number; Starting:End)
    Click Open
    Wait for the DDE engine to finish (can take 20-30 seconds at times)
    In the Links dialogue, you now select the Link again and press "Update Now"
    Warning: Nothing will display until you Close the dialogue (typical MS display screen issues)

    The resultant DDE Link will resize itself to fit in the original area, so if you have added or removed Rows and/or Columns, you will now need to manually repair the scale.
    Warning: Do not be fooled by the Selection tool reporting the scale remains as 1.000 AFTER add/remove Rows/and/or Columns, this again is typical MS element reporting behaviour that is faulty.
    i.e. If you were to reselect the Excel Area and PASTE Special, By Size, scale =1 as Lory's post below, then the size would differ to result.
    NB. I do not know how to correct this other than Lory's methodology, so in hindsight, I'd recommend this process over the above, unless maintaining correct scale is not an issue ( then PASTE using corners and drag).
    I personally prefer to maintain size correlation between Excel and Microstation, and suffer the bugs. I recall it took many years of complaints (plus tips and endless hours explaining to clueless techs) to AutoDesk to finally fix all the issues with OLE, now Microstation needs to do the same (and not blame Microsoft).

    However, with a rider, the main reason I use LINKED spreadsheets is so that Engineers have access to design data in the Drawing without needing a copy of Microstation.

    This subject really merits a White paper in its own right though.

    Greg Smith

    Microstation 10.17.01.058

    Opinions expressed are my own and not necessarily those of my employer

  • Unknown said:



    However, with a rider, the main reason I use LINKED spreadsheets is so that Engineers have access to design data in the Drawing without needing a copy of Microstation.

    Why not have Bentley View installed across your engineering group then engineers can read  print  and  measure any cad file they want...

    Its free and can be rolled out with a distribution install by IT very easily .... can even have your workspace custom settings for fonts, and level standards etc...

    Lorys

    Started msnt work 1990 - Retired  Nov 2022 ( oh boy am I old )

    But was long time user V8iss10 (8.11.09.919) dabbler CE  update 16 (10.16.00.80) 

    MicroStation user since 1990 Melbourne Australia.
    click link to PM me 

  • Sorry, probably should have said read/write access. They can put all their design data on a spreadsheet, then have a "publishing page" which is linked internally in the Spreadsheet and subsequently to the Drawing.
    Keeps them out of my hair while I get on with the presentation side of things.

    Greg Smith

    Microstation 10.17.01.058

    Opinions expressed are my own and not necessarily those of my employer

  • but if they delete or rename a spreadsheet  mstn  cant use the  update link and your endanger of loosing the data arent you..?

    Thats why I prefer the  embedded method as they cant delete or edit the excel I want to use in my DGN ( without using mstn full version)

    and I publish the dgn instead also when I send the dgn to external client it has the excel inside it ..

    But there is no wrong or write method or even better  or worse  all the  methods have  their advantages and disadvantages..

    And unfortunately the  auto update menu  of excel in mstn  without  manually  forcing the  links does not and  has  never  has worked its a mistake in the menu or a glitch..

    So this led me to adopt the embedded methodology as well as I have had 3rd party move/ rename / delete the  linked excel file so I've been burned a few times...

    Lorys

    Started msnt work 1990 - Retired  Nov 2022 ( oh boy am I old )

    But was long time user V8iss10 (8.11.09.919) dabbler CE  update 16 (10.16.00.80) 

    MicroStation user since 1990 Melbourne Australia.
    click link to PM me 

Reply
  • but if they delete or rename a spreadsheet  mstn  cant use the  update link and your endanger of loosing the data arent you..?

    Thats why I prefer the  embedded method as they cant delete or edit the excel I want to use in my DGN ( without using mstn full version)

    and I publish the dgn instead also when I send the dgn to external client it has the excel inside it ..

    But there is no wrong or write method or even better  or worse  all the  methods have  their advantages and disadvantages..

    And unfortunately the  auto update menu  of excel in mstn  without  manually  forcing the  links does not and  has  never  has worked its a mistake in the menu or a glitch..

    So this led me to adopt the embedded methodology as well as I have had 3rd party move/ rename / delete the  linked excel file so I've been burned a few times...

    Lorys

    Started msnt work 1990 - Retired  Nov 2022 ( oh boy am I old )

    But was long time user V8iss10 (8.11.09.919) dabbler CE  update 16 (10.16.00.80) 

    MicroStation user since 1990 Melbourne Australia.
    click link to PM me 

Children
No Data