Loop through excel files in a directory and copy onto master sheet

Topic

New Reply

Hi All,

I have around 10-15 excel files in a folder which are refreshed each week. I need to loop through these files and copy the data form them into one master sheet.

I plan to create the macro in a mast file called Pipeline Consolidated and run it form this workbook so don’t want this file to open.

I have the code once the additional workbook is open to select the cells and then copy them back to my master sheet, my problem is how to loop through all the excel file in the directory.

So far I have to put the file name in and repeart this step each time.

Path = “S:ReportingPipelineSubmitted by departmentsPipeline template imp.XLSX”
Workbooks.Open Filename:=Path
””’End of Open Workbook

‘’’’’’’’’’’’’’’copies form the additioanal workbook’’’’’’’’’’’’’’’’’’
Sheets(“Pipeline”).Select
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If
LastRow = ActiveSheet.Cells(Rows.Count, “B”).End(xlUp).Row
Range(“A4:J” & LastRow).Select
Selection.Copy

Windows(“Pipeline Consolidated.xlsm”).Activate

”’selects the sheet name on the Consolildated workbook and copies the data
Sheets(“Pipeline Consolidated”).Select
Range(“A4”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

I have been searching google for ages but as i am new to VBA and have learnt from you guys and myself it is very difficult as there are alot of complex answers when i was hoping it would be quite simple.

REgards,

Reply | Quote

Replies

Hi

From your code it looks like you are using Excel2007 or later?
Can you attach an empty sample datafile, with just the headings?
Can you give us an idea of the filenames you are using for the 10-15 excel files?

I can give you a merge tool that will do what you want.

zeddy

Reply | Quote

Stimp,

Try wrapping the following code around your macro. It will open each Excel file in a directory, run your macro, then close the workbook and cycle to the next file. After the last file is closed, the macro will end. Replace the path with your correct path.

I had this code stuffed in my bag of tricks. Don’t know who to give the credit to.

HTH,
Maud

Code:

Public Sub test()
[COLOR=”#008000″]’DECLARE AND SET VARIABLES[/COLOR]
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = “C:UsersMaudibeDesktopExcelFiles”
Filename = Dir(Path & “*.xlsm”)
[COLOR=”#008000″]’——————————————–
‘OPEN EXCEL FILES[/COLOR]
 Do While Len(Filename) > 0  [COLOR=”#008000″]’IF NEXT FILE EXISTS THEN[/COLOR]
    Set wbk = Workbooks.Open(Path & Filename)
[COLOR=”#008000″]    ‘
    ‘ CODE GOES HERE
    ‘[/COLOR]
     MsgBox Filename & ” has opened”
    wbk.Close True
    Filename = Dir
Loop
End Sub

Reply | Quote

Hi

Although the code from Maudibe will loop through all files in a specified folder, I would advise against this method for merging data into a consolidated file.
The reason is it is very easy for Users to place multiple copies of data files into such a folder, and you wouldn’t want to ‘double-count’ records etc. by processing all files in a folder.
Your code would need to test for such cases.
Also, your code would need to test each file is of the ‘expected’ type i.e. contains the ‘expected data’ etc.
(You could use a ‘file-naming’ convention to deal with this)
It would be better to have a specified list of pre-defined ‘source’ files.
I have a merge tool that will process such data, but just require a small sample datafile which has the data headings in.

zeddy

Reply | Quote

Hi Maudibe,

Sorry to bumps this thread, but thought it was relevant to the code you posted..

I have just come across your code that does the job, but I was wondering if there’s any way to pause the macro so that then user can check the spreadsheet that opens up with the macro, before the macros is resumed and the maintenance work is carried out, in my case I am just deleting empty rows, but not until I checked them first.

IF the user can take control of the spreadsheet in between the macros then I can see endless possibilities..

Many thanks for sharing this code..

Gangsta

Stimp,

Try wrapping the following code around your macro. It will open each Excel file in a directory, run your macro, then close the workbook and cycle to the next file. After the last file is closed, the macro will end. Replace the path with your correct path.

I had this code stuffed in my bag of tricks. Don’t know who to give the credit to.

HTH,
Maud

Code:

Public Sub test()
[COLOR=#008000]’DECLARE AND SET VARIABLES[/COLOR]
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = “C:UsersMaudibeDesktopExcelFiles”
Filename = Dir(Path & “*.xlsm”)
[COLOR=#008000]’——————————————–
‘OPEN EXCEL FILES[/COLOR]
 Do While Len(Filename) > 0  [COLOR=#008000]’IF NEXT FILE EXISTS THEN[/COLOR]
    Set wbk = Workbooks.Open(Path & Filename)
[COLOR=#008000]    ‘
    ‘ CODE GOES HERE
    ‘[/COLOR]
     MsgBox Filename & ” has opened”
    wbk.Close True
    Filename = Dir
Loop
End Sub

Reply | Quote

Hi Gangsta

..perhaps you just need to re-locate Maud’s message box, like this..

Code:

Public Sub test()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = "C:UsersMaudibeDesktopExcelFiles"
Filename = Dir(Path & "*.xlsm")
'--------------------------------------------
'OPEN EXCEL FILES
 Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
    Set wbk = Workbooks.Open(Path & Filename)
    '
    MsgBox Filename & " has opened. Check it now, and then click here to continue processing.."
    ' CODE GOES HERE
    '
     
    wbk.Close True
    Filename = Dir
Loop
End Sub

zeddy

Reply | Quote

hi ,

I am having around 1000 files present in the web application( each files contains some set of excel sheets, pdf file, word file, jpeg files etc), i am trying to fetch all of the files to the excel sheet, using your loop through concept, to see the flow of the excel sheets and then wanted to store all the datas present in the opened files to one master file where all the excels file will be displayed automatically, in this way, i can reduce the time in opening each file from the website web, as i am new to vb script please guide me to write the code to resolve the issue, please give your generic reply, and thanks for your valuable code snippet.

best,

abhchakr

Reply | Quote

Hi Maud, thanks for the attached.

Hi Zeddy,

Please find the attached file as requested.

The headings will be the same in each file, I can have the file names saved so we can always check for them files, although sometimes the files may not be there or may be blank so I guess an if file exists may work.

Once I have all the data together I can then continue to put my code on there to convert all values into a single currency etc.

Kind regards,

Reply | Quote

Hi

Does each datafile include multi-currency records, or is it one currency per datafile?
Just asking, because the merge process can also provide certain ‘summary’ info, for each datafile merged.

I’ll post the merge file tool later today, now that I have your datafile template.

zeddy

Reply | Quote

Hi

I have attached two files:
[rzDeptMergeTool-v1.00.xlsm]
[BlankPipelineConsolidated.zip]

Save the .xlsm file to a folder of your choice.
Unzip the [BlankPipelineConsolidated.zip] file, and place the binary .xlsb file into the same folder as the .xlsm file.
(binary .xlsb file cannot be uploaded to this forum, unfortunately)

1. Open the [rzDeptMergeTool-v1.00.xlsm] file.
2. Enable Content (i.e. enable macros)
3. In cell [F3], enter the folder location of your datafiles.
4. Enter the Department names in the Dept. List (in range [E16:E115] )
(provision has been made for up to 100 entries – this can be amended easily)
5. The corresponding datafile names in adjacent column [G] use a formula to determine the name.
(adjust the formulas in [G16:G115] to suit your naming convention, or just enter the actual filename.

6. make sure you have some sample datafiles in your folder (as specified in cell [F3] )
7. Click the button labelled [click here to Merge data files..]

Watch as the status block is updated as each file is merged.

Switch Windows to view the consolidation file.

The process can be amended to import directly into a sheet in this processing tool etc etc.

Please let me know how you get on with it.

zeddy

Reply | Quote

I have a question related to this thread. I too have a large number of files that have to be batch processed every week. I am fairly new to VBA.

The data comes in a csv file and is always in the same format. I have written a macro to clean up the data I need and place it in a section of the file so that I can copy it and paste it to another spreadsheet where I collate all the data from all the files. But I don’t know how to write the following:

1.Take the data I have just extracted, copy it, paste it to the end of a specific worksheet of a specific workbook
2.Save that workbook
3.Go back to the csv file I copied the data from. Close it (do not save the csv) and move onto the next file and repeat until all files in that folder are processed.

I have been doing some research on the net and come up with the following code:

Sub Cleanmyclaims()
Dim file
Dim path As String

path = “c:userspaulskydrivedocumentsbamhawkenclaims”

file = Dir (path & “*.csv”)
Do While file “”
Workbooks.Open Filename:=path & file’

‘Here is where I am not sure if I have written the correct code. I want to run the macro “claims” that is in my personal.xlsb

Call Claims

‘This is where I do not know how to copy the data to the end of Sheet1 of the following file (“C:userspaulskydrivedocumentsbamhawkenclaimsHawken – claims register.xlsx”). Go back to the csv file close it and then move onto the next file in the directory.

file = Dir()
Loop End Sub

Any help would be greatly appreciated!

Regards,

Paul

Reply | Quote

Looks very similar to the code in post #3.

Can you post a sample of your CSV file with the “cleaned up data” located in the section of the file you speak of? Will also need a sample copy of your second spreadsheet containing your macro and indicating where you want the data copied to.

Maud

Reply | Quote

36791-Claims-Macro

Thanks Maud for your offer of help. Overnight, I managed to find the code to copy the data into the new spreadsheet, go back to the previous window and close the csv file!

Now I just need the code to open a file, execute a macro, close the file and move onto the next in the folder. Could I use the code that you put in Post 3 to do this?

As requested, I have attached:

[*]A couple of raw csv files
[*]A processed csv
[*]The target spreadsheet for the data and
[*]A file containing the macro that I have written.
[*]

Any assistance you can offer is greatly appreciated!

Reply | Quote

Pwanis,

The answer is yes. You will need some method of calling the macro “test”. It can be a button, short cut keys, selection from macro list, or an event. You will also need to change the path to the folder where your files are located (line 6 in the code). In the section of the code, “CODE GOES HERE”, replace with the call to your macro. Although there are multiple ways to call a macro from within code, the 2 easiest ways are:
1. Using the Call statement: Call MACROname (parameter1, parameter 2)
2. Without the Call statement: MACROname parameter1, parameter2

Code:

Public Sub test()
‘DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = “C:UsersMaudibeDesktopExcelFiles” [COLOR=”#008000″] ‘CHANGE PATH[/COLOR]
Filename = Dir(Path & “*.csv”)
‘——————————————–
‘OPEN EXCEL FILES
 Do While Len(Filename) > 0  [COLOR=”#008000″]’IF NEXT FILE EXISTS THEN[/COLOR]
    Set wbk = Workbooks.Open(Path & Filename)
   [COLOR=”#008000″] ‘
    ‘ CODE GOES HERE
    ‘
[/COLOR]     MsgBox Filename & ” has opened”  [COLOR=”#008000″]’OPTIONAL- CAN COMMENT OUT[/COLOR]
    wbk.Close True
    Filename = Dir
Loop
End Sub

If you can define which sections (cell addresses) of data you wish to move in the .csv files and indicate where you want it placed in spreadsheet (.xlsx), I could help you further.

Maud

Reply | Quote

Hello Maudibe,

Im trying to get data from .csv files in folder to main wbk. The same problem which others were solving. I tried to use your code but its not working for me.

Below you can see code which Im using.

When I reach line Do While Len(Filename) > 0 it jumps to the end and macro ends with nothing done. I hope you can help me. Im using excel 2013. Thank you very much for your kind help.
[/FONT]
Sub Test()

Dim wbk As Workbook
Dim Filename As String
Dim Path As String

Path = “Path”
Filename = Dir(Path & “*.csv”)
Do While Len(Filename) > 0
Set wbk = Workbooks.Open(Path & Filename)

Range(“A1”).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows(“RINKAI_SOUHRN_LOOP.xlsm”).Activate
Range(“A1″).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save

MsgBox Filename & ” has opened”

wbk.Close True
Filename = Dir

Loop

End Sub

Pwanis,

The answer is yes. You will need some method of calling the macro “test”. It can be a button, short cut keys, selection from macro list, or an event. You will also need to change the path to the folder where your files are located (line 6 in the code). In the section of the code, “CODE GOES HERE”, replace with the call to your macro. Although there are multiple ways to call a macro from within code, the 2 easiest ways are:
1. Using the Call statement: Call MACROname (parameter1, parameter 2)
2. Without the Call statement: MACROname parameter1, parameter2

Code:

Public Sub test()
‘DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = “C:UsersMaudibeDesktopExcelFiles” [COLOR=#008000] ‘CHANGE PATH[/COLOR]
Filename = Dir(Path & “*.csv”)
‘——————————————–
‘OPEN EXCEL FILES
 Do While Len(Filename) > 0  [COLOR=#008000]’IF NEXT FILE EXISTS THEN[/COLOR]
    Set wbk = Workbooks.Open(Path & Filename)
   [COLOR=#008000] ‘
    ‘ CODE GOES HERE
    ‘
[/COLOR]     MsgBox Filename & ” has opened”  [COLOR=#008000]’OPTIONAL- CAN COMMENT OUT[/COLOR]
    wbk.Close True
    Filename = Dir
Loop
End Sub

If you can define which sections (cell addresses) of data you wish to move in the .csv files and indicate where you want it placed in spreadsheet (.xlsx), I could help you further.

Maud

Reply | Quote

Hi

Welcome to the Lounge as a new poster.

Code:

Sub Test()

 Dim wbk As Workbook
 Dim Filename As String
 Dim Path As String

 Path = "C:aaaaaaaaabbbbbbbbb"       '< 0
 Set wbk = Workbooks.Open(Path & Filename)

 Range("A1").Select
 Range(Selection, Selection.End(xlDown)).Select
 Range(Selection, Selection.End(xlToRight)).Select
 Selection.Copy
 Windows("RINKAI_SOUHRN_LOOP.xlsm").Activate
 Range("A1").Select
 Selection.End(xlDown).Select
 ActiveCell.Offset(1, 0).Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Application.CutCopyMode = False
 ActiveWorkbook.Save

 MsgBox Filename & " has opened"

 wbk.Close True
 Filename = Dir

 Loop

 End Sub

zeddy

Reply | Quote

Hello, thank you Zeddy, i have forgot mention that i removed my path here. In my code there is path written correctly. There is some issue with DoWhileLen or Set comand i guess?

Reply | Quote

Maud,

This worked perfectly!!! You are a legend and I really appreciate your help. If you are ever in Melbourne,:D Australia, contact me and I’ll shout you a beer!

Thanks again.

Regards,

Paul

Reply | Quote

You’re on bro!

Reply | Quote

You have already concatenated the path and file name so the “set” line is incorrect. Try this:
Set wbk = Workbooks.Open(Filename)

cheers, Paul

Reply | Quote

Have tried your modification Paul and its the same. After Do comand it jumps to the end of code.

Sub LoopThroughFolder()

Dim wbk As Workbook
Dim Filename As String
Dim Path As String

Path = “\Czprgcd0wgroupW01_Logistics1_Distribution3_DSD OntimeDSD ON-TIME reportExport RiRORINKAI kopie datw21”
Filename = Dir(Path & “*.csv”)

Do While Len(Filename) > 0
Set wbk = Workbooks.Open(Filename)

Range(“A1”).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows(“RINKAI_SOUHRN_LOOP.xlsm”).Activate
Range(“A1″).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save

MsgBox Filename & ” has opened”

wbk.Close True
Filename = Dir

Loop

End Sub

Reply | Quote

Add a message immediately before the set command to see if the path look valid – contains files.
MsgBox Filename & ” may not contain files”

cheers, Paul

Reply | Quote

Hello, when I put msgbox bwtween Do and Set it does the same thing, jumps to the end with no action done. No message is shown.

Reply | Quote

You haven’t got anything in the “Filename” variable. Put the messagebox before the Do and test.

Reply | Quote

Okay, what does it mean please? If I get message, macro does not see any files? Ive got the message..

Reply | Quote

Or should I put path into Filename = Dir(Path & “*.csv”)? I though I defined it before

Reply | Quote

Hi

..you are probably missing the last “” character for your path. This is required!
so use

Path = “\Czprgcd0wgroupW01_Logistics1_Distribution3_ DSD OntimeDSD ON-TIME reportExport RiRORINKAI kopie datw21”

zeddy

Reply | Quote

Hi Zeddy, Paul, Thank you very much for your time and help. Code works now, so there was slash missing in Path. Have a nice day. Bye

Reply | Quote

Hi, one more question please. If I do not want set path directly, what comand I should use to get “open file dialogue”? Will you help me with modification too? Thank you very much in advance

Reply | Quote

Hi malbre

..try this:

Code:

Sub LoopThroughFolder()

Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim saywhat
Dim zItem

Path = ThisWorkbook.Path                        'set a default path

'**********************************************
'DISPLAY FOLDER SELECTION BOX..                   'display folder picker
'**********************************************
With Application.FileDialog(msoFileDialogFolderPicker)          'use shortcut
saywhat = "Select the source folder for the source datafiles.." 'define browser text
.Title = saywhat                                'show heading message for THIS dialog box
.AllowMultiSelect = False                       'allow only one file to be selected
.InitialFileName = Path                         'set default source folder
zItem = .Show                                   'display the file selection dialog

.InitialFileName = ""                           'clear and reset search folderfile filter

If zItem = 0 Then Exit Sub                      'User cancelled; 0=no folder chosen

Path = .SelectedItems(1)                        'selected folder
End With                                        'end of shortcut

If Right(Path, 1)  "" Then                   'check for required last  in path
Path = Path & ""                               'add required last  if missing
End If                                          'end of test fro required last  char

Filename = Dir(Path & "*.csv")

Do While Len(Filename) > 0
Set wbk = Workbooks.Open(Filename)

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("RINKAI_SOUHRN_LOOP.xlsm").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save

MsgBox Filename & " has opened"

wbk.Close True
Filename = Dir

Loop

End Sub

zeddy

Reply | Quote

Hi Zeddy,
almost a same question here but i do want to look through the main folder and each sub-folders and only copy the csv file with specific text name on the csv file example ( inthe text file must have string “specialvvvv”
\c:….main foldersubfoldersubsubfoldersubsubsubfoldertexfile.csv
then open that text file , find and copy range to the master worksheet
do until all text file are copied then move to next subsubsubfolder…
keep doing that job untill all special text file in the main folder are copied to the master worksheet
Kind a lost of thing to do here , buthope you can help me out,

Thanks and truly appreciate !!
xh57

Reply | Quote

Try changing this line “Filename = Dir(Path & “*.csv”)” to “Filename = Dir(Path & “specialvvv*.csv”)”

cheers, Paul

Reply | Quote

Thanks Paul for quick respond ,
I need some help here :
in my desktop I have a folder A, In the folder A it has multi sub_folders such as A1,A2…..An, each sub-folder they have sub sub- folder then, in the very last sub sub….sub folder is contain many csv files + xls file .. with different name such as: ” blah.. blah & “my country” & blah …blah & .csv” or ” Blah… blah Blah & “my country” & .csv” or “my country” & blah blah ….blah blah .csv”

I would like to ONLY open any CSV file which contain string “my country” to do code , the rest csv or xls or word files is just ignore them,
I try to use the code in this threat but somehow still get stuck , could anyone can help me with this,
Many thanks,
xh57

Reply | Quote

Hi xh57

I have a csv processing tool that you could probably be adapted to meet your needs.

I have attached the processing tool and a sample of 100 csv files you can use for testing the tool.
Copy these attached files to a folder of your choice, and unzip the test csv files to a folder of your choice etc etc.

The tool generates a list of files that match your file pattern e.g “*my country*.csv”.
Sub-folders can be included in the search.
Data from each matching file is then imported into a sheet named [Import], and then appended to the master sheet [Data].

Please let us know if this does what you want.

zeddy

Reply | Quote

Where does it get stuck?

cheers, Paul

Reply | Quote

Thank both for prompt advise ,
I will try them ,
I truly appreciate your help,

Xh57

Reply | Quote

I can’t see how to do that in any meaningful way – you can’t easily read non-Excel files into Excel.
Can you be more specific about exactly what you are trying to do?

cheers, Paul

Reply | Quote

I can’t see how to do that in any meaningful way – you can’t easily read non-Excel files into Excel.
Can you be more specific about exactly what you are trying to do?

cheers, Paul

Hi,
I am having a website in which one can able to see all the documents related to bussiness needs, more specifically, some 15-20 vendors are there and this documents are deposited there with vendor wise. In each vendor name folder, different types of doc are resided( excel, ms word, png, jpeg format file), I am trying to fetch all the subfolder and sub-subfolder present in different vendor doc in single macro sheet, using loop through directory, such that when i click on action button from the 20-25 vendors name, it fetches all the doc related to that particular vendor, it shows all the file in excel, one by one, if click cancel button in pop up window, it should close, and if we click on next button in pop window it will allow the user to see successive doc present in that vendor doc.(some 500-1000 files are there).

This reduces the extra effort of clicking every single vendor file, and again selecting particular file from subfolder or sub sub folder.Is it possible to get the files by writing the link of of the main vendor file, and use some switch condition or fstream file handling command so that by seeing the loop macro fetches the entire files related to particular vendor main file or need to write link of every single subfolder or sub sub-folder, to fetch the files in macro?. Please help me out on this.

Regards,
abhchakr

Reply | Quote

Because the files are in many formats you can’t really do this using one product (Excel), because it can’t display all the files.
Once you are displaying a file you need to close the displaying app before opening the next file, again something Excel can’t easily do – nothing can easily do it.
I think you are better to stick with doing it manually as it’s relatively easy for a human to manage.

cheers, Paul

Reply | Quote

hello everyone,
I am trying to create loop, so that the on running macro the code scroll through multiple files based on the file name in cell(“A1:A100”) and copy data from the files to masterfile in the corresponding column of the selected file name.

below is the code i used,
but unable to achive what is required.

Sub Create_actualstatus_Summary()
Dim folderPath As String
Dim fileName As String
Dim thisWorkbook As Workbook
Dim workorderno As String
Dim rowOffset As Long

‘Folder containing daily dated workbooks for a month – CHANGE AS REQUIRED

folderPath = “Z:\documents\Year \order\order based link”
Set cRange = Range(“E1”)
Set thisWorkbook = ActiveWorkbook

If Right(folderPath, 1) <> “\” Then folderPath = folderPath & “\”

rowOffset = 0
fileName = Dir(folderPath & cRange & “*.xlsm”)
Do While fileName <> “”

‘Copy data from dated workbook to associated row in summary sheet

Workbooks.Open folderPath & fileName

With thisWorkbook.Sheets(“Status”).Range(“f4”)
.Offset(rowOffset, 0).Value = Sheets(“PHC”).Range(“i2”).Value

End With

ActiveWorkbook.Close savechanges:=True

‘Get next file name

fileName = Dir
Loop

MsgBox “Finished”

End Sub

Any Help on this will be highly appreciated.
Thank u in advance.

Reply | Quote

You can’t use cRange as the file name because it’s a range. Try cRange.Value

DIR returns files in a folder matching a pattern. As you’ve named the file specifically you don’t need to use DIR, unless you want to collect everything in a particular folder.

Return a handle for the opened file to allow you to use the wb by name.
wbRefFile = Workbooks.Open folderPath & fileName
wbRefFile.Close savechanges:=False

No need to use WITH for a single operation.
thisWorkbook.Sheets(“Status”).Range(“f4”) = wbRefFile.Sheets(“PHC”).Range(“i2”).Value

Note: quotes in the above text will need to be replaced in your code because the forum turns then into start and end quotes.

cheers, Paul

Reply | Quote