Stupid Microsoft Tricks
Dec. 19th, 2007 09:54 am![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
awfulhorridI am working on a project to convert the prior programmer's horrible Excel "database" spaghetti code application into something that can actually be updated and maintained. Of course the end users, being accountants, want both the results and the interface to remain in an Excel workbook. Yeah, yeah, I can do that ...
Warning: Geeky programming stuff ahead!
Easy enough, of course. I have VBA code execute in MS Excel that copies the data blocks into nice clean worksheets for import then launch MS Access to do the actual work. There are several different workbooks that this needs to work with and while the structure will work for all of them the same, some of the specific manipulations don't need to be performed for all of them. I need to be able to use command-line options to pass a parameter flag to Access so I can identify the launching workbook from within the application.
The obvious way to do this is with an API call or a shell process. With a shell, I can spawn an application and it's CLI options, but of course I can't specify a file. So then I try an API function "shellexecute" based off of the shell32.dll. Hey great, I can open an application by passing it a document name. This is exactly what I'm looking for ... except that according to MS documentation (and experiments that prove it), you can't pass run time parameters with it.
Augh!
So ... I could building a short cut with the Target value being the database files plus a CLI parameter. Gee, that used to work just fine. I've used it on a number of end-user applications to control things at run time. It launches successfully, but testing shows that the parameter is not passing it's value.
Again, Augh!
I was at a loss. This should be an easy task; I've created similar processes before, why was this one being so difficult? Then as I was describing this to bookwurm last night, I suddenly had an idea ... it was a really stupidly simple idea, but considering I'm working with Microsoft products, it just might work.
Use a DOS batch file.
The CLI options are designed to be executed from a DOS shell. Gee, let's turn the clock back a few years and see if it works. Which, of course, it did. All these fancy API calls and trying to do things elegantly and the solution is a frickin' BAT file????
*sigh*
Just to appease my sense of geek dignity, I've made the BAT file dynamic. It's created at run time and the single command line it contains varies depending on factors set from within the workbook. (OK, this is also to eliminate the possibility that MS Access is installed to a slightly different directory on the user's machine, but at least it makes me feel like I'm actually programming in 2007(8) and not 1988!)
... and then I shot 'em.
Warning: Geeky programming stuff ahead!
Easy enough, of course. I have VBA code execute in MS Excel that copies the data blocks into nice clean worksheets for import then launch MS Access to do the actual work. There are several different workbooks that this needs to work with and while the structure will work for all of them the same, some of the specific manipulations don't need to be performed for all of them. I need to be able to use command-line options to pass a parameter flag to Access so I can identify the launching workbook from within the application.
The obvious way to do this is with an API call or a shell process. With a shell, I can spawn an application and it's CLI options, but of course I can't specify a file. So then I try an API function "shellexecute" based off of the shell32.dll. Hey great, I can open an application by passing it a document name. This is exactly what I'm looking for ... except that according to MS documentation (and experiments that prove it), you can't pass run time parameters with it.
Augh!
So ... I could building a short cut with the Target value being the database files plus a CLI parameter. Gee, that used to work just fine. I've used it on a number of end-user applications to control things at run time. It launches successfully, but testing shows that the parameter is not passing it's value.
Again, Augh!
I was at a loss. This should be an easy task; I've created similar processes before, why was this one being so difficult? Then as I was describing this to bookwurm last night, I suddenly had an idea ... it was a really stupidly simple idea, but considering I'm working with Microsoft products, it just might work.
Use a DOS batch file.
The CLI options are designed to be executed from a DOS shell. Gee, let's turn the clock back a few years and see if it works. Which, of course, it did. All these fancy API calls and trying to do things elegantly and the solution is a frickin' BAT file????
*sigh*
Just to appease my sense of geek dignity, I've made the BAT file dynamic. It's created at run time and the single command line it contains varies depending on factors set from within the workbook. (OK, this is also to eliminate the possibility that MS Access is installed to a slightly different directory on the user's machine, but at least it makes me feel like I'm actually programming in 2007(8) and not 1988!)
... and then I shot 'em.
