MS Excel / VBA help thread

Nice, looks about right.
I was thinking about this this morning, I think it could be formula'able, but potentially v complex. You'd also have to make an assumption of the upper limit of how many people could be off in one time

Vba is the right way i think. It will recalculate all the time, so might slow down if it or your spreadsheet gets big, or you start tracking many years of holidays, but can't imagine it'll be terribly noticeable in this case.

It would be complex and when it breaks.....

I might not be there.

Perfect time for it to break, shirley... :)

I have people who manually entered dates on a sheet that goes out monthly. Over the formula I wrote to do it automatically.

Lock cells and data validation.

This is a nice solution, much better than my standard create a pivot table and lookup from there solution (although that is faster).

Should I spend !$150 for a year of data camp?

I think like to try and get into managing people to do data stuff.

COUNTIFS (not COUNTIF) could work using arrays.

They are very expensive fuctions though, and can completely murder spreadsheets.

Match and vlookup could be used with the flag at the being non-exact, but sort order of the data effects the value returned.

Yeah, it's one of those "I know what I want, but the time taken to do it for the appreciation given for solving problem that could be done manually is inversely proportional' situations.
The VBA I nicked looks like it'll do it, nobody needs to know how it does it. It just does it. And the spreadsheet is locked and managed by me.

But serious, does datacamp seem like a good idea? I think so.

SPAM
https://www.datacamp.com/promo/cybermonday

I signed up. I don't know if there are others out there in my situation of: only person on team, wanting to solve things, wanting to make progress out of current situation.

Good luck with it. I don't have any experience of datacamp so didn't reply to your posts on it the past couple of days. Report back!

I'm tempted, will spam back if I do.

Loosely related - have a look at https://www.xlwings.org/
Python for Excel. Would likely sort out a lot of the data crunching in something nicer than VBA. I've not used myself, but always thought it looked promising.

Is this like Code Academy? I've heard some quite negative stuff about Code Academy, not heard of Data Camp before, but I'd hope it's better than CA.

I've used S+ for data stuff and modelling in the past - it used to talk really nicely to excel (data in S+, presentation / driving from excel). Used to be that S+ and R were really similar, though not sure if that's still the case.

'Python for Excel' sounds like it was invented mostly to annoy srs bzns sql coders.

I wonder how relevant this kind of stuff will continue to be for the everyday power-user with the way MS is pushing Excel and its various BI stuff with ease of use.

As a test the other day I managed to load 256 million lines of data into Powerpivot (from a CSV) and pivots, lookups, etc took a negligible amount of time to process.

BI is the way for MS now isn't it?
It's all about being like tableau/ google data studio whatever.

So far, I'm smashing through introductory SQL.
it's quite basic, like me, but there are projects for everything. I think it's a case of doing the courses. Then doing the projects.

I would like to open the open file dialogue using VBA from a pre specified location on a sharepoint site. I can open the open dialogue bow using GetOpenFilename. How can I make sure the open dialogue box is always pointing at the correct place?

For bonus points, can I also filter files based on the filename (with wild card)? i.e. the file name structure will be filename_yyymmdd. I'd like the user to only see files with the filename string in them.

and sorted by date for even more ageing fixeh sidder points.

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-getopenfilename-method-excel

https://stackoverflow.com/questions/40107912/getopenfilename-not-opening-file-in-vba

?
is it filefilter?
https://www.mrexcel.com/forum/excel-questions/392217-application-getopenfilename-filefilter-question.html

This will get you close

Sub OpenFile()
Dim myfile As String

With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "C:\TEMP\filename*"
     .Show
    myfile = .SelectedItems(1)
    End With

Workbooks.Open Filename:=myfile

End Sub

(Obviously replace c:\temp with your directory). Have a google of msoFileDialogFilePicker for some of the other variables

Although if you really don't want them to select other files I'd be tempted to use VBA to create a sheet with a list of the files that they can open hyperlinked to the location.

Thankyou, will have a look if I get time tomorrow.

sorted by date

I reckon you would struggle to do this with .net or vb, let alone VBA.

A diiiirty way could be to have a VBA application caller insert a dword value into the registry to force the sort order, and then remove it when the sub is run. The walk away when your PC stops running.

I think you can use 'chdir' right before your getopenfilename to change the location it opens up in. I've not tried this on a SharePoint drive.

The code above from @aggi looks like it's probably the better way to do it though.