CODEWITHSUNDEEP
excelms-accessvba
isic5
isic5

Reputation: 191

Vba code in access that loops through all excel files in a folder, opens, saves and closes them

I am working on a vba script that I can deploy in Access that opens, saves and closes all excel files in a certain folder.

So far I have this code, which is kind of working, but has some issue. One is that it actually visibly opens excel and after the loop is done an empty, kind of weird looking excel is still open. With this I also noticed that in the taskmanager under processes this will result in running excel processes even though no excel instance is open.

Secondly since the format is xls I was dealing with the excel compatibility mode, not sure if I found the best solution for that as I sometimes get the error message afterwards now that the file extension and format is not the same.

Also I am just looking for some general improvements to the code as is.

This is my code: 

Sub demo()
Dim directory As String, fileName As String
Dim Mywb As Workbook
Dim app As New Excel.Application
app.Visible = True
directory = "C:\Users\test\FolderA\"
fileName = Dir(directory & "*.xls")

Do While fileName <> ""
Workbooks.Open (directory & fileName)
fileName = Dir()
ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Loop
End Sub

Upvotes: 0

Views: 81

Answers (1)

QHarr
QHarr

Reputation: 84465

I will address the "weird" looking Excel.

You want app.Quit at the end to ensure you close the opened Excel application.

If you don't, you will end up with a performance drop and eventual crashes as your resources are tied up with unclosed application instances.

Upvotes: 2

Related Questions