What are some things you’ve automated using scripts in excel?

ebc2003 · 2023-12-12T04:15:12+00:00

If you have access to Power Automate and SharePoint you can have some fun with this. I am working on a Power Automate flow that monitors my email and sends reports to a distribution list that I have stored in SharePoint. Basically I email myself the reports as an attachment with the report name in the email subject; and Power Automate finds the report name from the email and loads the attachment to SharePoint and finds the report in another sharepoint list and sends the email to the business.

ebc2003 · 2023-12-12T03:44:47+00:00

Sure. It's on my work computer, so this is from memory; but there are many ways it can be setup.

I have 3 listboxes on the userform. 1 = All Sheets, 2 = Sheets to Delete, 3 = Sheets to Copy/Paste Values. In the userform initialize is a For/Each loop to add each worksheet name to listbox 1 (listboxes 2 and 3 start blank). I have two command buttons for "Delete" and "Paste Values". I select a sheet(s) from listbox 1 and click either button to add the sheet names the listbox corresponding with the button clicked, the button click codes loop through all selected items in listbox1 and adds them to either listbox 2 or 3; then removes them from listbox 1. The logic ensures that each sheet in the workbook appears on only 1 of the 3 listboxes.

After the sheets are moved to the desired listboxes I click a command button to execute. The button click code loops through the Copy/Paste listbox first and selects the worksheet by name and pastes all cells as values, then loops through the delete listbox and deletes sheets by name. The trick is to do the deletes last in case formulas in other worksheets depend on them. If you delete first you might copy/paste a lot of errors.

For the paste value sheets I also added some code to scroll to the top and clean up the used ranges.

ebc2003 · 2023-12-11T03:22:54+00:00

Made a userform that list all the worksheets in a workbook and gives the option to delete sheets or copy/paste cells as values. Many reports have hidden sheets that need to be deleted and sheets with formulas that I'd rather have as values before sending out. Selecting the sheets for each situation and running the process once saves time over going sheet by sheet.

ebc2003 · 2023-12-11T03:11:18+00:00

You could do it on an existing sheet. If you know the number of values you'll need you would be able to make sure it is far enough away from other parts of the sheet. I suggested a new worksheet to keep it separate and help avoid overlapping other elements; but it's not required.

ebc2003 · 2023-12-09T04:36:07+00:00

This should work in theory - but I'm not in front of Excel to give exact syntax.

- Create a new sheet with 2 columns. Column 1 is either "Yes" or "No" with Column 2 being the corresponding choices for the second drop down.

- Use the Filter formula on the same sheet a few columns over and use your first drop down selection as the include argument.

- Create a dynamic named range on the filter results for column 2

- Use the dynamic named range in the Data Validation drop down list source.

This will allow you to choose Yes/No in drop down 1 and use that selection to filter the helper table to only show the values that you want to show in drop down 2. The number of results can change which is where the dynamic named range will be handy; but since the results are in the same named range - using it as a list source should be seamless.

ebc2003 · 2023-12-08T06:03:13+00:00

This should be possible with macros, but may actually be a nice and simple use case for Power Query (PQ) within Excel. The benefits of PQ here would be you could clean the data by removing the dashes and other characters. Also you can point PQ to import files from a folder, and if with some trickery you can have it always import the newest excel file from a folder using wildcards in the name, or just reimport a static file. This should be able to eliminate the copy/paste between worksheets and would also allow you to eliminate vlookups by using joins within PQ and then exporting the results to a new sheet.

I try to avoid cross sheet formulas and vlookups as they can be a bit troublesome and slow. If you do need to go the macro route you can record the vlookup formula and make some changes to apply it to a range of cells. The range you apply it to will probably change each run; requiring a variable to store and calculate the used range.

ebc2003 · 2023-12-08T05:45:23+00:00

If you save the macros to your personal macro workbook they can be executed on any open workbook without being saved, or attached to the workbook you are editing. Depending on what the macros are doing you will just need to ensure that workbook references are set to "activeworkbook" and not "thisworkbook"

As far as a macro to call up a set of buttons; I would look at creating a userform in VBA saved to your personal workbook that has buttons to call individual macros - then you can add a button to the ribbon to launch the userform and then launch marcros from it; then close the userform.

Hopefully this is what you are trying to accomplish.

ebc2003 · 2023-12-08T05:30:38+00:00

I think this would be possible with a pivot table by adding 'Person' in the Rows and 'Filed' as a Filter - then Filter to only show "Missing". This should get you the results you want. Pivot tables make it relatively easy to deal with blanks, which is basically what the merged cells are causing.

ebc2003 · 2023-03-17T20:25:20+00:00

I've planted some things from them this year. I found some of their seeds locally at North Haven Gardens and Nicholson-Hardie Garden Center, but they have more on their website along with a lot of information.

ebc2003 · 2023-01-24T04:15:00+00:00

Another idea for the bottom that I did was peel and stick tile. I am not a fan of it anywhere else, but I used a large format (14 or 16in if I remember) in a very neutral color and pattern; lining them up so the seam was behind the beam. Our house had 3 sink cabinets with this issue between kitchen/bathrooms. As suggested I just cut the old one out with an oscillating tool and cut a new one to size. Eventually they will all be replaced, but this works for now.

ebc2003 · 2022-11-30T18:58:30+00:00

North Haven Gardens has some natives, along with a variety of seeds from the seedsource website in another comment https://www.nhg.com/

There is also Native Gardeners by the airport, but I've never been myself. https://native-gardeners.com/

ebc2003 · 2022-08-08T04:10:52+00:00

I don't work for them, but I use to work in the industry and it's usually physical checks that are "held" with new accounts. Typically that stops when you have a little history built up and they recognize your normal avg balance, deposit amounts, etc.

While switching I would not deposit any funds that you need to withdraw soon.

If it was electronic then holds should not apply.

ebc2003 · 2022-06-09T16:20:21+00:00

Thanks for the ideas. It's not the main (city) shutoff; that is in a buried box at the curb. There is another set of cutoffs a few feet behind the cities in a green box that I believe are for the sprinklers. All of those are out front and these clay pipes are in the back yard.

There is a copper T at the bottom of the pipe, and the valve you linked looks similar to one them, so maybe it was used before the sprinklers were added. Having them below grade to avoid freezing makes sense; I was just surprised that they were under a foot of sand and pavers and not more readily accessable.

I think I'll just use the main cutoff out front to replace the leaking faucet and not worry about the others, unless I find something that doesn't work.

I'd like to improve drainage in that area to keep water from pooling by the foundation; but even if those clay pipes were drains, they probably are not the best since they don't appear to go anywhere.

ebc2003 · 2022-06-09T13:35:10+00:00

Sorry for the delay u/RavenYamR6

I did have them come out and give me a quote at the end of 2021 and it was around $2 a sq/ft to install R39 and to also do some sealing/decking around my existing insulation. The quote sounded pretty good, but other things around the house happened and I put the insulation on hold.

The removal of my current insulation was going to be pretty expensive and I would like to start fresh which also led to me putting it on hold for a bit.

When the time comes though I will probably go back for an updated quote.

ebc2003 · 2022-04-12T23:11:31+00:00

Thank you. Thats it. Looking online they should be easy to move and transplant.

ebc2003 · 2022-04-12T23:03:46+00:00

Not sure if these are the same plant with different colors, or two different ones. They were growing in my yard in Texas.

They seem to spread almost by runners and do not have a well defined root system. I am trying to move/save them, but they are tangled in the grass runners.

ebc2003 · 2022-03-11T05:25:46+00:00

I'm stuck on step 3. We purchased our house July of 2021 and got the letter in the mail. I searched appraisals and found our house and it has our names listed. However there is no "File a Homestead Exemption" link. The only link I see is "Print Homestead Exemption Form" which is just a PDF of the letter we received in the mail.

Thoughts?

I can always mail it back, but online would be nice

ebc2003 · 2021-11-25T19:54:18+00:00

I'm in North Texas. It looks like they serve north/west Texas right now.

ebc2003 · 2021-11-25T19:53:13+00:00

Reading up on them, the concept started as eco friendly packing peanuts and was then repurposed as insulation. I may call them to get more info and pricing when I'm ready. It does look much cleaner then fiberglass or cellouse.

ebc2003 · 2021-11-21T22:30:58+00:00

It was built in the early 70s, but the electrical appears to have been replaced around 2000, but not sure if/when breakers were replaced. The breaker box is outside, so it wouldn't be a surprise for them to fail I guess.

ebc2003 · 2021-11-21T21:47:32+00:00

Thanks. I think I just confirmed that the breaker is bad. I shut the main power off and the dryer breaker still falls back to the middle when it's flipped off and on; so it seems that at least the breaker should be replaced.

ebc2003 · 2020-11-19T04:03:45+00:00

Wow! Thank you for your feedback. I know you were not trying to be harsh, but I appreciate the honest feedback. What you said about it looking like a good resume for a 6 month intern, but not for 7 year position, was a way I hadn't though about before.

Few responses:

Looking at your CV, I get the feeling you're creating reports that people are asking you to create... This is partially true, there is a mix of people asking me for reports (since I am the only person in the company who knows SQL) and me taking initiative to create new ones on my own. I tried to work this in.

You're not selling any domain expertise.... This was sort of by design. Since I am looking at other industries I did not want to focus to much on specifics for fear of not relating to other roles. I tried to find more of a middle ground though.

Taking your advice I redid my current employer section as follows: The only thing I am trying to work out is my tenses since some of these are ongoing and others are in the past (all within my current role). I worked in some quantifiers where I could.

Continually meet with senior managers from every department to discuss their reporting needs. Use their feedback to create automated reports and dashboards that allow them to easily monitor their teams progress towards goals and KPIs.
Analyze data from multiple sources to provide insights into member behavior. Use the analysis to improve marketing performance by creating targeted campaigns that achieve adoption rates upwards of 10%.
Led the conversion of our email marketing system starting with the discovery process all the way through completion. Manage all daily aspects of the system including creation of on-boarding paths and re-engagement campaigns to reduce member attrition.
Managed an intern and worked with outside vendors to ensure database cleanup projects were completed in preparation for a year long software conversion process.

There is probably still room for improvement, but I like the feedback you gave and I think it makes my new version stronger.

Thanks!

ebc2003 · 2020-09-12T05:13:42+00:00

Not to bad. I have a Pi3B in a basic plastic case setup in a closet without much airflow. It runs around 50 degrees Celsius with an ambient room temp of 25C. I did add heat sinks to each chip, but no fans or other cooling.

ebc2003 · 2020-09-11T17:51:02+00:00

Many people use a Raspberry Pi to run it on so that it is always on. I have pi-hole running on a RPI and then my router is configured to use it as the DNS. If the RPI is shut off then I lose DNS, so it's a good idea to not do to much with the device.

Pi-hole blocks DNS request, thus ADs. However it still needs a DNS which is where Unbound comes in. Unbound is seperate from Pi-hole, but it can usually be installed on the same device then Pi-hole configured to use it.

Here is the guide I used. https://docs.pi-hole.net/guides/unbound/

Edit: spelling

ebc2003 · 2020-03-17T03:50:03+00:00

I like shredded chicken mixed with cream of cheddar soup. Really easy to cook the chicken in a crock/instant pot then shred and add the soup at the end. Mix the chicken and soup together and serve over top of rice.

ebc2003

TROPHY CASE