Excel VBA setting variable for power query from textbox

See also questions close to this topic

• .Body & .HTMLBody Indent and Default Signature Issue

  I've set up a macro to send an e-mail through Outlook.

  .Body is read from a cell inside the file with indents. Since the value will change depending on the usage, I need to reference that cell for the body.

  However, there rises 2 issues using .HTMLbody I lose indents which are constructed with CHAR(10) but I keep the default HTML signature.

  When using just .BODY indents are displayed are correctly however the default signature is not constructed as HTML and I lose the images.

  How should I go about fixing this issue?

  My code:

  sig = .HTMLBody
  body = xlSht.Range("B4").Value
      .To = xlSht.Range("B2").Value
      .CC = ""
      .Subject = xlSht.Range("B1").Value
      .body = body & sig
      .Display
  

  I'd really appreciate your assistance.

  Thanks.
• VBA: Creating a class property that is an array of dictionaries

  In Microsoft Excel VBA I need to create a class that has two properties, "Name" and "Holdings". "Name" is just a string so the code for that is easy. But I need "Holdings" to be a variable length array containing dictionary objects. So this is what I wrote:

  Private mName As String
  Private mHoldings() As Scripting.Dictionary
  
  Public Property Let Name(vName As String)
        mName = vName
  End Property
   
  Public Property Get Name() As String
        Name = mName
  End Property
   
  Public Property Set Holdings(vHoldings() As Scripting.Dictionary)
        Set mHoldings = vHoldings
  End Property
   
  Public Property Get Holdings() As Scripting.Dictionary
        Set Holdings = mHoldings
  End Property
  

  When I try to compile it gives me this error: "Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalide Set final parameter.

  What I doing wrong?
• Yahoo Finance no longer returns VBA cookie request for .getResponseHeader("Set-Cookie")

  The following Excel VBA code segment has worked for years, but stopped working around 28 Apr 2022. I receive the responseText, but the .getResponseHeader("Set-Cookie") returns null.

  Set httpReq = New WinHttp.WinHttpRequest
  
  DownloadURL = "https://finance.yahoo.com/lookup?s=" & stockSymbol
  With httpReq
      .Open "GET", DownloadURL, False
      .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
      .Send
      .waitForResponse
      response = .responseText
      cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
  End With
  
• compare two separate data sets

  is it possible to compare particular values from a table/report from power bi data set with another table from excel sheet?

  In the power bi data set i have the organization data about all employees, and in my excel i have the data for 10 particular individuals. (something like: Name, Grade, Role, Team, Joining Date, Departure Date)

  What i now do is to create in power bi the report with just the 10 people, export the data and then i compare that table with my local one, if there are any mismatches, like if the grades changed or they move to another team/department or some thing, in my local file i have some custom/aditional columns that arent in the powerbi dataset (Name, Grade, Role, Team, Joining Date, Departure Date, Comment, Skills, Future team)

  i could not load a data set from the excel file, and also from the company powerbi dataset, in the same report/page
• Format date with dax

  I have dates like:

  

  I need to do two transformations:

  Convert it into datetime

  field date and time in the initial data shows in GMT. Necessary translate into our local time as well as our working hours. Local time = time in Kyiv which also takes into account daylight saving time. Working hours = because we work 24/7, we take into account the beginning of each day not from 00:00, and from 7:25 am. That is, if the purchase was made on February 1 at 7:15, it has enroll until January 31. For the second point I used this function, how to midify it to respond to the requirement please?

  let
  Source = (datetimecolumn as datetime) =>
  
  let
  date = DateTime.Date(datetimecolumn),
  time = DateTime.Time(datetimecolumn),
  lastSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
  lastSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
  
  isSummerTime = (date = lastSundayOfMarch and time >= #time(2,0,0))
  or
  (date > lastSundayOfMarch and date < lastSundayOfOctober)
  or
  (date = lastSundayOfOctober and time <= #time(2,0,0)),
  timeZone = 1 + Number.From(isSummerTime),
  CET =
  DateTime.From(date)
  + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
  + #duration(0, timeZone, 0, 0)
  in
  CET
  in
  Source 2e
  
• How to get record before and after in forall loop powerapps

  I am trying to run a function that returns a table with a corresponding date index and date text. This is so that I can display the information in a tutorial screen. However, I am confused on how to access to the previous record. Here is my pseudocode:

  ForAll( Tweets (sorted by crf1d_date_index),
          If(the record IS NOT the LAST record,
               If('crf1d_date_index' != 'crf1d_date_index' of the NEXT record,
                       { 
                             Step: crf1d_date_index, 
                             Text: crf1d_tweet_time 
                       }
                 )
             )
  
          If(the record IS the LAST record,
               If('crf1d_date_index' != 'crf1d_date_index' of the PREVIOUS record),
                       { 
                             Step: crf1d_date_index, 
                             Text: crf1d_tweet_time 
                       }
                 )
  
  )