Posts by Sleeplol

Hello,

Is there a formula for returning a cell value from a closed WB using variable file paths???

In Reports WB

Column A is updated with a number of file paths (around 100), but will change as files are added or deleted from folder.

I'd like for column B to be able to return cell B4 from each of the file paths listed in Column A.

Thanks

Hello,

Would you help me with adjusting the below code to accomplish the following?

• Open file in a directory with a specific title
• Title is based on cell value
• Open different file types (pdf, word, excel, picture, etc)

I've been using the following script for a different project, however, it will only search within a 1 level of sub-folder, and only if the file is a PDF.

We would need to be able to search a directly due to the required file organization into different sub-folders within a directory.

Public Sub Open_PDF_File()

    'Const cAdobeReaderExe As String = "C:\path\to\AcroRd32.exe"
    Const cAdobeReaderExe As String = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"

    Dim PDFfile As String
    
    PDFfile = "G:\Administration\Signoff_Signatures\" & Range("AO5").Value & ".pdf"
    If Dir(PDFfile) <> vbNullString Then
        Shell cAdobeReaderExe & " " & Chr(34) & PDFfile & Chr(34), vbNormal
    Else
        MsgBox PDFfile & " not found."
    End If

End Sub

Help would be super appreciated

Thanks

Hi Vietnt.fov,

I scrutinized your results and EVERY input is correct. Holy Wow!!! Your profile says beginner, but I highly doubt that.

Your scripts work incredibly. Thank you very much.

Hi Vietnt.fov,

I've downloaded your work, but I won't get a chance to trial and test the results until later today.

I very much appreciate your work; thank you.

I'll post my findings here later.

Hi Roy,

Attached is the Workbook with the Google Sheet connection. I turned off Refresh on Open

Note, the JotForm form is simply:

Enter Employee ID

Clock IN

Clock OUT

(In and out are radio buttons)

Also, the formulas were working fine for day shifts, but now that there are night shifts they are not properly capturing the shift.

So, all the pay periods before last week were correct and were able to be emailed to payroll, but last week, period between 10-4 and 10-10 everyone's time is messed up.

We have a lot of red tape with permissions and such.

It would be simpler to use an all excel method with the clock in and out in the workbook. But that just isn't going to be available.

We are able to set up a simple In & Out on jot form on a thin client computer, then HR has access rights for office to use the excel file from office computers.

Later today when I'm at work I could attach the actual doc so you can see how it's put together; and hopefully give your feedback.

Do you use excel to keep track of your employees? If so, is the data sent from a form? And would you mind sharing the method?

JotFor > Google sheet > Excel.

This path works fine, but the format is TblA which is hard to read with multiple employees. TblB format would be ideal

Hi Roy,

They clock in using a JotForm which saves to a Google Sheet which is then queried to an Excel Workbook.

That query populates TblA. TblB needs to be structured like the example in my last post so that the user can run a report from the dashboard which uses an Advanced filter macro.

The value of the entire doc is based on the structure and accuracy of TblB

I posted an example workbook with no connections to just show the data and an example of how I need TblB structured.

- Show Clock in and out times for each employee ID

- Accommodate for day and overnight shifts (e.g. 8/3/20 7:00 PM - 8/4/20 5:00 AM)

- Show a blank or short message when employee forgets to clock in or out (e.g. "No Clock IN" - 8/4/20 5:00 AM, or 8/3/20 PM - "No Clock OUT")

Hi Roy,

Thanks for taking a look into this

All of the data is queried into TblA

I'd like the end result to be TblB so that we can easily see the hours worked in a shift.

Desired result would be TblB format but would also accommodate for day and night shifts and indicate if someone didn't clock in or out:

Please let me know any more info I can provide

Hello All,

I'm attempting to restructure a table with another table.

How would you do the following???

All Clock IN's from TblA in TblB Clock In column and Clock OUT's in the Clock OUT columns

This would also need to line up the appropriate Employee ID. I've also attached a macro enabled example file for testing

TblA

TblB

I appreciate any help with this.

Nevermind, let's scratch this post.

Had an issue with one row

Thanks to anyone who put in any effort into this

Good Morning Everyone,

I can't seem to get this to calculate correctly, so maybe this would need a VBA solution.

(I don't want to get docked for a double post; so I'll just post in the VBA section)

Table ClockIn is queried so it will grow.

Table Data_tbl Needs to split the Clock In and Clock Out times into different columns, and have a row for each entry. It works fine until the 12th entry, then it repeats.

File attached.

thanks for any help

Hi RoyUK,

Yes, it is unrelated.

It was literally my very next task with that same project, but I see that I should have posted a new thread to add value for anyone else who happens to be searching the topic down the road.

I will re-post

Man, Mumps and Roy in 1 post, I've gained like half of my VBA knowledge from just you two lol

Mumps,

I went ahead and targeted an "Action Item" to each drop-down that you input for me.

Kind of longhand on my part, but there were only 10.

Hey, while you're here (if you happen to revisit this thread)

I'm attempting to abc sort and eliminate blanks in multiple columns independently of each other.

Currently I'm using the following then calling each macro, but with a large data set it could take a few seconds.

(Note: I'm only posting the first 3 columns for the sake of space; there are more)

How would you recommend doing this??

Sub SortAll()
Call MainsortSev
Call MainsortName
Call MainsortOI
End Sub

Sub MainsortSev()
Dim rng As Range
  Application.ScreenUpdating = False
  Set rng = Worksheets("MainDATA").Range("CT5:CT50000")
  rng.Sort Key1:=rng.Cells(1), Order1:=xlAscending, Header:=xlNo
End Sub

Sub MainsortName()
Dim rng As Range
  Application.ScreenUpdating = False
  Set rng = Worksheets("MainDATA").Range("CU5:CU50000")
  rng.Sort Key1:=rng.Cells(1), Order1:=xlAscending, Header:=xlNo
End Sub

Sub MainsortOI()
Dim rng As Range
  Application.ScreenUpdating = False
  Set rng = Worksheets("MainDATA").Range("CV5:CV50000")
  rng.Sort Key1:=rng.Cells(1), Order1:=xlAscending, Header:=xlNo
End Sub

Hey Mumps,

Thanks for your help, I always appreciated your scripts.

The transfer works well but I can foresee a couple issues occurring.

Issues one: If a user selects the "blank" more than once, other action items are deleted.

Issue two: The copied issue under Action Items need to always be on the same row as the original issue.

Issue two is my fault for not stating that this is a necessity. That little area that the info is copied to won't be seen by the user and will feed a database.

Hello Everyone,

I'd like to set up a check box in E23 to:

1 - add "Action Item" to G24

2 - Value from G22 is copied to O9

Redundancy: (If check box is "deselected"

1 - "Action Item" is removed from G24

2 - O9 Value is cleared

This process is repeated for ten "check boxes" and their corresponding areas

Currently I have a worksheet_SelectionChange handling select & deselect, however, it doesn't call any function.

I can do this long hand with a change even for each cell, but with my know how I would need to have a separate cell for "Select" and Deselect"

I'm hoping you'll help me code this the proper way, versus what I would come up with.

Moch WB is attached, and probably makes better sense than what I've described.

Thanks for any help.

Justin Thank You!!

Your script works perfectly!

The run time is a nice touch; so incredibly fast

Many many thanks!!!!

Hey Justin,

Yes, you're right, I was thinking of "regular formulas" vs "array" (though powerful; in my experience take longer to calculate)

I would've also thought that a VBA solution would be quicker, but Roy's knowledge of VBA doesn't really leave room for my assumption

Thanks for your response and effort putting together the Pivot Table

The issue is that I would like the report to end up looking like the PreReport so that the date and instructor are also displayed. Below is the same pic from the WB.

Columns A:D are "refreshed" whenever a new training is added and assigned via userform (this is assigned to groups in bulk). The range on Sheet SignOffDates is also updated via userform.

The PreReport format seems to work well when running reports powered by advanced filter. The layout also feeds a dynamic matrix for ease of viewing for employees and management.

My mistake was setting this up prior to testing the performance of so many array formulas. Whenever a new training is added and assigned, it can take 5 to 7min to update, without the formulas (or when calculations are temporarily set to manual) it renders in 3sec. Though it will have to calculate at some point, and it is likely that management will want to add a new training then immediately run a report to notify staff of an update or what not, and the wait time makes that really tough.

Though the PreReport format would work best for me, I believe that you and Roy have the next best solution by using a pivot table or Power pivot (since combining two ranges)

Still very open to help.

In the mean time I'll post updates to this thread as I discover solutions.

Would there be a way to convert the array formulas to native formulas? Even if the formulas are longer and require helper columns, it would probably run faster than having all of the arrays. Seems like Excel is optimized for its native formulas

Darn! If you think the array formulas are (in this case) superior, then they likely are.

Thanks RoyUK

I'll have to rethink the structure I built.