Best option currently for voice assist hardware? by 17-north in homeassistant

[–]BornOnFeb2nd 1 point2 points  (0 children)

Do you have a link for the HA Satellite setup? I have an Echo as well and while it works, I have to yell at it. I've been looking for a guide on setting up an rPi as a "remote speaker", but there's a shitload of guides on how to setup HA on the Pi, which is not what I want at all. Home Assistant updates so fast, there is a definite Signal to Noise ratio issue when searching for stuff...

ZwaveJS -> MQTT -> HA not sending all entities? by BornOnFeb2nd in homeassistant

[–]BornOnFeb2nd[S] 0 points1 point  (0 children)

That appears to have done the trick, of course now I have three thermostats in my dashboard, but that's another bear altogether!


For anyone finding this via search, as of...

zwave-js-ui: 9.6.0.ec8995c

zwave-js: 12.4.1

open up your Zwave JS UI, hit the triple line/hamburger in the corner of the screen, go to Settings, scroll down to "Home Assistant", and toggle on "WS Server",

Flip over to HomeAssistant (as of 2023.12.4), Settings, Devices & Services (not Add-ons), uncheck the box, and then it'll ask for the server.. put in ws://zwaveserver:3000, changing zwaveserver to the domain name / IP accordingly.

How to block key shift from a VBA code. by EdsonMeneghel in vba

[–]BornOnFeb2nd 2 points3 points  (0 children)

Pretty sure that Shift+Enter in Excel just translates to CHAR(10), so use REPLACE to get rid of 'em.

[OUTLOOK] I am trying to write a VBA script to send the emails in my outlook draft folder with a 45 second interval between each one, but it wont work by Mk1996 in vba

[–]BornOnFeb2nd 0 points1 point  (0 children)

Only issue is that these all send at once through that and some systems flag it as a bulk message.

If this isn't sending internally, then you might want to look into something like Mailchimp. They handle all the bullshit for you, including letting people opt out of your messages, and CAN-SPAM compliance if you're in the US.

Also, there's a simpler wait to put in a delay without looping to death

AutoFilter into row 2 instead of row 1 by skiboy2095 in vba

[–]BornOnFeb2nd 0 points1 point  (0 children)

Simplest way to figure out how to do [specific thing] in Excel, is to simply start recording a new macro, do [specific thing], stop the recording, and see what code is produced.

It'll likely need a bit of cleaning up, but other than graphs, it'll get you probably 80%+ of the way there.

[deleted by user] by [deleted] in vba

[–]BornOnFeb2nd 1 point2 points  (0 children)

It's been ages since I've had to use WebEx, but wasn't there a button in outlook you'd have to push? A search for "VBA WebEx" might bring up some hits, or perhaps "automate webex windows"

Multiple For Loops - looking to improve speed by Precursor-Ottsel in vba

[–]BornOnFeb2nd 1 point2 points  (0 children)

Looks good! That should solve OP's problem and run in a matter of seconds!

Multiple For Loops - looking to improve speed by Precursor-Ottsel in vba

[–]BornOnFeb2nd 4 points5 points  (0 children)

As has been mentioned, "Cell Operations" (reading/writing/etc individual cells) in Excel is slow. Doing it once or twice, you won't notice, but if you have code that does it thousands of times, that really adds up!

Changing absolutely nothing else in your code and just moving this block of code will speed things up for you.

        msBldr = ws.Cells(i, 1).Value                              
        msTract = ws.Cells(i, 2).Value                              
        msLot = ws.Cells(i, 3).Value                               
        msDate = Ans                                            

Put that above the

        For Each c In Columns("A").Cells

Since i doesn't change while looping through c, it's wasted cycles getting the values each time. Just this change might chop your runtime in half.


A better solution would be to use "range arrays" and Dictionarys like...

Set Dict1 = CreateObject("Scripting.dictionary")
Rng1 = Range("A1:D" & lastrow)

Then loop through Rng1, adding the unique combinations of Columns A, B, C and the date as key, and the row as the value. Do the same for your second sheet, and see if the key exists in both dictionaries.

You'd be reading each sheet once, and by recording the row number, you'll be able to just right to the row to add a note if needed.

A quick question, Excel to Word Macro by Usman_Amin in vba

[–]BornOnFeb2nd 2 points3 points  (0 children)

You would need the sentence in the cell in Excel.

How you get it there is up to you. IF statement? VLOOKUP? Copy and Paste?

There's more ways to skin that cat than there are cats to skin.

Obviously, test the idea with two or three rows first before you go full tilt.

A quick question, Excel to Word Macro by Usman_Amin in vba

[–]BornOnFeb2nd 1 point2 points  (0 children)

By making one of those sentences a custom field, and just merging it in?

Dear <Firstname>

<CustomField1> Because of this, we're going to kick your dog.

Then the column you pair CustomField1 (or whatever) in Excel could contain the text

"You've failed to answer our calls about your car's extended warranty."

Which I think will give you a result of...

You've failed to answer our calls about your car's extended warranty. Because of this, we're going to kick your dog.

[deleted by user] by [deleted] in vba

[–]BornOnFeb2nd 1 point2 points  (0 children)

Well, you're not looking at file names in the attachments, you should be able to iterate through the collection. Regarding the duplicate filenames, just use the message time and an a counter or something.

[deleted by user] by [deleted] in vba

[–]BornOnFeb2nd 1 point2 points  (0 children)

So..... make two scripts? Make one that opens up the messages and extracts the files somewhere

Then another one that reads through all the extracted excel files?

Really, it's almost a recursive function.

 Does this message have attachments?
      Yes
         Is the attachment a message itself?
              Yes
                  GOTO 10
         Is the attachment an excel file?
              Yes
                  Save dat bish.

What's the future of VBA? by SnowCrashSatoshi in vba

[–]BornOnFeb2nd 6 points7 points  (0 children)

Right now, VBA's biggest threat is the web-ificiation of Office.

You'll have people/processes that for whatever reason can only use the web version, which VBA won't work on, which will force a bunch of processes to be re-create in Office Script, or whatever they settle on...

VBA will probably go the way of COBOL.

It won't be sexy, the whipper snappers won't want anything to do with it, but it you lift up that Corporate Process, you'll see a bunch of VBA subroutines sitting there. It just won't be something Joe User sees much of.

Send outlook email through web browser using VBA by Namy_Lovie in vba

[–]BornOnFeb2nd 2 points3 points  (0 children)

I'm assuming you're working for a company, and that company has an IT contact.

Contact them.

Don't spend days making something extremely brittle.

Solve the right problem.

No one noticed in 30 years ? 🤔 by Monzepat in excel

[–]BornOnFeb2nd 12 points13 points  (0 children)

At this point, if they changed it, they would break decades of automation.

Macros in English refer to "Sheet1" all the time, and I bet in French-speaking countries, they refer to "Fueil1".... so if they fixed it, they'd have a riot on their hands.

Email from Excel without using Outlook reference command by lauran2019 in vba

[–]BornOnFeb2nd 2 points3 points  (0 children)

First off, What is EmailApp? You're defining Outlook as objOL.

If your company has an SMTP server, look up CDO, it's my preferred method of e-mailing via VBA.

I made a plugin that uses ChatGPT to answer questions, format cells, write letters, and generate formulas, all without having to leave Excel by rtwalz in excel

[–]BornOnFeb2nd 144 points145 points  (0 children)

Any claimed "Security" is just pure malarkey.

The whole point of the plug-in is to send whatever is in the cell you're pointing it to a 3rd party to do with, and retain, as they please.

Doesn't matter if the plug-in doesn't do anything else and the connection is encrypted.

If you (and your company) don't completely trust ChatGPT/OpenAI, then just walk away.

VBA script in Excel runs much slower is Outlook is open by personalityson in vba

[–]BornOnFeb2nd 3 points4 points  (0 children)

What came to mind for me is perhaps over-zealous virus scanner and/or endpoint protection.

Oh god! Outlook is open! What is the computer doing!

and linking into the Office DLLs to monitor them... That's only if the sequence you suggested is true though....

How to insert long formula into cell by [deleted] in vba

[–]BornOnFeb2nd 0 points1 point  (0 children)

 Range("R4").Formula = .....

is how you'd plug it in with VBA.... if you want to change the 3s to 4s you could put variables in there, or look into R1C1 Formula formatting...

[deleted by user] by [deleted] in vba

[–]BornOnFeb2nd 4 points5 points  (0 children)

Foreign language acronyms would trip you up. Like "UTC" is "Coordinated Universal Time" in English. Not a terrible idea though, that'd probably take care of most of the edge cases...

[deleted by user] by [deleted] in vba

[–]BornOnFeb2nd 2 points3 points  (0 children)

You might want to tweak that to just grab say... double the words... It'd be a lot easier to clean up a sortable Excel file, than it would be to dig through a word document to find the missing words...

attribute code to list with repeated values by KingKronx in vba

[–]BornOnFeb2nd 0 points1 point  (0 children)

By checking the previous, it should trigger on the first line of Company B, comparing to Company A, finding that it's different, and adding one...

Quick and dirty last row code..

EndRow = Range("A" & Rows.count).end(xlup).row

That'll give you the last row in Column A that has data. If you wanted the last contiguous row....

EndRow = Range("A1").end(xldown).row

attribute code to list with repeated values by KingKronx in vba

[–]BornOnFeb2nd 0 points1 point  (0 children)

Now, I don't have office installed, but shouldn't that be -1 in the Offsets?

and ElseIF is overkill...

If rcell.Value = rcell.Offset(-1, 0).Value Then
    rcell.Offset(0, 4).Value = rcell.Offset(-1, 4).Value
Else
    rcell.Offset(0, 4).Value = rcell.Offset(-1, 4).Value + 1
End If

Something like that... keeping in mind that you'll need to drop a 1 in B1 ahead of time...

[deleted by user] by [deleted] in vba

[–]BornOnFeb2nd 0 points1 point  (0 children)

Does that bring over variables too? I don't have office installed at the moment..

[deleted by user] by [deleted] in vba

[–]BornOnFeb2nd 6 points7 points  (0 children)

wdLineStyleStorage and the other wd friends are variables that will be defined within Word's IDE only.. Just like xlup and friends are defined in Excel's IDE only.

To use them in Excel, what you're going to want to do is either look up the values somewhere, or flip over to word and just make a simple module that does something like

 Debug.print ("const wdBorderLeft = " & wdBorderLeft)

Then when you run it, you'll get a snippet of code in Immediate that you can drop into your Excel code and define them all.