Is there an EXCEL spreadsheet link plugin?
-
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