Say you have a large list of Work Orders that you want to search and export that data to Excel to perform some other analysis, how would you do this? I recently came across a macro that can be used to do this and hope that you find this useful.
1. Save the excel file as a macro file *.xlsm file first.

2. Create a new macro by selecting: View, Macros, Record Macro. Make the Macro Name: “BuildList”. Created the Shortcut Key to whatever you want it to be. In this case, it was called “Ctrl + b”

3. Now edit the macro that was just created by the following navigation: view, macros, view macros, select the macro you just created “BuildList”, edit

4. Remove all the code under General and replace with the following code:
'Sub BuildList()
'Dim Str As String
'Str = ""
'For Each c In Range(ActiveWindow.RangeSelection.Address)
'Str = Str & c.Value & ";"
'Next c
'Sheets.Add
'Range("A1").Select
'ActiveCell = Str
'MsgBox Str
'End Sub
Sub BuildList()
'
Dim Str, Cnt1, Cnt2, Sep As Variant
Dim DataObj As DataLabel
Cnt1 = 0
Cnt2 = 0
Str = ""
'Application.InputBox("Add a starting character, leave blank if none
required")
Sep = Application.InputBox("Enter the
seperator you require")
For Each c In Range(ActiveWindow.RangeSelection.Address)
Cnt1 = Cnt1 + 1
Next c
For Each c In Range(ActiveWindow.RangeSelection.Address)
If Cnt2 < Cnt1 - 1 Then Str = Str & c.Value & Sep
If Cnt2 = Cnt1 - 1 Then Str = Str & c.Value
Cnt2 = Cnt2 + 1
Next c
'DataObj.SetText Str
'DataObj.PutInClipboard
'Sheets.Add
Range("A6330").Select
ActiveCell = Str
ActiveCell.Copy
Range("A1").Select
End Sub
5. Close the VB dialog box by clicking on the red X on the top hand corner:

6. Now redo the shortcut key: view, macros, select “BuildList” macro, options. Enter the letter “b” if you want your shortcut to be “Ctrl + b”, OK, red “X” on top right of macro dialog box.
7. Now paste the list of data in cell A1. In my case I have 10 rows of Work Order numbers:

8. Select the data you want, in my case cells A2 to A11, hit the shortcut key you chose, in my case “Ctrl + b”, enter “,=”, and hit OK
9. Now go to a notepad file and hit "Ctrl V". you should see the following result:

10. Essentially all we did was insert a “,=” after the first Work Order number. We could have done this with the “Concatenate” Excel function the long way round as well.
11. Now the 10 rows of data is in the clipboard with the “,=” inserted after the first Work Order number.
12. Go to your application…in my case Maximo. Go to the Work Order Tracking application, paste the Work Orders in the new format in the Find field and hit the binoculars button:

13. Go to the List Folder in Maximo and you should see the 10 works orders listed and you can export this to excel or wherever you want.
You are the best ! Thanks
ReplyDeleteGood morning all, You can enter India for business purposes. You can apply online Indian business visa application, first fill your all details in form then pay India business visa cost. Visa cost depends on your nationality. All processes online are safe and simple.
ReplyDelete
ReplyDeleteHi sir, many people ask, e visa India processing time, normal visa you can get 3 to 5 working days, in urgent cases 1 to 3 days.
Get Data Science Certification from top-ranked universities UTM, Malaysia, and IBM. We provide extensive training for the future-ready workforce.
ReplyDeletedata scientist course
tül perde modelleri
ReplyDeletesms onay
mobil ödeme bozdurma
nft nasıl alınır
ankara evden eve nakliyat
Trafik Sigortasi
dedektör
web sitesi kurma
Ask romanlari
Venture into the world of opportunities with Data Science in Bangalore and learn the valuable skills to demonstrate your capabilities to tackle this evolution of huge data. Avail benefits like Placement Assistance, Mock Interview, and Resume Building support from the placement team. Enroll now and learn Python, Tableau, SQL, Hadoop, and Spark to become a specialist in Data Science. best data science courses in chennai
ReplyDelete