How to change directory when performing a loop in VBA?

Question

I would like to go up one level to process the following loop. I thought I could use ChDir but I see that when I call the files in, I would be in the incorrect directory. Any help would be appreciated.

I would like to generalize the path because the directory is always changing and is different for other users.

Though, here is a reference of how I would like the code to work.. The files I am processing would be in Data1 and this VBA module would be in Processing, see below. So I would need to go up one directory, to Data1, to pull the files, convert them, and then save the converted files in the same directory, Data1.

C:\Users\Documents\CDRL\Data1\Processing

Sub loopFiles()

Dim fso As New FileSystemObject
Dim fil As File
Dim fold As Folder
Dim yourfolder As String

Set fold = fso.GetFolder(Application.ActivePresentation.Path)

ChDir ".."

For Each fil In fold.Files

    If InStr(1, fil.Name, ".potx") > 0 Then
        Application.Presentations.Open fil.Path
        ActivePresentation.SaveAs Replace(fil.Path, ".potx", ".pptx"), ppSaveAsDefault
        ActivePresentation.Close

        fil.Delete True
    End If

Next fil

End Sub

well obvious if the dir change needs to occur within the loop it should be in it.... — Doug Coats, Jun 13 '17 at 22:22
Continuation of https://stackoverflow.com/questions/44528624/how-to-convert-all-potx-files-to-pptx-files-with-vba — Dan Donoghue, Jun 14 '17 at 23:29

score 1 · Answer 1 · answered Jun 13 '17 at 23:01

1

You don't really need to use chdir, you can just put the dir in the saveas:

Sub loopFiles()

Dim fso As New FileSystemObject, fil As File, fold As Folder, yourfolder As String, UpOneDir As String

Set fold = fso.GetFolder(Application.ActivePresentation.Path)

For Each fil In fold.Files

    If InStr(1, fil.name, ".potx") > 0 Then
        Application.Presentations.Open fil.Path
        UpOneDir = Left(fil.Path, Len(fil.Path) - Len(fil.name) - 1 - Len(Split(Left(fil.Path, Len(fil.Path) - Len(fil.name) - 1), "\")(UBound(Split(Left(fil.Path, Len(fil.Path) - Len(fil.name) - 1), "\")))))
        ActivePresentation.SaveAs UpOneDir & Replace(fil.name, ".potx", ".pptx"), ppSaveAsDefault
        ActivePresentation.Close

        fil.Delete True
    End If

Next fil

End Sub

I created a variable called UpOneDir which takes fil.path and removes the fil.name from it. I then split the result using the "\" and work out the length of the value of the upper boundary of the array, I then take the left side of the string minus that length we just calculated.

It looks really messy but it works. There may be an easier way to do this using the file system object, I just don't use it enough to know off the top of my head.

You could make it look pretty if you really wanted to by stepping out the parts of the UpOneDir creation into little parts but I get the sense that this will be a once use thing for you to simply convert some files to the current powerpoint format.

answered Jun 13 '17 at 23:01

Dan Donoghue

6,056
2
18
36

I need to go up one directory to open first and then I want to save it in that same directory – t.breeze Jun 14 '17 at 15:07
That makes no sense, you are polling files in a dir but you want to go up a dir before opening them? by the very nature of that, the files won't be in that dir unless you have duplicated every single file into the parent folder (which my code would still work in theory as it would work on the original not the duplicate). Need more details before I can help further. – Dan Donoghue Jun 14 '17 at 22:27
hmm there is a misunderstanding.. I have a folder (Processing) on my desktop with multiple processing files within, matlab, vba, etc. I copy this folder into another folder (Data1) full of hundreds of files, which is always a different folder for each data set. Then I open my processing files which are one level down, process data, then delete the folder Processing from the Data1 folder. This is so I can take my processing folder with me anywhere to run through these data sets. Also keeps it easy to share with others. – t.breeze Jun 14 '17 at 22:54
I think what you need to do is to edit the question and put a file structure layout in there. Then we can better understand what you need. – Dan Donoghue Jun 14 '17 at 23:30
Ok, I edited the description. As I mentioned I would like to keep the path generalized since it will change for each user. – t.breeze Jun 15 '17 at 00:23

How to change directory when performing a loop in VBA?

1 Answers1