Show Bid Request InstallShield-like Process to Run an Excel Macro Bid Request Id: 23737 Bookmark in my 'To Do' list Posted by: dbabbitt (4 ratings) (Software buyer rating 10) Non-action Ratio: Very Good - 10.00% Buyer Security Verifications: Good Approved on: Aug 5, 2002 1:52:21 PM EDT Bidding Closes: Oct 6, 2002 4:06:50 PM EDT Viewed (by coders): 581 times Deadline: 1/1/2003 TIME EXPIRED Phase: 100% of work completed and accepted. Coder has been paid. Max Accepted Bid: Bidding is closed Project Type: Small Business Project: $100 (USD) + Bidding Type: Open Auction Categories: Web, Windows, Other, Cold Fusion, Misc Enter chat room for this bid request (0 active users at Sep 29, 2003 3:30:06 AM EDT) Description: Having already created 4 comma delimited text files (.csv) and 1 Excel spreadsheet (.xls) using Cold Fusion, I need some way to simplify for the user the process of opening the spreadsheet, enabling macros, and running the macro which will take all the .csv files and make a four sheet workbook out of them. I don't want anything extra installed on the client, just the self-installing workbook. Certainly a queue could be set up so that the requests on the server are batch processed. I would prefer a process which can be called from the Cold Fusion server which will take the 5 files and bundle them into a executable file which will run the macro on the copy of MS Office which is accessible to the user (who right-clicked on this link and saved the .exe file to their hard drive), then delete any no-longer-neccessary files. This will include modifying the macro code as neccessary to import the files at paths relative to the .xls file in which the macro is running. Note: At present I use a method (unauthorized by MicroSoft) in which I open the spreadsheet in a copy of MS Office installed on the server via COM and run the macro that way. This method fails to scale in (not one but) two ways: in the first the Office "Server" trips over its own request, in the second, it takes too long to handle more than 200 rows of data at a time. The first scaling issue is actually a group of problems releasing hold on a file. Even though I use pointers to my COM object to handle multiple requests for it, (or maybe because of it,) I get situations where Office is trying to open the same file (usually the .xls file) twice, which aborts the whole process. And even though I try to gracefully close the file and destroy the pointers, this file is still left open by System and this forces a reboot of the entire machine in order to be able to open the file again. I am pretty sure this is the same user trying to open the file twice. The second scaling issue has to do with the mass amount of data I'm trying to feed the macro. At about 1000 rows of data, the HTML summary table file increases to more than 2 million lines of code, and the macro itself takes about 30 minutes to run. I have already fixed the HTML, but having the macro run so long on the server is unsatisfactory. Deliverables: Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. Complete copyrights to all work purchased. Platform: Cloud: Windows 2000 Server (NOT running MS Office 2000) and Cold Fusion Server 5.0. Possibly another dedicated machine running Windows 2000 Server with MS Office 2000 Local: Internet Explorer 5.X (or better) and MS Excel 98 (or better). Any plans for sharing from the client across a network are not going to work, as there is no network protocol besides TCP/IP. Must be 100% finished and received by buyer on: Jan 1, 2003 EDT Deadline legal notes: All times are expressed in the time zone of the site EDT (UT - 5). If the buyer omitted a time, then the deadline is 11:59:59 PM EDT on the indicated date. Additional Files: This bid request includes IMPORTANT additional attached files. Please download and read fully before bidding. Remember that contacting the other party outside of the site (by email, phone, etc.) on all business projects < $500 (before the buyer's money is escrowed) is a violation of both the software buyer and seller agreements. We monitor all site activity for such violations and can instantly expel transgressers on the spot, so we thank you in advance for your cooperation. If you notice a violation please help out the site and report it. Thanks for your help.   Bidding/Comments: All monetary amounts on the site are in United States dollars. Rent a Coder is a closed auction, so coders can only see their own bids and comments. Buyers can view every posting made on their bid requests. See all rejected bids (and all comments) Name   Bid Amount    Date   Coder Rating   This bid was accepted by the buyer! PaulMdx (5 ratings) in Loughborough, Leicestershire United Kingdom Bid id: 305,170   $500 (USD) Sep 10, 2002 12:37:59 PM EDT  10 (Excellent)     Hi, Interesting project.. Couple of questions -- I should mention I've not used Cold Fusion, so you have to bear with me in that respect. -- What format is the data in on the server? You mention the files are in CSV format? What I'm trying to say is - how exactly are the files layed out? Do you auto-generate the four files for each person, or are all the files in a directory with a certain naming convention? -- Do you need to package up the information on a request by request basis, or could a 'packaging run' be done every week? There's a couple of ideas I have for the best way to produce the 'package', depending on when they need to be created. -- How many users do you need to support? For the package itself, I would think following would work ok: Create a 'stub' program that puts together a single EXE file containing the Excel file and four CSV files. The EXE can unpack the files to the temp dir, then open Excel and insert the data (more on that later). I suspect the best way to delete the CSV files is for the EXE to keep a handle to the Excel window, and delete the files once it closes. This method has the advantage that you can let IE to run the app from the current location, rather than downloading it first. -- Regarding importing the data into Excel. Have you considered Automation? With MS Visual Studio dev tools you can hook into MS Office family products and remote control them. I have to admit, I'd almost consider ditching Excel, depending on what features you need in Excel. This isn't a particularly small project, especially if you want it to work smoothly and well. I would explore all the options and find the one that is going to be most successful, not necessarily the quickest to put together. Hopefully with a bit more info I'll be able to come up with something that's more applicable to your problem. One thing I would say is this - 1000 rows is nothing to a VC++ app! Hope to hear from you soon. Paul.           There are 7 private replies to/from PaulMdx. Click here to view them.