Is there an EXCEL spreadsheet link plugin?
-
Took a very quick look at the file.
Obviously there is a lot more data than most users (especially me) would be interested in, and some of only cursory curiosity. Then the hard core users could want and make use of those rarefied bits.
We will end up with perhaps 99% of it, but let's start with a subset of the 16 entity types, the one's I think would be of greater interest. Later the other entities can be incorporated by simply expanding on the basic framework of the exporter RB and the reporter spreadsheet, as requested by users.
If you can give me a list of the data elements available for each of the following 8 entities, even if of dubious use (for now) then I can mockup a spreadsheet with a relevant selection of those elements.
Model
Face
Group
Definition
Instance
Material
Layer
TextThe real key here is some unique common identifier so a relational DB can work.
I am going to assume the "REF" is that identifier. So if say, component ZZZ says its "parent" is XXX, then the REF of XXX should be the data element, not the name. The name is useful as well, but not necessary. The RDB function will replace the parent as XXX not its REF. Again, I'm assuming that ZZZ is nested within XXX.Also, any NUL data in an element field must contain a value of some kind, zero or "-" to avoid misplaced columns which will royally screw things up.
Replacing embedded "," with ";" is not a problem for me. However you can also substitute one common alternative extended ASCii code (128-254) for any embedded "," (ASCii 44) that I can search and replace with a "," in the RDB. It should be innocuous to an EXCEL CSV import.
After I see the list of data elements and understand what each really represents (with your tutelage), then we can see where some can be eliminated, or others combined.
At that point you can produce a raw data CSV exporter while I mockup the Spreadsheet.
We will also need 1 or more varied geometric SKP's to test with.
Once we are in sync, I can start defining the detailed spreadsheet functionality.
Then it can be expanded in stages to meet any demand. -
I've started a mockup to test some concepts for the reporter.
I just ran into a very frustrating limitation in EXCEL that LOTUS123 had no problem with. I've queried some expert XL'rs but no reply yet. The available help does not address this at all.
When XL imports a CSV file it opens a new window, rather than import into the open sheet at the time. That file import also puts me into the new window, where I then have to macro copy the CSV data over into the spreadsheet where I want it. Then go back to the CSV file window and close it, which will return me back to the spreadsheet.
I need to define the sheets by name (in a cell reference) to the Macro. There is a "range("name")" function that I use for selecting the files to import, BUT the window switching macro command "Windows("windowname").activate" will not accept it. It wants a hardcoded window name in the syntax, and that would blow me out of the water to give the reporter the flexibility to work with any SU model.
There is another way to do this using a CSV dataset in a TXT file. While that would put the data exactly where I want it, the user has to put up with a data formatting dialog for every CSV file. That is a non-starter.
-
You insert the CSV just the once as a 'linked object' so the XLS updates when the CSV changes... but the XLS formats and uses the CSV data as it wishes...
I think you can have the entire CSV load into an unused worksheet and extract data from that into other worksheets in the same XLS as desired...
I haven't had a chance to think any more about this but I will get back to you... -
Had not thought about linking. Never tried it, but tomorrow I will bone up on it.
The auto-update is intriguing.
thanks. -
I have done this before with a CSV linked into a XLS.
The SKP exporter keeps the CSV up to date and the XLS displays the latest CSV data on opening or can be updated when open [like an Xref].
Because you can't format a CSV you do all of your formating in the host XLS and manipulate cells etc as needed.
As I said you need at east two worksheets in the XLS.
One sheet containing the CSV 'insert' link and the other sheet reading data from it with formatting, formulas etc as needed. Obviously you could have several worksheets presenting different aspects of the CSV's data if desired... -
My concept is a workbook with several sheets, maybe 20+ with all 16 entities reported.
Several CSV data sheets each containing the raw CSV data and a slightly massaged version of that data used for the database. I am leaning towards 1 sheet per entity type, but will combine a few where practical.
A main cover sheet for user controls and macro cell references, plus user guide.
Several canned reports, 1 per sheet. So far, 5 reports.
and as many added user defined reports as wanted, 1 per sheet.The report sheets are divided in 2, the far right side is for the raw data extract from the CSV data sheets, specific to the report in sorted order.
The left side is for the formatted output report.In addition, my concept would open a new CSV import window, from which I transfer the data over to the CSV data sheets, then close that window. I have not yet considered the linking aspect you mentioned yesterday, so this may change.
I was going to make the sheet flexible enough to handle multiple models, 1 at a time, but this am I realized this won't be practical.
So there will be a master template, from which is spawned a copy for each SU model the user wants. The template would be user unalterable, but the copies will just protect critical parts of the sheet. I will publish the model sheet passwords for those who wish to be daring, but not the master template password. Only you and I will know that.
Until I get a solution to the windows switch macro, I am defining several other macro models to do specific housekeeping tasks.
-
Hey Tig; still with me on this
I have figured a very simple workaround to the window name/address problem.
So immediately, EXCEL throws another curve at me.
When I attempt to close the CSV input window, either with or without data in it, EXCEL sometimes asks if I want to save the sheet before closing. But the MACRO will not record my "NO" (dammit) entry. Not only that, it is not consistent in asking. I started searching any EXCEL help files, but gave up mid week and took a mental health day(s) off.
If this were still LOTUS 123, the data load macro would be long done and working right.
-
I've been a bit preoccupied but I am still here
Rule 1: you should never edit the CSV file.
Rather you must insert the virgin CSV file as a linked file into a separate XLS file.
Now you can FORMAT the data in the XLS as desired and save the XLS - but never EDIT the data from the CSV directly as this is your the link back into the SKP [eventually!].
Now if you edit the CSV file directly [I know I said NOT to, but here we will 'simulate' the SKP changing the CSV fro now!!!].
Now the XLS will change to suit the CSV changes...
If you want to be able to change the CSV file it's OK BUT only if yu them 'import' it back into the SKP!
It'll be destroyed next time the SKP exports the data to the CSV anyway
-
I had no intention of editing the CSV file, only import, copy over and close the import file, unchanged.
I need them pristine in case the files needed to be imported again. If any were edited then I would have to run the SU Extract script every time, and that may not be advisable in some circumstances, especially if the SU model is undergoing changes, but the user wants to develop reports using an older version of the model.
Any editing was done on the copy in the XL CSV data area, after the import CSV was closed.
Should we (I hope) be able to export to SU a changed CSV file, then I will deal with that later, probably by bringing it in again, make the edits and exporting the changed CSV to SU.
I am not yet sold on a linked CSV file. I have no experience with that, but It may not be a good idea to auto update the XL reporter, unless there is some user control. Like I said, I need to experiment with it, but I have that stupid "save file - Yes/No" dialog to contend with.
In Lotus 123 EVERY keystroke/mouse click could be recorded or simply coded for. In XL, most of the time you end up needing some extra code around simple stuff, like dialog box entries, or range name inclusion, and yet, some keystrokes, mouse clicks can't be coded around. Also, the documentation for XL Macros and VBA suck bigtime.
-
Im interested in this thread but sadly Im no script writer. I don't want to distract from what you are looking to achieve in this thread but it is related to something Im trying to achieve so have started a separate thread and wonder if you wouldn't mind taking a quick look to see if you have any suggestions . . .
Thanks
-
Hi Jgb, hi folks.
If you have SU pro, you may try File menu --> Generate report.
Just ideas.
Advertisement