looping through files and
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Looping through files AND closing the save box | Excel forum

Looping through files AND closing the save box | Excel forum

resolvedResolved · Urgent Priority · Version 2010

Dominique has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course

Looping through files AND closing the save box

Hiya, would be grateful for your help please as I need to do the following:

For each file in a folder called Test Month RTT, open it, copy row B21:BC21, paste this into a file called Monthly RTT (which the macro will be stored in), close the first file without saving.

The following was suggested at training & I have found online to loop through the files

Sub LoopThroughFiles()
Dim StrFile As String
StrFile = Dir("c:\testfolder\*test*")
Do While Len(StrFile) > 0
[WHATEVER NEEDS DOING TO EACH FILE]
Loop
End Sub

But I'm not sure how to start the process, e.g. how to open the first file in that folder. The following is what I want to do each file but I can only do this if I know what the name of the file is:

Workbooks.Open Filename:= _
"R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\Audiology (Paeds) Completed.csv"
Range("B21") = ActiveSheet.Name
Range(Selection, Selection.End(xlToRight)).Select
Range("B21:BC21").Select
Selection.Copy
Windows("RTT macro v2 test.xlsm").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Windows("Audiology (Paeds) Completed.csv").Activate
Application.DisplayAlerts = False
Windows("Audiology (Paeds) Completed.csv").Close
Application.DisplayAlerts = True

Also when trying to suppress the the 'Do you want to save...' message, I have got the following code which works if I know the file name:

Windows("Audiology (Paeds) Completed.csv").Activate
Application.DisplayAlerts = False
Windows("Audiology (Paeds) Completed.csv").Close
Application.DisplayAlerts = True

But I'm not sure what I should replace the file name with to work in the loop (e.g. "*csv*" doesn't work)

I've spent quite a lot of time playing around with this with no joy so would be grateful for your help please.

Many thanks!

Kind regards,

Dom

RE: Looping through files AND closing the save box

Hi Dom,

Thank you for the forum question.

I am training today but I can give you some ideas which may help you.

Have a look at the code below. I have not been able to test it but I am in the office tomorrow and I will have a closer look at it.

Sub LoopThroughFiles()

Dim StrFile As String

StrFile = Dir("R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\" & "*.csv")
Do While Len(StrFile) > 0
Workbooks.Open Filename:= _
"R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\" & StrFile 'this will open the file stored in the variable StrFile

[what you need to do]



StrFile.Close savechanges:=False 'this will close and not save the changes
StrFile = Dir() 'this will store the next file name in the variable

Loop
End Sub





Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Looping through files AND closing the save box

Hi Jens, thanks for getting back so quickly. For some reason an error message is coming up even when stepping through saying 'Compile error: invalid qualifier' for the 'StrFile' in the line: StrFile.Close savechanges:=False

I've tried adding: Windows("Strfile").Activate
before this to make sure it activates that file before closing, but it doesn't seem to make any difference. So the full code looks like:

Sub LoopThroughFiles()
Dim StrFile As String


StrFile = Dir("R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\" & "*.csv")
Do While Len(StrFile) > 0
Workbooks.Open Filename:= _
"R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\" & StrFile 'this will open the file stored in the variable StrFile

Range("B21") = ActiveSheet.Name
Range(Selection, Selection.End(xlToRight)).Select
Range("B21:BC21").Select
Selection.Copy
Windows("RTT macro v2 test.xlsm").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste

StrFile.Close savechanges:=False 'this will close and not save the changes
StrFile = Dir() 'this will store the next file name in the variable

Loop
End Sub

Any ideas?

Thanks!

Dom

RE: Looping through files AND closing the save box

Hi Dom,

I have changed the macro a bit and hopefully this will do the job.

I have added another variable WKbook to store the reference to the source workbooks.

Every time you need to refer the the source workbook you will need to use WKbook (if you want to activate the workbook type WKbook.Activate),(If you want to do something in the first sheet in the workbook type WKbook.Sheets(1).Range("B21:BC21").Select)



Sub LoopThroughFiles()
Dim WKbook As Workbook 'I have added a new variable WKbook

Dim StrFile As String

StrFile = Dir("R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\" & "*.csv")
Do While Len(StrFile) > 0

Set WKbook = Workbooks.Open("R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\" & StrFile) 'this will open the file stored in the variable StrFile

[what you need to do]



WKbook.Close savechanges:=False 'this will close and not save the changes
StrFile = Dir() 'this will store the next file name in the variable

Loop
End Sub

Please let me know if this is not doing the job.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Looping through files AND closing the save box

Hi Jens, that's great you've cracked it! Can't believe how much time that will save.

I do have one small issue where I only have one row of column headings and am using

Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select

to add data to the row under the lowest populated row (i.e. the heading in the first instance, and then offsetting), but if I only have the column headings in it goes right to the bottom of the page. Assume I may have to do an if function (e.g. if A2 = blank, paste, if not do selection.end(xldown etc)?

Thanks! Sorry I'll leave you alone soon!

Dom

RE: Looping through files AND closing the save box

Hi Dom,

I am happy to help you. You can ask as many questions you like.

Have a look at the two options below. If I understand you right you will be able to use both.

Sub Test()

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select


End Sub

Sub test2()

numrows = Range("a1").CurrentRegion.Rows.Count + 1

Range("a" & numrows).Select

End Sub


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Looping through files AND closing the save box

Hi Jens,

Sorry I'm struggling again! I've tried to move this macro (just by copying & pasting the code) to the 'real' file I want to use and it's not playing ball now, even after renaming the real file to the previous file name. So I currently have:

Sub LoopThroughFilesv2()
Dim WKbook As Workbook
Dim StrFile As String

StrFile = Dir("R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\" & "*.csv")
Do While Len(StrFile) > 0

Set WKbook = Workbooks.Open("R:\Provider Performance Team Warwick HSE\RTT\RTT Data\Test Month RTT\" & StrFile) 'this will open the file stored in the variable StrFile

Range("B21") = ActiveWorkbook.Name
Range(Selection, Selection.End(xlToRight)).Select
Range("B21:BC21").Select
Selection.Copy
Windows("RTT macro v2 test.xlsm").Sheets("Reporting Mth RTT data").Activate
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste

WKbook.Close savechanges:=False 'this will close and not save the changes
StrFile = Dir() 'this will store the next file name in the variable

Loop
End Sub

At the point where it selects Range("B21") = ActiveWorkbook.name

It is inserting the ActiveWorkbook.name of the WKbook file into B21 of the 'RTT macro v2 test' file rather than the WKbook file. I've tried WKBook.activate first but this doesn't seem to solve it, I can't work out why it's reverting back to the RTT macro v2 test file when there is no coding for it to do so. Any ideas?

Thanks!

Dom

RE: Looping through files AND closing the save box

Hi Dom,

I am running a course today and I am not able to test it but I am almost sure that if you change:

Range("B21") = ActiveWorkbook.name

To:

Range("B21") = WKbook.name

You will get what you want.

Please let me know if not.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Looping through files AND closing the save box

Hi Jens,

That's great, all sorted. Many thanks!

Kind regards,

Dom

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel training class and other training courses.

Excel tip:

Line breaks in a cell

You can control the line breaks for multiple-line headings or labels in your Microsoft Excel worksheet, just like you do in Microsoft Word. Here's how to do it.

Click the cell where you want the label or heading to appear.
Type the first line of information.
Press ALT+ENTER.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.07 secs.