MS Excel / VBA help thread

Will try again today.

It's one of these things that I want to try and set up right from the get go. I was maintaining a pretty spreadsheet for one business line with something like 10-15 rows, so it was easier to do mainly manual work. But now it's bigger and I need other people to feed into it, I want to get it set up neatly.

Looks shit aesthetically though. Except the chart. That looks great.

How many rows of data?

Where does it come from?
How often does it update?
If other people are adding shit, it will break.
Can you get them to add data to a different book and then import that data from their book into your reporting book?

No, you just need to add Years into the pivot along with Months.

This works.

How many rows of data?

1. Hopefully this will increase. However, it will likely decrease first.
   
   

Where does it come from?

A salesforce export to xls.

How often does it update?

Checked weekly. Changing monthly. Volume of changes depends on the column, but for the count in question monthly. Probably

If other people are adding shit, it will break.

Yes.

Can you get them to add data to a different book and then import that data from their book into your reporting book?

Yes I could. Maybe this is the way to go. It would also allow me to do some gatekeeping ownership bullshit.

Are you going to say put it into another sheet and power query all the things?

Almost certainly.
But you don't need to power query it fully.

Give the people their own sheets.
Import data from sheets into your book.
Turn them into tables if you need/want
Then you're linked to the raw data, you can do some simple cleaning if they fuck it up/ask them to change it when they do because they will have broken the world.

But if it's coming from salesforce, and you define what you're taking, then they just grow their export file (don't change its name so you can have salesforcedata_from_brian_010124-311224 etc etc) which expands monthly, your sheet looks at their sheets.

This is a good idea. I want to get a new sf report built as right now I'm taking from 2 reports.

It's all so fucking annoying as I've got shit like one report listing the project as;
"Company Name - Currency - Project Name - sort of description (- maybe another bit of concat info to make life hard)",

while the other lists it as;
"Project Name (date from - date to)"

Write an SOP.

This is, nowadays, often the answer.

Most of my previous data collecting, cleaning and collating stuff which would be done with SQL and macros and shit are now done much more easily via Power Query.

I do still have issues with it though. Not being able to dynamically locate a source file based on %userprofile% is annoying as is not being able to import CSV files from Onedrive.

Argh that one is terrible - guys here's something to use in the team as a reproducible thing.
"it doesn't work - i'm not using it"
"you just have to change the links to the bit with your name?"
"no it doesn't work"

how do i unprotect all the files in a location without opening them first?

I wouldn't have thought that's possible. I'd use a macro to open and save unprotected so at least you can just leave it running.

It isn't....

Did it in vba with "FALSE" display alerts.

i'll check it worked in a minute.

Sub RemovePasswordProtection()
Dim folderPath As String
Dim password As String
Dim wb As Workbook
Dim file As String

' Set the folder path where your Excel files are located
folderPath = "filepathgoeshere"

' Set the password for the Excel files
password = "pwd"

' Disable alerts to prevent prompts
Application.DisplayAlerts = False

' Loop through each Excel file in the folder
file = Dir(folderPath & "*.xlsx")

Do While file <> ""
' Open the workbook with the specified password
Set wb = Workbooks.Open(folderPath & file, password:=password)

' Save the workbook to remove password protection
wb.Save
wb.Close

' Get the next file in the folder
file = Dir
Loop

' Enable alerts again
Application.DisplayAlerts = True

' Display a message
MsgBox "Password protection removed from all Excel files in the folder.", vbInformation
End Sub

why this no work!?

Aren't you just saving the file again? I'd say you need to save as and remove the password (although I can't test this as my Excel is currently a white screen).

Edit, looks like Password:="" is what you want

ActiveWorkbook.SaveAs Filename:=myfilename, _
        FileFormat:=xlExcel12, CreateBackup:=False, Password:=""

I powershelled some of it, but there is one folder that's proving to be a twat. and also: scripts aren't exactly encouraged.
i also spent some time trying to run python in R....

https://www.youtube.com/watch?v=N-ENJA8NGog

https://www.reddit.com/r/excel/comments/u6vlqm/power_query_folder_of_password_protected_files/

(i can't open youtube in my office, but can from home...)

This is cooking my brain. I use month = today-1 to get last months name but today this is giving me Jan despite the month value answer being 2 which is correct. Anything obvious im doing wrong?

Edit even month(today()) is giving me 3 in a separate cell, but in this formula gives me 3 that becomes Jan. Weird.

2 Attachments

Oh this is a known problem.
https://answers.microsoft.com/en-us/msoffice/forum/all/all-months-in-excel-are-showing-as-january-now/3c1ab150-a343-4342-a1bd-59ac16cac818

=TEXT(TODAY()-1,"MMMM")

In =TEXT(2,"mmmm"), you are getting the month from a date value which represents 2 Jan 1900. Use the actual date value.

Edit: I'm not entirely clear on your first bit but are you using =MONTH(TODAY())-1 instead of =MONTH(TODAY()-1)?

Thanks, this does fix it.

In =TEXT(2,"mmmm"), you are getting the month from a date value which represents 2 Jan 1900. Use the actual date value.

The 2 was derived from date value though? The images are from the Evsluate Formula review steps

Edit: I'm not entirely clear on your first bit but are you using =MONTH(TODAY())-1 instead of =MONTH(TODAY()-1)?

Both gave same result, but turns out MONTH needn't be used.

You're missing what a date value really is: a count of days elapsed since 31 Dec 1899. And using MONTH was causing your problem.

Right now TODAY() = 45352 (as you'll see if you format as number)

MONTH(TODAY())-1 is equivalent to (current month) -1
MONTH(TODAY()-1) is equivalent to yesterday's month

They'll only give the same result on the first of a month Feb-Dec.