Set File Path to Cell in a workbook

[sharc316]

Hi,

If someone can please help. The below code loops through files in specified folder and executes a macro on each of the files that is called below. I've set up a browse button that lets me select a specific file and return the file path in to a cell in a workbook. I would like to modify the below code to use the file path in this cell, lets say E18, and execute the macro on that specific file only.

Any suggestions would be greatly appreciated.

Sub MultipleFilesDataFormattingKarpExprs()
'Enable reference to Microsoft Scripting Runtime if you want to use early binding
    Dim fso As Object  'Scritping.FileSystemObject
    Dim fldr As Object 'Scripting.Folder
    Dim file As Object 'Scripting.File
    Dim wb As Workbook

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.Getfolder("C:\***xx\")

    For Each file In fldr.Files
        'Open the file
        Set wb = Workbooks.Open(file.Path)
        
         'Copy and Paste Business name; path needs to be UPDATED for every company
    Workbooks("wbname").Worksheets("Sheet1").Range("C3").Copy
    Sheets("sheet1").Select
    Range("C1").Select
    ActiveSheet.Paste
        
        '## You will need to modify this line to refer to the correct
        '    module name and macro name:
        Application.Run "PERSONAL.XLSB!DataExtract.DataExtract"
        
    'Save file in a directory based on cell value on sheet1
    ChDir "C:\filepath"
    ThisFile = Sheets("Sheet1").Range("E1").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=51
    wb.Close
    Next

    Set file = Nothing
    Set fldr = Nothing
    Set fso = Nothing
End Sub

[p45cal]

Lots of unknowns in your code so this is a wild guess:

Sub OneFileDataFormattingKarpExprs()
Dim wb As Workbook

'Open the file
Set wb = Workbooks.Open(Range("E18").Value)

'Copy and Paste Business name; path needs to be UPDATED for every company
Workbooks("wbname").Worksheets("Sheet1").Range("C3").Copy
Sheets("sheet1").Select
Range("C1").Select
ActiveSheet.Paste

'## You will need to modify this line to refer to the correct
'    module name and macro name:
Application.Run "PERSONAL.XLSB!DataExtract.DataExtract"

'Save file in a directory based on cell value on sheet1
ChDir "C:\filepath"
ThisFile = Sheets("Sheet1").Range("E1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=51
wb.Close
End Sub

[sharc316]

This is working great. Thank you for your help p45cal.