Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Iowa
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Need assistance with creating a script for Excel

    This is way beyond my knowledge so I need some collective assistance. I want to take a variable input by the user using the SET command (ex. US-1234ABC) and strip off the 1st 3 characters to end up with (1234ABC). Then take that variable and open an Excel spreadsheet (ex. data.xlsx) and look in column A for a match. If it finds a match take the data from column B in the same line and append it to a file (temp.txt) that already has other info in it pertaining to this variable. I have no clue how to do this and any help would be appreciated!

    Thanks in advance!

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Summersond:
    Can you provide a bit more information. How does the user provide the original data? Is it by an Excel Input Box? is it by entering the information in a specific cell of a Workbook? Once the user enters the information do you always want the same file data.xlsx to open? Do you want the Macro to open temp.txt? or just append it?

    Looking at the request, it could be far easier to limit the use of VBA and instead use Excel to get this done. What I mean is if a user opens a workbook and there is a hidden sheet or hidden workbook with the data.xlsx information Excel's string functions can strip the first three digits the a simple vlookup would return the informaiton in Column B of the open data.xlxs Alternatively, using the "On Open" Event you could have VBA open and make active the "data" workbook (Hidden if desired and with a password to unhide). Now a much simpler macro would transfer the data from excel to the txt file.

    I am not saying VBA cannot do everything, to me it would make more sense to use Excel and its built in functions to do as much of the work as possible and leave the VBA to finish the task.

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Iowa
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I am all for the easiest way here. I just don't know Excel that well and didn't have much luck hunting for what I wanted to do. Here is the batch file I am using to import the other info into the temp.txt file. The variable I want to pass to Excel is %pc%. The user would only run the batch file and the script would go out and get the info and not actually open the excel file. The final listing in the temp.txt file would have all of the below info in it as well as the import from Excel.

    Thanks for the help!

    @Echo off
    set /p pc= Enter PC name

    wmic /output: c:\users\%username%\desktop\temp.txt /node: "%pc%" computersystem get name,model, username /format:textvaluelist.xsl
    wmic /append: c:\users\%username%\desktop\temp.txt /node: "%pc%" registry get InstallDate
    type c:\users\%username%\desktop\temp.txt | clip
    type c:\users\%username%\desktop\temp.txt

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Could you provide a sample of Temp.txt?

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Iowa
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The text below would be appended with the info from the Excel sheet. When a new query would be done, the current info would be overwritten with the new info.

    ==============
    Model=OptiPlex 990
    Name=US-CSJ9YQ1
    UserName=summersond


    InstallDate
    20110721112724.000000-300
    ======================

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Iowa
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I was looking for alternatives to input a variable and pull out data and found this on MS site using ADO. It looks like it might work but not sure if it would pass a variable in, plus not sure how it would append the temp.txt file. http://technet.microsoft.com/en-us/l.../ee692882.aspx

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Summersond,

    If I am understanding you, what you are trying to do can all be done within excel and a whole heck of a lot easier and dependable.

    If you will be using the same Excel file that contains the matching data in column A and related data in column B, then you can put all your code into that file. An input box can grab user entered criteria, then the code would look for a match in Column A then open and output and append your data to the text file. This will eliminate the need and maintenance of a the batch file. If a text file holds the search parameter, then the code could be modified to open the text and extract the parameter in the place of an input box.

    Alternately, if you will be using different Excel files that hold the data, the approach I would use is to have a master Excel file containing the master code that is always initially run. The code will grab the parameter (text file or input box), open the excel file that contains the matching data, perform the search on that file and extract results, then output to your appending text file. This will also allow the master file to do some record keeping or log function if you like.

    As duthiet pointed out, some of the workload can be shared with excel functions and that may be his expertise. This, of course, is a preference. I prefer the all VBA route because I find overwriting formulas very irritating and more difficult to trouble shoot.

    Either way, it is far, far more easy to pull data into Excel rather than having cumbersome batch file export it into Excel. This sounds like an interesting project and I for one would be happy to pitch in. Some things that would need to be considered are:

    1. Will the same Excel be used that houses the data to be compared always be the same file?
    2. Can the search parameter be obtained using an input box from within Excel (preferred) versus a batch or text file? If not, what is the complete path of the text file?
    3. What is the complete path of the Excel file that holds the data?
    4. What is the path of the text file that the data will be outputted to?
    5. Will the appended data in the text file be placed before or after trailing ============= bar?

    HTH,
    Maud

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Iowa
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Maud! After looking at my data that I pulled using WMIC, all of that info is in this file so EVERYTHING I need is in this file. That makes it easier! To answer your questions...

    1. Will the same Excel be used that houses the data to be compared always be the same file? Yes, it will be updated monthly but the format is the same.

    2. Can the search parameter be obtained using an input box from within Excel (preferred) versus a batch or text file? If not, what is the complete path of the text file? Not sure how to answer this one. Multiple users would be using this system and I would them to just need to run a simple batch file external to the excel file to go into the file, grab the info, and paste into the clipboard without actually opening the file itself. They would paste this info directly into a service ticket system we use.

    3. What is the complete path of the Excel file that holds the data? It would be on the server common for all to access, so just leave a server path in the code and I can paste in the final path later.

    4. What is the path of the text file that the data will be outputted to? Not sure I need a text file now, just the clipboard for temp storage until they paste into the service ticket system.

    5. Will the appended data in the text file be placed before or after trailing ============= bar? The bars were just to delineate the actual pulled data from the rest of my message. Now all info will be pulled from excel with no other outside data needed.


    I would need to match the input to column A and when a match is found, pull data in column D and U on the same line. The input text for the user needs to say "Please input 7 character service tag without US-". This will alleviate having to strip off the 1st 3 characters of the pc name, as column A has just the 7 character string. An option might be to check for exactly 7 characters and if it is less or more, run the above message. Then you could simply ask "Please input 7 character service tag" to start with.

    Thanks for any help!!! If you have any other questions, please let me know. I think this will really help speed up the ticketing process and get us the info we need to process the ticket 100% of the time.

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Summersond,

    Could you post a sample of the monthly updated excel file? Is the parameter that you are passing to the Excel file using the command line, the pc name of the computer being used?
    Last edited by Maudibe; 2014-12-05 at 21:49.

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    Iowa
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I will shortly post a sample of the file. Yes, it is the name of the computer. I would like the batch file asking the user for the pc name to enter only the service tag of the unit. They are all Dells and that is a 7 character alpha numeric value. If they enter more than 7 characters, I want it to prompt them to go back and enter only the 7 character value. Sorry the pic is small! I wasn't sure how to attach the file. It will be a .csv file. Just click on the pic and it will open in a new window. sample.png
    Last edited by Summersond; 2014-12-08 at 12:53.

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Summersond,

    Your users may not need to input the computer name. VBA can gather that data for you using the following lines:

    Dim CompName As String
    CompName = Environ$("computername")

    I have an idea that will involve a workflow of just double clicking a desktop icon then pasting the correlated data into your ticket. I will await the file to be posted.

    Maud

  12. #12
    New Lounger
    Join Date
    Dec 2009
    Location
    Iowa
    Posts
    17
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Here is the test file

    I had to rename it to a .xlsx to get it to upload. It is a .csv file in real life.
    Attached Files Attached Files

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    If you want to get the Dell Service Tag number for the PC, you can use the vba routine in my attached file.

    zeddy
    Attached Files Attached Files

  14. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2014-12-09)

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Now that is what I call impressive!!!!!

  16. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    Many thanks. The key to getting the Dell Service Tag number was using the command line:
    wmic csproduct get identifyingnumber > c:\Temp\delltag.txt

    This uses the old Windows Management Instrumentation Command-line (wmic), which in turn uses the power of Windows Management Instrumentation (WMI) to enable systems management from the command line. I piped the result to a temp file, to be read as input later.

    From the DOS prompt, you can use the following command line:
    wmic csproduct get identifyingnumber,name,vendor

    This will display the serial number, model, and brand of the computer. Try it on any PC.

    For a Dell, this will give you something like:
    IdentifyingNumber Name Vendor
    4P97QQ1 XPS 15 9530 Dell Inc.

    For other laptop/pcs/desktops you get serial number, model, brand.

    If you wanted to get the Dell Service Tag from a remote computer (rather than the current one), you would use this command line:
    wmic /user:administrator /node:remote-host bios get serialnumber

    ..where you would replace remote-host with the workstations name or IP address you are querying.

    zeddy

Page 1 of 3 123 LastLast

Tags for this Thread

Posting Permissions

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