vba – w3toppers.com

Search structured text in Outlook body

finalSubj = ParseTextLinePair(initialBod, “Mailbox:”) See “Listing 17.1. Extract data from a structured text block”. https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/dd492012(v=office.12) Function ParseTextLinePair(strSource As String, strLabel As String) Dim intLocLabel As Integer Dim intLocCRLF As Integer Dim intLenLabel As Integer Dim strText As String ‘ locate the label in the source text intLocLabel = InStr(strSource, strLabel) intLenLabel = Len(strLabel) If intLocLabel … Read more

mac excel vba loop : from list & then export as pdf

I’m not a MAC user so I may be missing some restrictions I don’t have in Windows OS, but you may be after something like follows: Option Explicit Sub Pdfexportmacro() Dim rCell As Range, rRng As Range ‘Student numbers in cells A7:A160 Set rRng = Worksheets(“studentlist”).Range(“A7:A160”) ‘<–| set your “students” range With Worksheets(“Feedback”) ‘<–| reference … Read more

Difference between Thisworkbook.name and Activeworkbook.name in VBA

Activeworkbook.name is used to get the name of the active workbook from n different number of opened workbooks. Thisworkbook.name is used to get the name of the workbook in which the code is written or stored in the module of that workbook. E.g if you are writing the code in the module or sheet of … Read more

How can I assign a Variant to a Variant in VBA?

In VBA, the only way to assign a Variant to a variable where you don’t know if it is an object or a primitive, is by passing it as a parameter. If you cannot refactor your code so that the v is passed as a parameter to a Sub, Function or Let Property (despite the … Read more

Exporting MS Access Forms and Class / Modules Recursively to text files?

You can also try this code. It will preserve the items’ filetypes (.bas, .cls, .frm) Remember to refer to / Check the Microsoft Visual Basic For Applications Extensibility Library in VBE > Tools > References Public Sub ExportAllCode() Dim c As VBComponent Dim Sfx As String For Each c In Application.VBE.VBProjects(1).VBComponents Select Case c.Type Case … Read more

Excel VBA to answer Internet Explorer 11 download prompt, in Windows 10?

Consider downloading historic data for shares via XMLHttpRequest instead of IE automation. In the example below share ISIN is specified (SE0001493776 for AAK), first request returns share id (SSE36273), and second request retrieves historic data by id, then shows it in notepad as text, and saves as csv file. Sub Test() Dim dToDate, dFromDate, aDataBinary, … Read more

How to create dynamic variable names VBA

A dictionary would probably help in this case, it’s designed for scripting, and while it won’t let you create “dynamic” variables, the dictionary’s items are dynamic, and can serve similar purpose as “variables”. Dim Teams as Object Set Teams = CreateObject(“Scripting.Dictionary”) For i = 1 To x Teams(i) = “some value” Next Later, to query … Read more

How to add items to a combobox in a form in excel VBA?

The method I prefer assigns an array of data to the combobox. Click on the body of your userform and change the “Click” event to “Initialize”. Now the combobox will fill upon the initializing of the userform. I hope this helps. Sub UserForm_Initialize() ComboBox1.List = Array(“1001”, “1002”, “1003”, “1004”, “1005”, “1006”, “1007”, “1008”, “1009”, “1010”) … Read more

Timing Delays in VBA

If you are in Excel VBA you can use the following. Application.Wait(Now + TimeValue(“0:00:01”)) (The time string should look like H:MM:SS.)

How to refer to Excel objects in Access VBA?

I dissent from both the answers. Don’t create a reference at all, but use late binding: Dim objExcelApp As Object Dim wb As Object Sub Initialize() Set objExcelApp = CreateObject(“Excel.Application”) End Sub Sub ProcessDataWorkbook() Set wb = objExcelApp.Workbooks.Open(“path to my workbook”) Dim ws As Object Set ws = wb.Sheets(1) ws.Cells(1, 1).Value = “Hello” ws.Cells(1, 2).Value … Read more