Posts by TimDunn

    Hey Roy,

    I think I have found a temporary solution. I didn't and still don't really want the user to have to do anything but fill in the blanks and hit submit but..... I made the changes below . We will have to make sure the printer we want to use is set up on the users kiosks if we end up using this until the Application.ActivePrinter is figured out. I have only tested it on my computer so far but will try it on one of the kiosks today. I appreciate your help and will keep trying to get the original plan to work in the meantime.


    Code
    1. 'If Application.ActivePrinter <> "NPI140D8A (HP LaserJet 400 M401n) on Ne00:" Then _
    2. 'Application.ActivePrinter = "NPI140D8A (HP LaserJet 400 M401n) on Ne00:"
    3. Application.Dialogs(xlDialogPrinterSetup).Show
    4. wsO.PrintOut
    5. Application.ActivePrinter = CurrentPrinter

    It's the "Application.ActivePrinter" line right before the wsO.printout.

    Hi Roy,

    Thank you!! Seems to be working great so far. I am still trying to figure out how to get the new sheet to print to a network printer.

    Added:

    Code
    1. Dim CurrentPrinter as string
    2. CurrentPrinter = Application.ActivePrinter

    at top of code, and:

    Code
    1. Application.ActivePrinter = "NPI140D8A (HP LaserJet 400 M401n) on Ne00:" ' (obtained this by running a quick sub to show Application.ActivePrinter on a pc in my building to use it for testing.)
    Code
    1. wsO.PrintOut , preview = False
    2. Application.ActivePrinter = CurrentPrinter

    after the PasteSpecial statements.

    Code attached keeps getting: Run-Time error 1004 Method "ActivePrinter" of object'_Application' failed

    I have tried multiple variations but am not having any luck.

    Any suggestion would be greatly appreciated.

    Thank you,

    Tim


    Roy,

    No worries, I appreciate all your help. Wonder if I might run another idea by you. While continuing to search for solutions, I ran across some information about appending a date and time to the saveas file name. I changed the line of code to read:

    .SaveAs Filename:=path & filename1 & Format (Now(), "MMDDYY hhmmss"), FileFormat:=51

    I ran a half dozen tests and so far it saves each file with a unique file name. As long as each name contains the value from E7, I don't have any issues with the file names. Are there any hidden gotcha's I should be cautious of? Would really appreciate your opinion of that methodology. If that would work then the last piece of the puzzle would be getting it to print to a network printer. I will continue to see what I can find on that.


    Thank you again,


    Tim

    Hi Roy,

    Sorry to be a pain. I get a file already exists message when I step through the code the second time. First pass creates the new workbook, copies the range, and saves the new workbook name based on the value in cell E7 with no problem. clip attached. If I say yes to the overwrite, it completes the macro with no further errors.

    Thank you!!


    Hi Roy,

    So far, with your help, I have been able to save an identified range to a new workbook. (code below) The new workbook name is derived from a cell value in the original workbook. Concerns of duplicate file names prompted the need to check for existing files of the same name and to append the file name if the file already exists. I have tried to plug the code you sent me in to my existing code and I have been completely unsuccessful. I have no idea exactly where to insert the code and when I try I get an error telling me I have to end the original sub. I have searched this forum as well as others but to no avail. If the file exists, I want to append the file name and then save it. If the file does not exist (else) I assume the .saveas filename: in the existing could would work. I am completely confused at this point.

    Thanks for any additional guidance.

    Tim



    Roy,

    So far that works great! The info in "e7" is a work order number that the user will enter. A potential issue that I was just made aware of is that it is possible that a user may have to submit two forms (requisitions) using the same work order number ("e7"). I don't want to overwrite if the file already exists but would love to append to it if needed. Maybe with a -1, -2 etc.... I also still need to see if I can get the new workbook to print on a network printer as a part of the "submit" process. I don't want to wear out my welcome as I am extremely appreciative of your time and expertise. You have already put me WAY ahead of where I would have been. I will be researching the "if file exists" and printing tasks and would truly appreciate any advice or guidance you have on those topics.

    Thank you for your invaluable assistance,

    Tim

    Hi Roy,

    Quick update. I moved the path and filename code up under the "source/input workbook" in the code I sent you. (Edited code is below)

    I changed the ".saveas" section back to use that path and filename and was able to generate a new workbook with the correct name.

    When I went to open the new workbook I got a warning about mismatched file formats but was able to open the file. I then changed the .saveas file format to "52" and was able to create a new workbook and open it with no warnings or errors. Is there a way to save the new workbook as .xls or .xlsx without getting the warning message?

    Thanks so much for your help!

    Tim

    Hello, My name is Tim.


    I am working on a project and in need of some help.

    I have created a form in excel and inserted a button to "submit" the form.

    Submit in this case needs to accomplish the following:

    Copy a range from the form and save it to a new workbook, Paste the formats and the values (form will have Vlookup formulas). So far the code below has worked for this part.

    Save the new workbook using a specific path and filename. Filename to be derived from a cell on the form. I get a "Method 'SaveAs of Object'_Workbook' failed" error.

    When I specify the path and a filename directly in the .saveas line it seems to work.

    This is as far as I have gotten so far by piecing together code I have found online. Once this is working I would like to also send the copied range (new workbook) to a network printer.

    I have not found or tried any code for that part yet but would welcome any help or suggestions.

    I have attached the "Form" and the code I am working with is below.

    Thank you in advance for any help and suggestions, both are greatly appreciated.