Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Icons for attachments added via VBA
Hi, I have a spreadsheet which I have protected to stop people tampering with it. As a result in order to add attachments to it I have included a button and some VBA code. Now this all works fine, except the attachment doesn't pick up the icon of the associated application (eg: a word doc, etc), any thoughts on this would be appreciated, below is the code, thanks: Private Sub cmdInsert_Click() Dim strFilename As String Dim varNumber As Variant Dim strPosition As String strFilename = Application.GetOpenFilename(, , "Add Attachment") If strFilename < "False" Then varNumber = InputBox("Enter attachment position number (1 to 10)", "Add Attachement") If varNumber = 1 And varNumber <= 10 Then strPosition = "D" & varNumber + 5 Range(strPosition).Select ActiveSheet.OLEObjects.Add(Filename:=strFilename, Link:=False, _ DisplayAsIcon:=True, *IconFileName:=strFilename*, IconIndex:=0, _ IconLabel:=strFilename.Select End If End IfEnd Sub -- Nick-B ------------------------------------------------------------------------ Nick-B's Profile: http://www.excelforum.com/member.php...o&userid=37267 View this thread: http://www.excelforum.com/showthread...hreadid=569810 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Icons for attachments added via VBA
Thanks Gary, I did some similar tests this afternoon and was coming to the same conclusion as you. My concern here is that strings like "{00020409-78E1-11D2-B60F-006097C998E7}" in the path will not be the same across different machines, which is essential for me. Looking at this further this evening it looks like you can simply specify the executable name without any path (eg:"excel.exe" for a spreadsheet) and get the default icon for it, which avoids having to specify the installer path. Unfortunately I will still have to have a switch statement for at least the main types of file. Thanks again for your help. Nick -- Nick-B ------------------------------------------------------------------------ Nick-B's Profile: http://www.excelforum.com/member.php...o&userid=37267 View this thread: http://www.excelforum.com/showthread...hreadid=569810 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Icons for attachments added via VBA
Does this help:
http://www.developerfusion.co.uk/show/2982/2/ NickHK "Nick-B" wrote in message ... Thanks Gary, I did some similar tests this afternoon and was coming to the same conclusion as you. My concern here is that strings like "{00020409-78E1-11D2-B60F-006097C998E7}" in the path will not be the same across different machines, which is essential for me. Looking at this further this evening it looks like you can simply specify the executable name without any path (eg:"excel.exe" for a spreadsheet) and get the default icon for it, which avoids having to specify the installer path. Unfortunately I will still have to have a switch statement for at least the main types of file. Thanks again for your help. Nick -- Nick-B ------------------------------------------------------------------------ Nick-B's Profile: http://www.excelforum.com/member.php...o&userid=37267 View this thread: http://www.excelforum.com/showthread...hreadid=569810 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Icons for attachments added via VBA
Certainly does help, thanks for this. With this website and http://www.arcatapet.net/vbregget.cfm (note: all except the "GetIcon" function were taken from this website) I've come up with the following which I'm going to integrate into my spreadsheet (FYI the last command chops off the ",<number" from the end of the registry entry). Declare Function RegCloseKey& Lib "advapi32.dll" (ByVal hKey&) Declare Function RegOpenKeyExA& Lib "advapi32.dll" (ByVal hKey&, ByVal lpszSubKey$, dwOptions&, ByVal samDesired&, lpHKey&) Declare Function RegQueryValueExA& Lib "advapi32.dll" (ByVal hKey&, ByVal lpszValueName$, ByVal lpdwRes&, lpdwType&, ByVal lpDataBuff$, nSize&) Declare Function RegQueryValueEx& Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey&, ByVal lpszValueName$, ByVal lpdwRes&, lpdwType&, lpDataBuff&, nSize&) Const HKEY_CLASSES_ROOT = &H80000000 Const HKEY_CURRENT_USER = &H80000001 Const HKEY_LOCAL_MACHINE = &H80000002 Const HKEY_USERS = &H80000003 Const ERROR_SUCCESS = 0& Const REG_SZ = 1& ' Unicode nul terminated string Const REG_DWORD = 4& ' 32-bit number Const KEY_QUERY_VALUE = &H1& Const KEY_SET_VALUE = &H2& Const KEY_CREATE_SUB_KEY = &H4& Const KEY_ENUMERATE_SUB_KEYS = &H8& Const KEY_NOTIFY = &H10& Const KEY_CREATE_LINK = &H20& Const READ_CONTROL = &H20000 Const WRITE_DAC = &H40000 Const WRITE_OWNER = &H80000 Const SYNCHRONIZE = &H100000 Const STANDARD_RIGHTS_REQUIRED = &HF0000 Const STANDARD_RIGHTS_READ = READ_CONTROL Const STANDARD_RIGHTS_WRITE = READ_CONTROL Const STANDARD_RIGHTS_EXECUTE = READ_CONTROL Const KEY_READ = STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY Const KEY_WRITE = STANDARD_RIGHTS_WRITE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Const KEY_EXECUTE = KEY_READ Function RegGetValue$(MainKey&, SubKey$, value$) ' MainKey must be one of the Publicly declared HKEY constants. Dim sKeyType& 'to return the key type. This function expects REG_SZ or REG_DWORD Dim ret& 'returned by registry functions, should be 0& Dim lpHKey& 'return handle to opened key Dim lpcbData& 'length of data in returned string Dim ReturnedString$ 'returned string value Dim ReturnedLong& 'returned long value If MainKey = &H80000000 And MainKey <= &H80000006 Then ' Open key ret = RegOpenKeyExA(MainKey, SubKey, 0&, KEY_READ, lpHKey) If ret < ERROR_SUCCESS Then RegGetValue = "" Exit Function 'No key open, so leave End If ' Set up buffer for data to be returned in. ' Adjust next value for larger buffers. lpcbData = 255 ReturnedString = Space$(lpcbData) ' Read key ret& = RegQueryValueExA(lpHKey, value, ByVal 0&, sKeyType, ReturnedString, lpcbData) If ret < ERROR_SUCCESS Then RegGetValue = "" 'Value probably doesn't exist Else If sKeyType = REG_DWORD Then ret = RegQueryValueEx(lpHKey, value, ByVal 0&, sKeyType, ReturnedLong, 4) If ret = ERROR_SUCCESS Then RegGetValue = CStr(ReturnedLong) Else RegGetValue = Left$(ReturnedString, lpcbData - 1) End If End If ' Always close opened keys. ret = RegCloseKey(lpHKey) End If End Function Function GetIcon(strExtension As String) As String GetIcon = RegGetValue$(HKEY_CLASSES_ROOT, RegGetValue$(HKEY_CLASSES_ROOT, strExtension, "") & "\DefaultIcon", "") If InStr(GetIcon, ",") 0 Then GetIcon = Left(GetIcon, InStr(GetIcon, ",") - 1) End Function -- Nick-B ------------------------------------------------------------------------ Nick-B's Profile: http://www.excelforum.com/member.php...o&userid=37267 View this thread: http://www.excelforum.com/showthread...hreadid=569810 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
attachments - | Setting up and Configuration of Excel | |||
attachments | Excel Discussion (Misc queries) | |||
E-mail Attachments | Excel Programming | |||
Attachments | Excel Worksheet Functions | |||
Attachments | Excel Programming |