10/5/2023 0 Comments Fedex tracking number on label![]() Private Function GetLastDataRow(ByVal xlSheet As Worksheet, Optional ByVal columnLetter As String = "A") As Long ShData.Cells(HEADER_ROW_NUM, DataColumns.).Value = "Concat Links" ShData.Cells(HEADER_ROW_NUM, DataColumns.).Value = "Trimmed Tracking" '//Re-write the column headers of the cleared columns It was mainly done this way because I had never used private enums before. 'TODO: Take a look whether using private enum for columns is even necessary for this project scope with such few headers _ ShData.Columns("A:C").NumberFormat = there is old generated data, clear it ![]() '//Format columns A:C as text, not D, because I might want it to be a clickable link in the future 'Adjusts Excel settings for faster VBA processingĪpplication.EnableAnimations = Not ToggleĪpplication.DisplayStatusBar = Not ToggleĪpplication.Calculation = IIf(Toggle, xlCalculationManual, xlCalculationAutomatic) Private Sub LudicrousMode(ByVal Toggle As Boolean) MsgBox Err.Number & vbTab & Err.Description, vbCritical, "Error" MsgBox "Time taken to complete:" & vbNewLine & endSeconds - beginSeconds & " seconds", vbInformation, vbNullString Module: m_fedex_tracking Attribute VB_Name = ExplicitĬall FilterForOnlyLinksAndPasteToSheetLinksĬall SetConditionalFormattingStatusColumnofStatusSheet '//Move the cursor here for the user to begin pasting here MsgBox "Please paste in the following data:" & vbNewLine & vbNewLine & "TRACKING number into A2" & vbNewLine & "CARTON number into B2", vbInformation, "Required Data" Private Function DoSimpleChecks() As String ![]() Worksheet Codename: shData ProcedureNotUsed I'm looking for a critique, so I can improve further. I could have used XMLHTTPRequests to grab element id's, but there were too many possible status sections to mess around with, and if the site changed at all, the code might stop working. This code is actually a re-write of my original, which was awful code, but it worked and saved me a ton of time at work. I've been programming in VBA for less than a year. A green filter on the status column will only show cartons in-transit, like so: Status), the filtered pastes will form into a table, and an index match will show the associated carton. Some filtering will occur, as to not grab all the unnecessary text.įinally, in shStatus (4. The code will loop through the links, opening up Internet Explorer, Ctrl+A (selecting all), and Ctrl+C (copy), then return to shPastes(3. The code will generate links where each link concatenates up to 30 tracking numbers together, like so: Start off on shData (1.User Input) by pasting in the tracking & carton numbers like so: From there, I could take that list and paste it into our warehouse management system to do the necessary processing. I could then filter on the green color, and I would have a list of carton numbers that had left the building. On that third column, I applied conditional formatting of green fill color to highlight text containing “picked up", "in-transit", "delivered". My goal was to have a third column show the FedEx status. In another column, I had a list of the associated carton numbers. I had a column of FedEx tracking numbers. You might even give up, because it’s so tedious. That is going to take a long time, and due to nature of the process and length of the numbers, it will be very error-prone. So, if you're doing this manually for 1000 tracking numbers, you must somehow break up your list into 30 chunks at a time, then paste them into the website, do your looking back and forth comparing exercise, then do that 33 more times. The carton number that is in-transit is what we need, so we can process the carton number.Īnother issue is the FedEx website only allows you to paste in 30 tracking numbers at time. Ok, so you have the status on the website, you match that 10+ digit long tracking number with the one on your list, then you can highlight the associated carton number. In most of my usage, there were only 2-3 sections used, but it still required constant up and down comparing back and forth. ![]() To clarify, if you have a list of numbers you're looking up, the website takes the tracking numbers out of order, so as you're comparing your list with the website, you're scrolling up and down constantly trying to figure out which section the number is in. In June 2019, while looking at the html elements, there were 19 sections. The FedEx website categorizes tracking numbers in various sections depending on status. The statuses that I would be looking for on the FedEx website are "picked up", "in-transit", and "delivered". For cartons that are not already in a manifested or invoiced status within our warehouse management system, it is necessary to check the FedEx website to determine if a carton has left our building. The two main numbers it will have are a FedEx tracking number and a carton number. If you buy something from our facility, you will get a box with a shipping label on it.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |