Need assistance with creating a script for Excel

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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%desktoptemp.txt /node: “%pc%” computersystem get name,model, username /format:textvaluelist.xsl
wmic /append: c:users%username%desktoptemp.txt /node: “%pc%” registry get InstallDate
type c:users%username%desktoptemp.txt | clip
type c:users%username%desktoptemp.txt

Reply | Quote

Could you provide a sample of Temp.txt?

Reply | Quote

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
======================

Reply | Quote

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/library/ee692882.aspx

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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. 38648-sample

Reply | Quote

Summersond,

I will look for the posted file. Here is what VBA is capable of doing for you. On the desktop you double click on an excel icon and with nothing opening, you can paste the extracted value right into your ticket. You don’t need a batch file and you don’t need user input. VBA can capture the PC name with the following lines added to a routine coded to run but not open workbook:

Dim CompName As String
CompName = Environ$(“computername”)

If all goes right, the end result is that you double click and paste.

Maud

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Now that is what I call impressive!!!!!

Reply | Quote

Hi Maud

Many thanks. The key to getting the Dell Service Tag number was using the command line:
wmic csproduct get identifyingnumber > c:Tempdelltag.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

Reply | Quote

Summersond,

See if something like this works for you. First, download and save the attached workbook to the desktop of the workstation. Rename it to what ever you would like. When the user doubleclicks the desktop icon, Excel launces seamlessly behind the scenes, grabs the computer name and trims the first three characters, references your master file to obtain the related data in column B, places the data on the clipboard, then shuts down. The user only momentarily sees excel in the taskbar and can just paste into your ticket. It is unclear as to whether you need to use the computer name or the service tag as the search parameter if they are not one in the same. If you need the service tag, Zeddy’s excellent code can be incorporated into the routine.

You will need to change the following lines in the code to the file name and path of the source file:

FileToOpen = “Procurement_Table_Load_File – test.xlsx”
FilePath = “C:UsersMaudibeDesktop”

To open the file for viewing/editing, hold the shift key while employing a slower double click on the icon. Each time the user runs the file, a log is maintained on the worksheet: Computer name, retrieved data, Date/Time run. Note: You must save the file to the desktop before running. Also note that you do not have data in column B of your source file

Standard Module:

Code:

Public Sub POnumToClipBoard()
[COLOR=”#008000″]’———————————–
‘DISABLE CODE INTERUPTS AND SCREEN UPDATING[/COLOR]
    Application.EnableCancelKey = xlDisabled
    Application.ScreenUpdating = False
[COLOR=”#008000″]’———————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim wb1 As Workbook
    Dim rng As Range, cell As Range
    Dim FileToOpen As String  ‘DEFINE VARIABLES
    Dim CompName As String, POnum As String
    Dim LastRow As Long, NextRow As Long
    NextRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    [COLOR=”#008000″]’CHANGE TO CORRECT FILE NAME AND PATH OF SOURCE FILE[/COLOR]
    FileToOpen = “Procurement_Table_Load_File – test.xlsx”
    FilePath = “C:UsersMaudibeDesktop”
[COLOR=”#008000″]’———————————–
‘GET COMPUTER NAME AND TRIM FIRST THREE CHARACTERS[/COLOR]
    CompName = Environ$(“computername”)
    CompName = Right(CompName, Len(CompName) – 3)
    Range(“A” & NextRow) = CompName
[COLOR=”#008000″]’———————————–
‘OPEN SOURCE FILE[/COLOR]
    Workbooks.Open Filename:=FilePath & FileToOpen
    Set wb1 = Workbooks(FileToOpen)
[COLOR=”#008000″]’———————————–
‘CYCLE FOR MATCHING COMPUTER NAME AND GET RELATED DATA IN COLUMN B[/COLOR]
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range(“A2:A” & LastRow)
    For Each cell In rng
        If cell = CompName Then
            POnum = cell.Offset(0, 1)
            GoTo skip
        End If
    Next cell
skip:
    wb1.Close
[COLOR=”#008000″]’———————————–
‘CREATE LOG[/COLOR]
    ThisWorkbook.Activate
    Range(“C” & NextRow) = Now
    Range(“B” & NextRow) = POnum
    Range(“B” & NextRow).Copy
[COLOR=”#008000″]’———————————–
‘RESTORE SETTINGS, SAVE WORKBOOK, COPY PONUM TO CLIPBOARD[/COLOR]
    Application.ScreenUpdating = True
    Application.EnableCancelKey = xlInterrupt
    ThisWorkbook.Save
    ClipBoard_SetData POnum
    Application.Quit
End Sub

ThisWorkbook module:

Code:

Private Sub Workbook_Open()
Application.WindowState = xlMinimized
POnumToClipBoard
End Sub

Thanks to Jon Crowell at StackOverflow for his code (located in module 2) needed to retain the clipboard after the Excel application is terminated.

Just one question: The name or service tag of the computer never changes. These can easily added to designated cells in the file on the desktop so there would be no need to extract them each time the code is run. Would that be a consideration?

Reply | Quote

Zeddy,

After you initial post I booted up my old Dell XPS 600 and ran your worksheet. Very, very cool!!! Worked as you stated. Thanks for the additional info above.

Maud

Reply | Quote

Here is a batch file that is similar that works if the pc is online. Rename to .bat and run it. If it doesn’t work, create a shortcut to it and go into the properties of the shortcut and have it run as admin. It will then work fine. It prints out the info of the pc and also pastes it into the clipboard.

Reply | Quote

Nice Zeddy! The reason I veered from my initial route of using WMIC for inputting the pc info into the clipboard is that the remote pc has to be turned on and online for it to return a valid response. For our service desk’s use, it is very possible that the pc would be non functioning when they call in. This is why I went to using the spreadsheet to get all of the information needed by them. It will be up to date enough and has all needed information in it for their use. I am waiting for Maud’s routine to see how it works. I appreciate all of everyone’s help!

Reply | Quote

Thanks Maude! I will test this out. 2 things:
1. I do not need column B but columns A,C and O. (PC service tag, Date purch and model)
2. How does the service desk person enter the pc name of the client calling in to get that info?

You are stripping off the first 3 characters of the pc name so all is well. The service desk person would need to enter the 3 character prefix then which is fine.

I am trying to debug the script and tried holding down the shift key while slowly clicking the icon and Excel either does nothing or seems to run and disappear. I had it up to debug to change the path but when I put in the correct path, now excel disappears and nothing is in the clipboard… Ideas?

Reply | Quote

Maude, I modified the modules to accommodate the different columns. can you look over the code to see if all is correct? Not sure on the Public Sub line if that name needs changed to reflect another name since I removed references to the POnum elsewhere and changed. I am wondering if the line which uses Clipboard_Setdata is valid since I wanted to grab all 3 values separated by a comma. Also, is the code in “ThisWorkbook module correct? I am wondering if I can use the 3 clipboard inputs like I have listed there. I did not modify Module2 at all.

dave

Module1 Code:

Public Sub POnumToClipBoard()
‘———————————–
‘DISABLE CODE INTERUPTS AND SCREEN UPDATING
Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False
‘———————————–
‘DECLARE AND SET VARIABLES
Dim wb1 As Workbook
Dim rng As Range, cell As Range
Dim FileToOpen As String ‘DEFINE VARIABLES
Dim CompName As String, PurchDate As String, Model As String
Dim LastRow As Long, NextRow As Long
NextRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
‘CHANGE TO CORRECT FILE NAME AND PATH OF SOURCE FILE
FileToOpen = “Procurement_Table_Load_File.csv”
FilePath = “C:UserssummersondDesktopwmic_commands”
‘———————————–
‘GET COMPUTER NAME AND TRIM FIRST THREE CHARACTERS
CompName = Environ$(“computername”)
CompName = Right(CompName, Len(CompName) – 3)
Range(“A” & NextRow) = CompName
‘———————————–
‘OPEN SOURCE FILE
Workbooks.Open Filename:=FilePath & FileToOpen
Set wb1 = Workbooks(FileToOpen)
‘———————————–
‘CYCLE FOR MATCHING COMPUTER NAME AND GET RELATED DATA IN COLUMN B
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(“A2:A” & LastRow)
For Each cell In rng
If cell = CompName Then
PurchDate = cell.Offset(0, 2)
Model = cell.Offset(0, 14)
GoTo skip
End If
Next cell
skip:
wb1.Close
‘———————————–
‘CREATE LOG
ThisWorkbook.Activate
Range(“C” & NextRow) = Now
Range(“B” & NextRow) = PurchDate
Range(“B” & NextRow) = Model
Range(“B” & NextRow).Copy
‘———————————–
‘RESTORE SETTINGS, SAVE WORKBOOK, COPY PONUM TO CLIPBOARD
Application.ScreenUpdating = True
Application.EnableCancelKey = xlInterrupt
ThisWorkbook.Save
ClipBoard_SetData CompName, PurchDate, Model
Application.Quit
End Sub

ThisWorkbook Code

Private Sub Workbook_Open() ‘<<< Currently when I run it now, it seems to bomb on this line…
Application.WindowState = xlMinimized
CompNameToClipboard
PurchDateToClipBoard
ModelToClipBoard
End Sub

Reply | Quote

Private Sub Workbook_Open() ‘<<< Currently when I run it now, it seems to bomb on this line…
Application.WindowState = xlMinimized
CompNameToClipboard
PurchDateToClipBoard
ModelToClipBoard
End Sub

Dave,

The code is not bombing out on the line "Private Sub Workbook_Open()". It is bombing out on the line that you added, "CompNameToClipboard", which calls a routine that does not exist. In your image, the line that causes the routine to fail is highlighted in blue. If that line didn't fail, the next 2 lines after it would have because the same mistake is repeated. The name of the routine is POnumToClipBoard

Change back to:

Code:

Private Sub Workbook_Open()
Application.WindowState = xlMinimized
POnumToClipBoard
End Sub

We'll Dim an additional variable, Clipbrd, to address the next issue:

Code:

    Dim CompName As String, PurchDate As String
    Dim Model As String, [COLOR="#0000FF"]Clipbrd As String[/COLOR]

I do not need column B but columns A,C and O. (PC service tag, Date purch and model)

In post #1 you asked to return Column B but now you want A, C, and O. OK, things change as the project progresses. But is Column O, Chassis description, the same as model?

Code:

[COLOR="#008000"]'———————————–
 'CREATE LOG[/COLOR]
 ThisWorkbook.Activate
 Range("C" & NextRow) = Now
 Range("B" & NextRow) = PurchDate
 Range("B" & NextRow) = Model
 Range("B" & NextRow).Copy

should be rewritten to capture the 3 variables

Code:

'———————————–
 'CREATE LOG
    ThisWorkbook.Activate
    Range("B" & NextRow) = Model
    Range("C" & NextRow) = PurchDate
    Range("D" & NextRow) = Now
    [COLOR="#0000FF"]Range("E" & NextRow) = CompName & "," & PurchDate & "," & Model[/COLOR]
    [COLOR="#0000FF"]Clipbrd = Range("E" & NextRow)[/COLOR]
[/COLOR]

I could have directly set Clipbrd to the concatenation but for demonstration purpose, we'll place it in column E the set Clipbrd from there.

You made a change to the line:

From:
ClipBoard_SetData POnum

To:
ClipBoard_SetData CompName, PurchDate, Model

With this change, you are sending 3 parameters to a subroutine that expects only one. If your code got this far, it surely would have failed. In the above code in blue, I have combined the 3 values separated by commas into 1 value (Clipbrd) and sent that to the subroutine to be placed on the clipboard.

Code:

    Application.ScreenUpdating = True
    Application.EnableCancelKey = xlInterrupt
    ThisWorkbook.Save
    [COLOR="#0000FF"]ClipBoard_SetData Clipbrd[/COLOR]
    Application.Quit

How does the service desk person enter the pc name of the client calling in to get that info?

The overall workflow, as I understood it, was the user that was pasting the data into the ticket, not the helpdesk tech receiving the ticket. If so, that is a whole different ball game.

Attached, is the completed file with all the changes made along with your file name and path. Make sure for testing that you have a matching computer name (minus left 3 characters) in Column A of the .csv file as the computer you are running, and place some Dell models on Column O.

Save it to your desktop then double click the icon. Expect just a flash on the screen then paste into notepad for the results.

Maud

Reply | Quote

It seems to work wonderfully pulling my local info and running it against the file! Now to get a box to pop up and ask for the 7 digit service tag and press OK to enter that data into the file to pull from and we’re all set! Any way to add text to the output? Instead of having ABC123A,11/13/12,Optiplex 790, could it look like Tag# ABC123A, Purch. Date 11/13/12, Model OptiPlex 790?

Reply | Quote

In post #1 you asked to return Column B but now you want A, C, and O. OK, things change as the project progresses. But is Column O, Chassis description, the same as model? Yes, it returns the same information.

The overall workflow, as I understood it, was the user that was pasting the data into the ticket, not the helpdesk tech receiving the ticket. If so, that is a whole different ball game. No, the user calls into the service desk and the support person enters the service tag in a box, hits enter and the values are retrieved.

I will test this out.
Thanks!

Reply | Quote

Absolutely! Will work on it shortly.

1. Will the users use the app like you did to pull up the computer name? If desired, we can make that appear in a message box so the user can reference it when speaking with the rep. If you are using Outlook, we could also auto generate an email to send the data to the help desk as confirmation of some sort.

2. When the user gives the rep the computer name, will the rep be removing the first 3 digits and entering the rest into the input box or will they enter the whole tag #?

Maud

Reply | Quote

Dave,

Here is the file that when the Service Rep double clicks the desktop icon for the file, an input box will open prompting the input of the computer name. If the Rep clicks cancel or the red X in the top right corner, the form closes and execution halts. If the user enters the wrong Computer name, a message appears indicating no match found and execution halts. If a matching computer name is found, the input box closes, the computer name is trimmed, the computer data is extracted and concatenated into a string, then placed on the clipboard ready for pasting. The additional verbiage was added in the Computer Information string:

Tag#: ABC123B, Purchase Date: 5/5/2005, Model: XPS

Your same path and file name were added to the code. To open the file for editing or to view the log, hold down the shift key while double clicking the icon. This will bypass the workbook_open event routine which runs the file minimized and opens the input form.

Here is a segment of my desktop showing that only the input form displays.

38688-sommersond1

Reply | Quote

Now that is really cool! I beg of one (maybe 2) last favors please.
1. Can you make the OK button automatically highlight and come into focus so they don’t have to mouse over and click it (OK), just hit the enter key?
2. Any way to make it not case sensitive so it will always look at the input as all caps?

Thank you so much for all of your help! This will be a great piece to add to their workflow!

Reply | Quote

Ok, I got the OK button to be in focus by setting the option for the OK button CommandButton Default = True. This will make the OK button go in focus when one starts to type in a number.

Still working on the All Caps part. I found this snippet online but am not sure where to insert it…

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End Sub

Reply | Quote

Dave,

Here are the changes you need to have made:

Code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
[COLOR=”#008000″]’PRESSING ENTER WILL BE SAME AS CLICKING OK BUTTON[/COLOR]
OKButton_Click
End Sub

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
[COLOR=”#008000″]’CHARACTER BECOME CAPS AS YOU TYPE[/COLOR]
TextBox1.Value = UCase(TextBox1.Value)
End Sub

Private Sub UserForm_Activate()
[COLOR=”#008000″]’GIVE FOCUS TO TEXTBOX ON STARTUP[/COLOR]
TextBox1.SetFocus
End Sub

HTH,
Maud

Reply | Quote

Maude, I can’t thank you enough for all of your help! I believe it is working like I intended it to. Now to move it to the server and see if there are any permissions issues that others will encounter (hopefully not too many).

Thanks again!
Dave

Reply | Quote

You’re Welcome Dave. PM me if I can help further.

Maud

Reply | Quote