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
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