VBA To Pass Command Line Parameter

  • I found the following link that got me started http://www.vbforums.com/showthread.php?t=366559, so now I have my code for reading/analyzing comand line parameters. Here is an example of the parm I'm passing in

    Quote


    /e/C:\Program Files\AFP\bgst_before.txt§C:\Program Files\AFP\bgst_after.txt§C:\PROGRAM FILES\A_SHB_REXXCODE\excel_diff.txt§


    and here is the sub I have on workbook open


    The three files (separated by a §) are then processed in my VBA code. Everything works fine, until I exit the sub above.


    Suddenly, I'm seeing 3 messages (one for each file specified/opened ?) along the lines of

    Quote


    Can't find Files\AFP\bgst_before.txt§C:\Program.xls Check that the name is correctly spelt etc etc


    These messages are issued AFTER the "About to leave Workbook_open" message.


    If I run EXACTLY the same code from a function (cheating, and passing the string to the routine that actually does the processing, so I don't go through the cmdline code linked to above), everything works fine.


    My question is - based on the link and my code above, is there some code I need to "drop" memory or similar, or can anyone see anything obviously wrong in what I'm doing ?

  • Re: VBA To Pass Comand Line Parameter


    That's the problem Dave, I can't. I missed appending the actual sub that is run, which I'll include now. First the last few lines of the open workbook event

    Code
    1. Call create_diff_layout(cmdline)
    2. End If
    3. MsgBox "About to leave Workbook_open"
    4. End Sub


    and now the relevant parts of create_diff_layout


    I see the message box "About to leave workbook open", AFTER which I get the strange messages. Am I missing something, but I assume that once I've left the open workbook there's nothing left to F8 on, or ........ ???

  • Re: VBA To Pass Comand Line Parameter


    Quote

    That's the problem Dave, I can't.

    Why not?



    Quote

    and now the relevant parts of create_diff_layout

    Why ONLY some code when the whole Procedure is called? Also, if don't know the problem, how do you know which parts are relevant?

  • Re: VBA To Pass Command Line Parameter


    Okay - here's the whole create_diff_layout


    As to not being able to F8, I'll repeat part of the earlier append


    Quote


    I see the message box "About to leave workbook open", AFTER which I get the strange messages. Am I missing something, but I assume that once I've left the open workbook there's nothing left to F8 on, or ........ ???


    As a novice, I have no problem in F8'ing through my code, but once I leave the open workbook event, hasn't control passed back to Excel (and it is then the messages appear)

  • Re: VBA To Pass Command Line Parameter


    Why "assume" when you can test?


    Debugging your own code is the BEST way to find out why it's NOT working as expected.


    BTW, your "messages" look like run time errors to me.

  • Re: VBA To Pass Command Line Parameter


    Okay then - please enlighten me. What do I do when I get to the last line in the open workbook event to continue debugging.


    I reiterate - once I press F8 on the LAST line in the event, haven't I left MY code and am in the world of Excel.

  • Re: VBA To Pass Command Line Parameter


    I'm beginning to think we're talking at cross-purposes now. As appended above, here are the last few lines in the workbook event

    Code
    1. Call create_diff_layout(cmdline)
    2. End If
    3. MsgBox "About to leave Workbook_open"
    4. End Sub


    If I have a breakpoint on the msgbox line above, then I've already run through and finished the call to create_diff_layout. If I then press F8 from the msgbox line, I get to the end sub line. One more F8 and I'm out of the open workbook event. Now, surely there's nothing left to debug ?? That's when I see the strange messages.

  • Re: VBA To Pass Command Line Parameter


    Place your cursor at the start of the Workbook_Open Procedure and push F8 to step through BOTH Procedures.


    At least TRY and help yourself and stop assuming. YOU are the ONLY one who can debug your code, nobody else.

  • Re: VBA To Pass Command Line Parameter


    Okay - I'll try that. Next question (well, a follow-on). If I call Excel from a DOS command line, with the name of my workbook ast the first parm and my filesnames passed as /e/... parms, how do I ensure that the open workbook event breaks to let me debug the code ?


    Do you know - it suddenly occurred to me that I'd omitted to mention that that is the way I'm running.


    I'm starting from a DOS window and calling excel as in excel.exe workbook.xls /e/myparms


    My apologies.

  • Re: VBA To Pass Command Line Parameter


    Quote


    how do I ensure that the open workbook event breaks to let me debug the code ?


    the first line of code in the workbook open event should be

    Code
    1. 'variable declarations
    2. STOP


    The STOP command suspends execution like a breakpoint. Once the code breaks at the STOP point, press F8 to step through the rest of your code.


    HTH
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: VBA To Pass Command Line Parameter


    Honestly no... but if you force the break with STOP and then step through, then you should in theory have the same scenario and you should in theory generate the messages...

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: VBA To Pass Command Line Parameter


    Thanks again for the STOP tip Ger. Did as you suggested, and I get no message until AFTER I F8 on the last line in the open workbook event.


    That was my whole point to Dave - that I never see the message until I'm back in Excel's bowels. This was made me wonder whether the code I referred to in the first append (I'm guessing here) allocated some memory that should have been freed. Maybe Excel is still (?) trying to read/process the command line (or what it thinks is there). Like I say, just guessing.

  • Re: VBA To Pass Command Line Parameter


    I'm not sure. I'll refer back to my "memory solution". It seems like, once the VBA script has run to completion, that something's left "hanging around" and that something is being interpreted by Excel as a request to open a specified xls workbook.


    I now have a trivial bat file with the following content

    Code
    1. "c:\program files\microsoft office\office11\excel.exe" c:\excel\Compare_DB2_Unloads.xls /e/C:\Program Files\AFP\[email protected]:\Program Files\AFP\[email protected]:\excel\[email protected]


    and will test it at work tomorrow. Maybe it's a language or fixpack problem. It'll be interesting to see. At home I have English XP and Swedish Excel. At work I have English XP and Excel, so if it works here, that'll give a clear indication of where the problem might lie.


    Will keep you informed.

  • Re: VBA To Pass Command Line Parameter


    Okay - have tested it today at work. A colleague has Swedish Excel and there was no problem with that, so it seem to be a problem with the version/level of Excel I have at home, so I shan't bother to pursue this any further.


    Thanks to both of you for taking the time to try and help.