Accessing an image that's inside of an Excel Table via VBA
I am designing a VBA Form in Excel. The Workbook has a table called "images", and inside there I am dropping some images from my local hard drive.
These Workbook & UserForm are to be shared with my colleagues. They might not have these images in their harddrive, but they will have them inside of the Excel table.
I am looking for a way to load an image that's inside of a table inside of an "Image" VBA form control.
In Google all I find is how to load an image from my hard drive (i.e. using an absolute path like "C:/my_images/car.png"). What I can't find is how to load an image that's within a table, i.e. already bundled within the Workbook.
See also questions close to this topic
How to compare two columns from two DFs keeping some column constants and print row?
I'm working on a project where I have to find the changes done in second sheet (specific column) as compare to primary/Master sheet. after that I wanted to print or save the complete row in which changes are found. here are more details. both the excel sheets have many columns my master sheet has data something like as follows:
TID LOC HECI RR UNIT SUBD S EUSE INV ACT CAC FMT CKT DD SCID CUSTOMER F&E/SERVICE ID BVAP PORD AUTH RULE ST RGN CHCGILDTO3P050101D CHCGILDTO3P M3MSA0S1RA 0501.01D 1A1 IE D STR3RA8 S CL/HFFS/688898 /LGT 2018-07-21 BLOOMBERG LP DS3-16668545 WMS881282 E.485339 IL N CHCGILDTO3P050101D CHCGILDTO3P M3MSA0S1RA 0501.01D 1A2 IE J DNA UNDER DECOM EID 2466 2019-03-22 WMS881282 E.485339 IL N CHCGILDTO3P050101D CHCGILDTO3P M3MSA0S1RA 0501.01D 1A3 IE J DNA UNDER DECOM EID 2466 2019-03-22 WMS881282 E.485339 IL N CHCGILDTO3P050101D CHCGILDTO3P M3MSA0S1RA 0501.01D 1A4 IE J DNA UNDER DECOM EID 2466 2019-03-22 WMS881282 E.485339 IL N CHCGILDTO3P050101D CHCGILDTO3P M3MSA0S1RA 0501.01D 1A5 IE J DNA UNDER DECOM EID 2466 2019-03-22 WMS881282 E.485339 IL N
and my second sheet has data as follows :
HECI UNIT INV SUB ACT CKT PACT DD LOC RR M3MSA0S1RA 1A1 IE $ CL/HFFS/688898 /LGT D 72118 CHCGILDTO3P 0501.01D M3MSA0S1RA 1A2 IE J DNA UNDER DECOM EID 2466 32219 CHCGILDTO3P 0501.01D M3MSA0S1RA 1A3 IE J DNA UNDER DECOM EID 2466 32219 CHCGILDTO3P 0501.01D M3MSA0S1RA 1A4 IE J DNA UNDER DECOM EID 2466 32219 CHCGILDTO3P 0501.01D M3MSA0S1RA 1A5 IE J DNA UNDER DECOM EID 2466 32219 CHCGILDTO3P 0501.01D
so first i want to check if the values of LOC HECI RR & UNIT are same in both the sheets I want to move forward and comapre ACT column and print the difference as output.
for example you can see row #1 in Master data ACT is 'D' and where as in second sheet its changes to '$'
so I want output something like related complete row which says its changes from 'D' to '$'
this seems very complicated to me as I'm at beginning stage of python and pandas.
I tried using loops but unable to execute also if I use too much loop that's not the pandas way I believe
here is my code:
import pandas as pd df1 = pd.read_excel("Master Database.xlsx") df2 = pd.read_excel("CHCGILDTO3P_0501.01D.xlsx") d1_act = df1['ACT'] d2_act = df2['ACT'] for index1, row1 in df1.iterrows(): for index2, row2 in df2.iterrows(): if(row1['LOC'],row1['HECI'],row1['RR']) ==(row2['LOC'],row2['HECI'],row2['RR']): for x in d1_act and y in d2_act: #print(x,y) if x != y: print (x, y) # not getting how to print complete respective row else: pass else: pass
I want ouput like:
M3MSA0S1RA 1A1 IE $ CL/HFFS/688898 /LGT D 72118 CHCGILDTO3P 0501.01D
changes from 'D to '$'
please assist ! thank you in advance!
merge duplicate cells of a column
My Current excel looks like:
---------------- | Type | Val | |--------------| | A | 1 | |--------------| | A | 2 | |--------------| | B | 3 | |--------------| | B | 4 | |--------------| | B | 5 | |--------------| | C | 6 |
This is the required excel:
---------------------- | Type | Val | Sum | |--------------------| | A | 1 | 3 | | |------| | | | 2 | | |--------------------| | B | 3 | 12 | | |------| | | | 4 | | | |------| | | | 5 | | |--------------------| | C | 6 | 6 | ----------------------
Is it possible in python using pandas or any other module?
To get password that is used to protect sheet
Is there any possibility to get the password that is used to protect sheet? I have a protected a sheet with password by the code. Now I need to retrieve that password with the help of the code? How do we retrieve that in apache-poi.
sheet.getCTWorksheet().getSheetProtection().getPassword(); This is giving me some "[B@da4eab14" value. How to convert that to string? Do we have any other way to get the password?
How to link a ComboBox value with a different value and put that value into a cell in a worksheet
I have an excel workbook. Within that work book I have one sheet named Classes and I have another sheet called Homework. In the Classes sheet I have the different classes in column A I also have the class hours in column B. In the Homework worksheet I have the homework assignment names in column A. I have a UserForm that has a TextBox, ComboBox and a command button. In the TextBox I am supposed to type the assignment name. The combobox is populated with the classes that are in column A of the Classes worksheet. I want to be able to type in the homework assignment and select a class within the combobox and then click the command button and this will put the homework assignment in column A of the Homework worksheet and then put the class hour (Ex. 1,2,3,4,5 not the class name) in column B of the Homework worksheet. I dont know how to link the classes that are populated in the Combobox with the class hours so that when I choose a class it will then put the class hour into a cell within the Homework worksheet.
How to prevent UiPath from getting stuck in Call Macro Stage?
I am calling a macro using call macro in UiPath Workflow. The concern is some time one of the excels that the macro handles will freeze forever, Causing the macro execution to hang and the process will get stuck in Call macro forever.
Is there some way I can modify the UiPath workflow so that, if the Call macro activity is not completed within 15 minutes, the bot throws an exception?
Scraping web with url in milliseconds Unix
The web in which I browse has several pages. I want to click on those elements or play with urls and thus be able to copy the data. The initial URL ends at = 1 & playerType = ALL & ts = 1558502019375, in my code there is a loop that is supposed to go page after page to get the data but I can not get it done.
Sub UPDATE_DATA_MLB() Application.ScreenUpdating = False 'dimension (set aside memory for) our variables Dim objIE As InternetExplorer Dim ele As Object Dim y As Integer Dim EstaPagina As Byte Dim EstaURL As String 'Página inicial EstaPagina = 1 'we will output data to excel, starting on row 1 y = 1 EstaURL = "http://mlb.mlb.com/stats/sortable.jsp#elem=%5Bobject+Object%5D&tab_level=child&click_text=Sortable+Player+hitting&game_type='R'&season=2018&season_type=ANY&league_code='MLB'§ionType=sp&statType=hitting&page=1&playerType=ALL&ts=1558502019375" '&ts=1526432697176" 'start a new browser instance Set objIE = New InternetExplorer 'make browser visible objIE.Visible = False Do Until EstaPagina = 255 'navigate to page with needed data objIE.navigate EstaURL & EstaPagina 'wait for page to load Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop 'If UCase(Left(EstaURL, 211) & "1") = UCase(Left(objIE.LocationURL, (211 + Len(EstaPagina)))) And y > 1 Then Exit Do 'look at all the 'tr' elements in the 'table' with id 'myTable', 'and evaluate each, one at a time, using 'ele' variable For Each ele In objIE.document.getElementById("datagrid").getElementsByTagName("tr") 'show the text content of 'tr' element being looked at 'Debug.Print ele.textContent 'each 'tr' (table row) element contains 4 children ('td') elements 'put text of 1st 'td' in col A Sheets("Sheet1").Range("A" & y).Value = ele.Children(0).textContent 'put text of 2nd 'td' in col B Sheets("Sheet1").Range("B" & y).Value = ele.Children(1).textContent 'put text of 3rd 'td' in col C Sheets("Sheet1").Range("C" & y).Value = ele.Children(2).textContent 'put text of 4th 'td' in col D Sheets("Sheet1").Range("D" & y).Value = ele.Children(5).textContent 'put text of 4th 'td' in col f Sheets("Sheet1").Range("E" & y).Value = ele.Children(22).textContent 'increment row counter by 1 y = y + 1 Next EstaPagina = EstaPagina + 1 Loop lobjIE.Quit Set objIE = Nothing Set ele = Nothing Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _ Header:=xlNo Application.ScreenUpdating = True MsgBox "Volcado terminado", vbInformation Range("A1").Select 'save the Excel workbook ActiveWorkbook.Save End Sub ```
VBA: Loop through an array, find how many values are matching and display the result as percentage in a cell
- What I have so far
The code below, which I got from my original request here: Finding if a cell values (delimited by comma) are all existing in a defined table
Option Explicit Sub LE_Checker() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayStatusBar = False Application.EnableEvents = False Dim RunValidation As Integer RunValidation = MsgBox("Are you sure?", vbYesNo, "Checking LE Matches") If RunValidation = vbYes Then Dim ws1 As Worksheet, ws2 As Worksheet Dim LastRowA As Long, i As Long, y As Long Dim arr As Variant Dim CountryCode As String Dim rng As Range, SearchRange As Range, FindPosition As Range Dim Appears As Boolean Dim Suppliers As Long 'Set worksheets on variables With ThisWorkbook Set ws1 = .Worksheets("Sheet1") Set ws2 = .Worksheets("Sheet1") ' For testing purpose I'm gonna use the same sheet End With 'Set the range to search in for country codes Set SearchRange = ws2.Range("I2:K2") With ws1 'Find the last row of Column A Sheet1 LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row ' Start loop from row 2 to last row Sheet1 For i = 2 To LastRowA 'Criteria needed: Content Groups: not empty / Region: EUROPE / Old Supplier: No (case sensitive) If .Range("A" & i).Value <> "" And .Range("C" & i).Value = "EUROPE" And .Range("D" & i).Value = "No" Then CountryCode = .Range("B" & i).Value 'In which column is the country code found Set FindPosition = SearchRange.Find(What:=CountryCode, LookIn:=xlValues, LookAt:=xlWhole) 'If country code exists If Not FindPosition Is Nothing Then 'Set the range to search for the groups in the column where the code is header Set rng = ws2.Range(ws2.Cells(2, FindPosition.Column), ws2.Cells(ws2.Cells(ws2.Rows.Count, FindPosition.Column).End(xlUp).Row, FindPosition.Column)) 'Split the string with comma and assing it on arr arr = Split(.Range("A" & i).Value) Appears = False 'Loop the arr For y = LBound(arr) To UBound(arr) 'Check if the arr(y) start from C as all code start from C If Left(arr(y), 1) = "C" Then 'Count how many times the arr(y) with out the comma appears in the rng If Application.WorksheetFunction.CountIf(rng, Replace(arr(y), ",", "")) > 0 Then 'If appears the variable Appears is true Appears = True Else 'If does not appear the variable Appears is False & Exit the loop Appears = False Exit For End If End If Next y 'Check Appears variable status and import value in Column C If Appears = True Then .Range("E" & i).Value = "Yes" Suppliers = Suppliers + 1 Else .Range("E" & i).Value = "No" End If End If End If Next i End With MsgBox "Done!" & Chr(13) & "Found " & Suppliers & " suppliers" End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.DisplayStatusBar = True Application.EnableEvents = True End Sub
The code will loop through a cell's values and find if all of them match the values defined in a table for the country code specified. If all codes match what's in the table = YES, otherwise = NO.
Like in the picture below:
- What I need:
An extension of this code, basically to check in that array loop (column A), in addition to what it does now, how many values are matching. Then return it in next column (F) as percentage (I need to identify the partial ones that have at least 50% codes in)
For example: If a supplier has 2/3 codes matching the country he's from, then show in column F 66.6%, if a supplier has 1/4 codes matching show 25% and so on.
- Expected result:
Google Map Scrapping
I have a list of companies and I want scrap all the companies address from google map using vba. I have tried to do it with some of the codes of vba but still its not working. Can anyone guide me please. I will really appreciate you for this. You can contact me @ firstname.lastname@example.org
Find With VBA DOCX File in Folder and Add It To eMail
I created code that is searching certain files inside a folder with dynamic path, then add it to an email and sendes it. I want that in every email I send inside the loop to will be specific DOCX file too. but I dont want to search for specific name, i want that it just will find the only DOCX in this folder and add it as an attachment.
i n my code its not working well, the part with the DOCS file. Here is the relevant part of the code:
'*****************************************EMail from here Dim outlookApp As Outlook.Application Dim myMail As Outlook.MailItem Dim source_file, to_emails As String Dim WordFile As String Dim i, j As Integer '---------here i find the file, I tried that it will search for the only docx in the folder WordFile = Dir(path & "\*.docx") Do While Len(WordFile) > 0 WordFile = Dir Wend For i = 2 To LastManaRow 'find manager in mailing list With ListSheet ManaNumber = .Cells(i, 1).Value End With With EmailWS j = .Range(.Cells(2, 1), .Cells(LastMailRow, 1)).Find(ManaNumber, , xlValues, xlWhole).Row to_emails = .Cells(j, 3).Value End With Set outlookApp = New Outlook.Application Set myMail = outlookApp.CreateItem(olMailItem) FileToSendName = ManaNumber & " " & PartFileName source_file = path & "\" & FileToSendName & ".xlsx" '-----here i give the excel file variable name MainWB.Worksheets("Activation").Cells(1, 5) = WordFile MsgWS.Cells(1, 5) = FileToSendName MsgWS.Range("H1") = source_file With myMail .Attachments.Add source_file .Attachments.Add WordFile ' ------ERROR because the variable has no value .To = to_emails .Subject = MainWB.Worksheets("MSG").Range("B1").Value .Body = BodyMsg .Send End With Next i