Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
vbs script pull the value in an excel cell and render the value RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I'm using BioPDF to convert and append printer output to a pdf file, which works great. The problem is I need the value in cell c2 of a spreadsheet to be rendered from a vbs script so the output winds up appended to the resultant pdf file name. BioPDF offers a number of canned macros to add the date, time or a static value to the output file name. The custom macro option has the syntax <vbs:path\filename.vbs> I know it can work because I tested it with a vbs script that throws up an input box and the box does appear, but the program doesn't receive what I enter in the box and I get an error.

    Any help would be appreciated on how to make such a script.

    here is what I tried with the latest inputbox script, it failed saying object required: 'lotnumber'

    strAnswer = InputBox("Please enter lot number:", _
        "Lot Number")

    other versions of this vbs returns error object required "wscript"

    I have no clue how to make a script that will pull a cell from excel and render it to the program, the value contains numbers and letters.

    there is a macro folder, the program seems to run all the vbs files that are in the folder when ever printing occurs.

    FYI, we are using an access 2000 db to build the printer output, it populates a spreadsheet with the lotnumber value, I don't want the user to have to edit each filename or enter the lotnumber twice. the guy that developed the access form is long gone.


    • Edited by Shazbotz Thursday, May 14, 2015 8:39 PM
    • Moved by Bill_Stewart Thursday, July 9, 2015 4:41 PM Question outside forum scope
    Thursday, May 14, 2015 8:38 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I couldn't find much by way of support on their web site either, other than a "contact us" form that notes it may take some time for the vendor to respond, unless one has a support contract that provides the user with priority support.

    The right thing to do in this scenario is to contact the vendor. This is not the correct forum to ask support questions about third-party software.

    -- Bill Stewart [Bill_Stewart]

    • Proposed as answer by Bill_Stewart Monday, May 25, 2015 4:09 PM
    • Marked as answer by Bill_Stewart Thursday, July 9, 2015 4:40 PM
    Friday, May 22, 2015 8:32 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Unfortunately we're not going to be able to help since there is too much missing information in your question, and I don't think you quite understand the scope of what you're asking.

    I would recommend hiring a consultant to help you sort this out.

    -- Bill Stewart [Bill_Stewart]

    Thursday, May 14, 2015 8:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I totally agree with your response, I asked my question before researching and studying scripting enough! The budget here is zero for purchases, consultants, I hear things like, "what do we have you for". However through persistence I've been able to complete the first part of the script which successfully pulls the value that I need from the spreadsheet. Please view the script below.

    Dim xlApp
    Dim xlBook
    Dim xlSheet
     
    Set xlApp = CreateObject("Excel.Application")
    xlApp.visible = false
    Set xlBook = xlApp.Workbooks.open("c:\bar\Mail Merge barcode Data Source.xls") 
     
    Set xlSheet = xlBook.Worksheets("Sheet1")
    Msgbox xlSheet.Cells(2, 3).Value

    This delivers the data I need, the shipment lot number, but it goes to a Msgbox, I just need to massage this script to provide the value as output. The bioPDF program which calls the vbscript through a macro is waiting for the output, it can then plug that value into the appropriate place in the pdf filename and I get to end this project! Any help is appreciated.

    Bryan

    Friday, May 22, 2015 6:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    No one could have guessed that from your question.

    \_(ツ)_/

    Friday, May 22, 2015 6:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The VBScript code you have posted automates Excel, but does not display a message box.

    The problem is that you have an idea of what you're asking, but we don't since we don't understand your application, we don't have access to your system, and we can't see your screen.

    Based on only the information you have posted, there is no possible way for us to write a solution for your scenario. That's not the purpose of this forum, in any case.

    -- Bill Stewart [Bill_Stewart]

    Friday, May 22, 2015 6:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    the biopdf program apparently wants an output object defined with the value of the main body of the vbs filename, lotnumber.vbs.

    An error occurred.

    Error 424: Error running macro: lotnumber.vbs.

    Object required: 'lotnumber'

    Source: Microsoft VBScript runtime error

    Internal hint: Get the configuration for this PDF generation

    Friday, May 22, 2015 7:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The only way someone will be able to help you is if they are sitting with you and looking at your screen.

    This is not something we can fix for you from afar in a free forum. We're not consultants.

    -- Bill Stewart [Bill_Stewart]

    Friday, May 22, 2015 7:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In most of these custom script extensions the vendor sets a set of objects that are injected into the script environment.  The error is obvious and is telling you what you are doing wrong. The fact that you called the script the same name as a variable is confusing you.  Change the script name to see what I mean.

    Object required: 'lotnumber'

    This is likely telling you that you need to create a "lotnumber" object using the vendors injected objects. This is how you send data back to the program.,

    This is all standard for the "scripting object library' and the script support.

    Here is an article on how it works: https://msdn.microsoft.com/en-us/magazine/cc302278.aspx

    This object is not obsolete and is only used in older programs.  Contact the vendor for support with this library.

    \_(ツ)_/

    Friday, May 22, 2015 7:32 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Here is the doc: http://www.biopdf.com/guide/vbscript_events_and_macros.php

    The company has no support forums and charges for support by email it appears.

    \_(ツ)_/


    • Edited by jrv Friday, May 22, 2015 8:12 PM
    Friday, May 22, 2015 7:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The company has not support forums and changes for support by email it appear4s.

    Did you mean to say: "The company has no support forums and charges for support by email"?

    -- Bill Stewart [Bill_Stewart]

    Friday, May 22, 2015 8:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The company has not support forums and changes for support by email it appear4s.

    Did you mean to say: "The company has no support forums and charges for support by email"?

    -- Bill Stewart [Bill_Stewart]


    Sorry - yes.  Doing too many things at once.

    \_(ツ)_/

    Friday, May 22, 2015 8:12 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I couldn't find much by way of support on their web site either, other than a "contact us" form that notes it may take some time for the vendor to respond, unless one has a support contract that provides the user with priority support.

    The right thing to do in this scenario is to contact the vendor. This is not the correct forum to ask support questions about third-party software.

    -- Bill Stewart [Bill_Stewart]

    • Proposed as answer by Bill_Stewart Monday, May 25, 2015 4:09 PM
    • Marked as answer by Bill_Stewart Thursday, July 9, 2015 4:40 PM
    Friday, May 22, 2015 8:32 PM