Dumbfounded

[Countryboy69]

Ok I'm wondering if there is a formula, macro, program, or some thing I've never heard of that will have a cell if filled out with certain information search through files and open certain files?

 

[Joe4]

That almost certainly would be VBA/macros.

 

[Countryboy69]

I've never done a macro so help would be greatly appreciated. I need this macro to search an "O" drive and open a file with a certain title but only when imputed in a certain cell. Also need it to look up another title when another cell is filled out without the first one being deleted and so on and so on. Any help would be awesome on finding the macro I believe I can find how to install it.

 

[Countryboy69]

Ok i believe i found the macro i was looking for. i just need confirmation it will do as i need it to. i need it to look through a massive O:drive and retrieve a worksheet titled per the input in cells B3:B74. i also need it to continue to find the worksheets without having to delete the previous one. Any input would be greatly appreciated.

Sub OpenWorkBook()
    Dim ExtFile As String
    Dim ExtBk As Workbook
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual
   
    ExtFile = Range("NOTES!C3:C74").Value
    If Not ExtFile = "" And Dir(ExtFile) <> "" Then
    Else
        ExtFile = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Please Select Service A File")
    End If
    On Error Resume Next
    Set ExtBk = Workbooks(Dir(ExtFile))
    On Error GoTo 0
    If ExtBk Is Nothing Then
        Application.Workbooks.Open ExtFile
        Set ExtBk = Workbooks(Dir(ExtFile))
        Application.ScreenUpdating = True 
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub

 

[Yongle]

I need it to look through a massive O:drive and retrieve a worksheet titled per the input in cells B3:B74
I also need it to continue to find the worksheets without having to delete the previous one

1. Worksheet
- do you mean workBOOOK (rather than workSHEET)?

2. Searching for 72 values in a big drive is unlikely to be speedy!
- are the workbooks all in the same folder (or in sub-folders of a known single parent folder)?
- if not do you know which folder contains each workbook

3. Do the workbooks all have the same extention?
.xlsx?
.xlsm?

4. Do any workbooks contain links to other workbooks?

5. Are any of the workbooks password protected?
- sheet protection does not cause issues

 

[Countryboy69]

As far as I know they would be workbooks, and I dont need it to search all 72 at once just when info is added to a specific cell. The files are broke down by dept. So I would need it to look thru said parent folder to find sub folder, to find subfolder, til it finds the one with the name that matches said input. Not sure if they have the same extensions. No links to anything in these workbooks just used as information sheets and no they are not password protected

 

[Yongle]

1. The parent folder is common - good, makes it quicker to run
2. Do you want this macro to run automatically after cell in B3:B74 changes?
3. The macro will search sub- folders until it finds the correct workbook
- it will therefore open the FIRST workbook with name = cell value
- is there any risk of there being 2 workbooks with the same name sitting somewhere under parent folder?
- do you want macro to search all subfolders and provide user with choice if more than one workbook has the same name?
4. What happens after the workbook is opened by the macro - do you want to call another macro or is workbook simply left open for user action?

thanks

 

[Countryboy69]

2. Yes would make it better
3. Yes that's what I want it to do
- no there wouldn't be copies of any subfolder
4. For now just open the work book 8n the future I want it to start another macro to pull specific data from opened worksheet

 

[Yongle]

Thanks - I will post a working macro for you either later today or tomorrow morning

 

[Countryboy69]

Thanks - I will post a working macro for you either later today or tomorrow morning

dude you rock thank you