Importing/Exporting Command Maps With Excel

Discuss topics related to the plugins and uses for MLServer beta
MurrayW
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1198
Joined: Sun Mar 07, 2004 1:05 pm
Location: Austin, TX
Contact:

Importing/Exporting Command Maps With Excel

Postby MurrayW » Wed May 03, 2006 1:23 pm

I started a new thread so the latest file could always be contained in the first post. This thread is really just a continuation of this previous thread:
http://www.cinemaronlineforums.com/forum/viewtopic.php?t=5961&highlight=

I included some instructions in the excel workbook on how to use this excel tool for managing your command maps. There are two files included. The one named Command Map.xls is the one you should use for your own commands. There is one sheet with a few example commands (modify or delete these) and 4 other blank ones. The other workbook named Command Map Examples.xls has 3 sheets with example from justmikie, gregoryx and me -- Worksheets: justmike, MurrayW and gregoryx.

These 3 worksheets are included as examples of command maps that other users are using. Some of these maps are quite complex and long. For beginners, it is recommended that you start with simple, shorter command maps before moving up to some of the complex commands contained in these example worksheets. Since there are over 2000 command maps in the example workbook, it may take a few seconds to update when changes are made.

Let me know if there are any bugs or if the instructions need to be modified to make it clearer how to use this tool.

Hope this helps!

Murray

06-14-2006 Update
1. Fixed a problem with the import/export process that was causing macros to error out when trying to determine the column that contains the command map and command name formulas if no formulas existed in the sheet.
2. Broke the attached files into 2 separate downloads -- one for the command map workbook and the other for command map examples. Both files have been updated, so you should re-download the new files.

12-14-2006 Update
1. Removed the dynamic formulas from the Full Map Name and Full Command Line columns. These formulas led to extremely long calculation times and high CPU utilization. Replaced these formulas with a "Join Cmd Parts" button in cell A1. This button runs a macro that concatenates the parts of the commands and name. Since the formula is gone, the full names and full commands are no longer built on the fly as the parts are entered. You must manually click the J"oin Cmd Parts" macro button to concatenate the pieces together.
2. Fixed a bug with the exporting commands to MLServer process.
3. Updated instruction sheet.

03-09-2007 Update
1. Uploaded the 12-14-2006 files again since they had become corrupt
You do not have the required permissions to view the files attached to this post.
Last edited by MurrayW on Wed Jul 11, 2007 12:31 pm, edited 7 times in total.

THARTIGH
Expert
Expert
Posts: 878
Joined: Fri Apr 30, 2004 12:30 pm
Location: MI
Contact:

Postby THARTIGH » Wed Jun 14, 2006 12:19 am

Murray, I finally tried the new files, but when I hit the export it locks up excel to 100cpu. If I export a couple of commands it goes to 100 then goes to back to normal, but the maps are not added.

If I try to import It says the commands are added, but when I say ok it pops of the caution box saying the existing commands will be deleted. When I say yes, I get a:

run-time error '3704':
Operation is not allowed when the object is closed.

Thanks for the newer tool
Please help!
Todd

MurrayW
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1198
Joined: Sun Mar 07, 2004 1:05 pm
Location: Austin, TX
Contact:

Postby MurrayW » Wed Jun 14, 2006 12:49 am

THARTIGH wrote:Murray, I finally tried the new files, but when I hit the export it locks up excel to 100cpu. If I export a couple of commands it goes to 100 then goes to back to normal, but the maps are not added.

If I try to import It says the commands are added, but when I say ok it pops of the caution box saying the existing commands will be deleted. When I say yes, I get a:

run-time error '3704':
Operation is not allowed when the object is closed.

Thanks for the newer tool
Please help!
Todd

Hey Todd,

Can you send me your excel file with your commands so I can take a look at it to try to figure it out. What version of excel are you running, what OS and what version of msjet40.dll is in your C:\Windows32\System folder?

I have tested it on Excel 2000, Excel XP and Excel 2003, I have Windows XP Pro SP 2 and version 4.0.8618.0 for msjet40.dll.

My email address is murray period williams @ earthlink period net.

Murray

THARTIGH
Expert
Expert
Posts: 878
Joined: Fri Apr 30, 2004 12:30 pm
Location: MI
Contact:

Postby THARTIGH » Wed Jun 14, 2006 1:18 am

I have not added anything to the file yet. I figured before I did I would make sure it imports and exports. So it is exactly as it is downloaded.

I am using Excel 2000, Windows xp home, and the version of the msjet40.dll is 4.0.8618.0.

Thanks for looking into this!
Todd

MurrayW
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1198
Joined: Sun Mar 07, 2004 1:05 pm
Location: Austin, TX
Contact:

Postby MurrayW » Wed Jun 14, 2006 12:34 pm

THARTIGH wrote:I have not added anything to the file yet. I figured before I did I would make sure it imports and exports. So it is exactly as it is downloaded.

I am using Excel 2000, Windows xp home, and the version of the msjet40.dll is 4.0.8618.0.

Thanks for looking into this!
Todd

Todd, I was able to duplicate your error and I found the offending portion of the code. I didn't have time to thoroughly test every possible iteration, but I was able to successfully import from access to excel and export from excel to access both with deleting all maps and keeping current ones and only adding new or modified ones.

As far as the CPU utilization going to 100%, that is "normal" as there are a lot of calculations that excel has to do in the formulas contained in the sheets. It also seems to make a difference (slows things down) if other excel workbooks (with a lot of formulas) are open at the same time. Your CPU speed, installed memory and version of excel will also make a difference. It is probably a good idea to clear out any unneeded command maps from each of the cmd map sheets if you don't need them.

Let me know if the revised workbook works now.

Murray

THARTIGH
Expert
Expert
Posts: 878
Joined: Fri Apr 30, 2004 12:30 pm
Location: MI
Contact:

Postby THARTIGH » Wed Jun 14, 2006 2:21 pm

Thank you almighty god of excel.
:lol:


Worked like a charm!

Todd :D

MurrayW
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1198
Joined: Sun Mar 07, 2004 1:05 pm
Location: Austin, TX
Contact:

Postby MurrayW » Wed Jun 14, 2006 3:35 pm

THARTIGH wrote:Thank you almighty god of excel.
:lol:


Worked like a charm!

Todd :D
I think that I am a mere mortal. Would a god of excel create a flawed macro?

Glad it worked.

Murray

tpate
Member
Member
Posts: 93
Joined: Fri Sep 30, 2005 9:11 pm
Location: Ann Arbor, MI
Contact:

Postby tpate » Fri Nov 10, 2006 6:06 pm

Starting to use... Great Idea!! :D

Couple questions... could try but might be easier to ask rather than screw up the excel sheet.

1. OK to insert rows and copy formulas from above? (Noticed in the instructions warning against copying formulas to add at bottom). Would be much easier than cutting and pasting large list of rows to add another row in the middle.

2. "RunCmdMap"... I see in the examples. Is this cmd still necessary? still work?

Thanks for GREAT TOOL!!
Tom

MurrayW
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1198
Joined: Sun Mar 07, 2004 1:05 pm
Location: Austin, TX
Contact:

Postby MurrayW » Fri Nov 10, 2006 10:40 pm

tpate wrote:Starting to use... Great Idea!! :D

Couple questions... could try but might be easier to ask rather than screw up the excel sheet.

1. OK to insert rows and copy formulas from above? (Noticed in the instructions warning against copying formulas to add at bottom). Would be much easier than cutting and pasting large list of rows to add another row in the middle.

2. "RunCmdMap"... I see in the examples. Is this cmd still necessary? still work?

Thanks for GREAT TOOL!!

1. It has been a while since I looked at this, but as far as I remember, inserting rows is OK, but make sure you copy all the formulas (there may be some in hidden columns and cells that look blank but actually do contain formulas).

What I would advise doing if you want to insert rows, would be to insert them, then copy an entire ROW of one of the original rows, then paste that into the new inserted rows. That way you will be sure to get everything that is needed to do the calculations.

2. RunCmdMap still works with MLServer 3, but I don't think it is necessary. I haven't gotten around to taking them out of my command maps yet so I can't personally vouch that they are not needed, but from what I have read you don't need this with MLServer 3.

Good luck and have fun with command maps!

Murray

kirkfrey
Expert
Expert
Posts: 759
Joined: Wed Jan 12, 2005 10:55 am
Contact:

Postby kirkfrey » Fri Mar 09, 2007 11:13 am

The attached zip's seem to be corrupt, can anyone post them again please?
Kirk

Just trying to figure it out...

MurrayW
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1198
Joined: Sun Mar 07, 2004 1:05 pm
Location: Austin, TX
Contact:

Postby MurrayW » Fri Mar 09, 2007 1:11 pm

kirkfrey wrote:The attached zip's seem to be corrupt, can anyone post them again please?
I re-uploaded them but am still getting the same error. I went to another completely unrelated post that had an attached zip file and got the same error trying to unzip it. I think it is a problem with the forum software. If you want, I can email you those files if you need them before cinemar can fix the download problems.

Murray

Maxfli
Newbie
Newbie
Posts: 6
Joined: Sat May 13, 2006 12:41 pm
Contact:

Command File

Postby Maxfli » Wed Mar 21, 2007 12:38 pm

I am still getting a 'zip file is either corrupted or invalid' message when I try to open the zip file.

Regards,
Max

MurrayW
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1198
Joined: Sun Mar 07, 2004 1:05 pm
Location: Austin, TX
Contact:

Re: Command File

Postby MurrayW » Wed Mar 21, 2007 1:50 pm

Maxfli wrote:I am still getting a 'zip file is either corrupted or invalid' message when I try to open the zip file.

Regards,
Max
Apparently there is still some problem with the message board software. PM me with your email address and I will email them to you if you would like.
Murray
Edit: email sent

User avatar
ronsatter
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1304
Joined: Mon Dec 04, 2006 1:40 am
Location: San Leandro, CA
Contact:

Is This Still A Valid Project?

Postby ronsatter » Sun Mar 29, 2009 11:21 pm

Hi Murray,

Just stumbled on this thread. Eager to try. Is the download zip fixed as of this date?

Ron
If it ain't broke ... don't fix it!

MurrayW
Is there life beyond Cinemar?
Is there life beyond Cinemar?
Posts: 1198
Joined: Sun Mar 07, 2004 1:05 pm
Location: Austin, TX
Contact:

Re: Is This Still A Valid Project?

Postby MurrayW » Mon Mar 30, 2009 10:35 am

ronsatter wrote:Hi Murray,

Just stumbled on this thread. Eager to try. Is the download zip fixed as of this date?

Ron
I believe it is. I haven't tried it for a while. If I remember correctly, the problem with the download before is that you would not get any files. If you get excel files from the zip file, it is probably OK. Give it a try and let me know if it works for you.

Murray


Return to “MLServer Questions & Answers”

Who is online

Users browsing this forum: No registered users and 1 guest