PDA

View Full Version : [SOLVED] Set File Path to Cell in a workbook



sharc316
04-05-2017, 12:41 PM
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
04-06-2017, 03:30 AM
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
04-06-2017, 04:34 AM
This is working great. Thank you for your help p45cal.