Code snippet to return IP and Hostname lookup in excel. Change the sheet name and ranges as needed. Can take some time to run on big lists! Function GetPingResult(Host) Dim objPing As Object Dim objStatus As Object Dim Result As String ‘ping the host Set objPing = GetObject(“winmgmts:{impersonationLevel=impersonate}”). _ ExecQuery(“Select * from Win32_PingStatus Where […]
Windows 10 Upgrade – Excel / Word not opening from emails Having issues opening excel or word documents after upgrading is the result of the trust center protected view being enabled. In Word, Excel and/or PowerPoint choose: File-> Options-> Trust Center-> Trust Center Settings…-> Protected View-> deselect option: Enable Protected View for Outlook attachments
Macros enabled but will not run in excel 2007 If you have password protected your workbook, this may cause macros to be disabled, even if you have enabled them in your security settings.
Picking up Excel Named Variables When defining a name within an Excel spreadsheet, you can pick it up using VBA as follows: [vb]ActiveWorkbook.Names(“your defined variable name”).Value[/vb] or set a named variable: [vb]ActiveWorkbook.Names.Add Name:=”bob”, RefersToR1C1:=”=Sheet1!R5C21:R8C21″ ActiveWorkbook.Names(“bob”).Comment = “”[/vb]
Password Protect Excel 2007 To set a password for the file in Excel 2007, click the Office button and choose Save As. Select the file type that you want (probably the first one: Excel Workbook), and that will launch the Save As dialog box. In the lower-left corner is a button labeled Tools. Click that […]
Resize Merged Excel Cells – VBA [vb] Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells ¬†¬†¬†¬†¬†¬†¬†¬† MrgeWdth […]