Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    California, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    XL 2K macro not working in OfcXP (XL XP)

    Noob (to the lounge) here, hoping for some help with a macro. I moved computers from one running Win98/Office 2K/Visual Studio 6 to another one running XP and Office XP (no Visual Studio). The following macro isn't working on several levels. See each line for notes on what it does and doesn't do on the new machine:

    ActiveWorkbook.Save <==Saves the workbook. This still works; so far so good.
    Application.ActivePrinter = "Rich's Printer on LPT:PS8B1E-1" <==Declares a new printer, not the default. This bonks, even though the printer is valid (the LPT port is simply for a network print server). Msg is Run-time error '1004'/Method 'ActivePrinter' of object '_Application' failed
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "Rich's Printer on LPT:PS8B1E-1", Collate:=True
    <==Theoretically, this should print to the other printer. What happens is that I have to comment out the above line declaring the active printer, and the darn thing prints out on my printer.
    ActiveWorkbook.PublishObjects.Add(xlSourcePrintAre a, _
    "F:ReportsMTDMar2004.htm", "2004", "", xlHtmlStatic, "MTDMar2004_17288", _
    "March Month-to-date").Publish (True)
    <==This updates the spreadsheet on a webpage. This works.
    ChDir "F:Reports" <==Don't know why this has to be here, but this is the network share the file resides on. No problems here.
    ActiveWorkbook.SendMail Recipients:=Array("user1", "user2", "user3", _
    "user4", "user5", "user6", "user7"), Subject:="Today's Month-to-date Report"
    <==Here, obviously, I'm trying to send mail to an array of recipients via Outlook 2002. I've blanked out the names, suffice it to say they're in my address book. What happens here is that sometimes a window pops up stating 'Outlook is trying to send a message on your behalf. Do you want to allow this?' There's a timer bar that doesn't let you click OK until about 10 seconds have passed. And the kicker is that sometimes the window doesn't even pop up, although I can see an item in the taskbar with the XL logo but the text "Microsoft Outlook". When the window doesn't show up I have to close the taskbar item and send the e-mail manually. Obviously, this is a security thing, but since I do this every fricking day I'd like this turned off.
    ActiveWorkbook.Close SaveChanges:=True <==Should save the file, although it never gets this far. I assume this would work.
    Application.Quit <==Quits the application. Pet peeve, but this hasn't ever worked, since XL keeps running although with no file open.

    I created this macro on Ofc2K using the "Record new macro" method. Trying that in XP doesn't help, it simply adds some default lines of code and the result is the same--i.e., in the print example above I try to tell it to print to "Rich's Printer" but all it does is add code to the effect of printing out to the default local printer.

    Help me, Obi-Wan Kenobi! You're my only hope! (You and all the other lounge users, that is. . . .)

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: XL 2K macro not working in OfcXP (XL XP)

    The printer may be valid, but the string you're passing to set it isn't. Try this: open Excel and select the alternative printer via Print, Printer Name selection drop-down, OK out. Then open the VBE, and in the Immediates Window (Ctrl-G toggles it open and closed) enter:

    ?Application.Activeprinter

    Use the returned result as the printer name in your macro. Also, you should be able to skip this line:

    Application.ActivePrinter = "Rich's Printer on LPT:PS8B1E-1"

    and use only this:

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "Rich's Printer on LPT:PS8B1E-1", Collate:=True

    Post back if this doesn't help.
    -John ... I float in liquid gardens
    UTC -7DS

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL 2K macro not working in OfcXP (XL XP)

    On the "Do you want to......." message, the general recommendation is to use ClickYes. There is no actual way to turn the message off. HTH
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •