Code Snippets VBA

Short texts VBA 🧑‍💻 Code snippets VBA ✍️ Reading lists VBA 👀

processing all excel files in current dir

Sub OpenFilesVBA() Dim wb As Workbook Dim strFolder As String Dim strFil As String Application.ScreenUpdating = True strFolder = ThisWorkbook.Path & “\” strFil = Dir(strFolder & “\*.xls*”) Do While strFil vbNullString If strFil ThisWorkbook.Name Then Set wb = Workbooks.Open(strFolder & “\” & strFil) ‘===== PROCESSING BEGINS ===== wb.Activate Range(“A:A,H:H,I:I,O:O”).Select Selection.Delete Shift:=xlToLeft Rows(“1:1”).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove […]

delete_attachments_from_selected_mails.vba

‘ https://docs.microsoft.com/ja-jp/office/vba/api/outlook.application Sub delete_attachments_from_selected_mails() Dim items As Selection Dim mail As MailItem Dim atts As Attachments Dim i As Integer Set items = ActiveExplorer.Selection For i = 1 To items.Count If TypeName(items.Item(i)) = “MailItem” Then Set mail = items.Item(i) Set atts = mail.Attachments If atts.Count > 0 Then Do atts.Remove 1 Loop Until atts.Count < […]

Outlook VBA Forward Spam to [email protected]

Attribute VB_Name = “ForwardSpamToDoNotPay” Sub ForwardSpamToDoNotPay() ‘ dim outlook folders Dim objNS As Outlook.NameSpace Dim accountFolder, parentFolder, spamFolder, sentFolder As Outlook.MAPIFolder ‘ set folder name constants parentFolderName = “[Gmail]” spamFolderName = “Spam” sentFolderName = “Sent Mail” ‘ initialize link to first account folder Set objNS = GetNamespace(“MAPI”) Set accountFolder = objNS.Folders.GetFirst ‘ for each account […]

VBA code for calling Assembly.Load using raw vtable lookups for the IUnknown

‘ Need to add project references to C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscoree.tlb and mscorlib.tlb Private Declare PtrSafe Function DispCallFunc Lib “oleaut32.dll” (ByVal pv As LongPtr, ByVal ov As LongPtr, ByVal cc As Integer, ByVal vr As Integer, ByVal ca As Long, ByRef pr As Integer, ByRef pg As LongPtr, ByRef par As Variant) As Long Private Declare PtrSafe Sub […]

VBA/EXCEL – Conversione di data e ora in Unix Time

Public Function UNIXTIME(data, ora) If Len(ora) = 0 Then ora = “00:00:00” End If If Len(data) = 0 Then data = “01/01/1970” End If UNIXTIME = DateDiff(“s”, DateSerial(1970, 1, 1), data) + DateDiff(“s”, “00:00:00”, ora) End Function