CODEWITHSUNDEEP
excelvbams-access
LovetoLearn
LovetoLearn

Reputation: 41

Looping through different kinds of files in folder (VBA)

I have a question regarding looping through a folder of two different kinds of files: xlsm and mdb (Access).

I have currently written a macro that would open 1 single xlsm file and 1 single mdb file before copying some data from the xlsm to mdb file and then saving the mdb file.

Now, I would like this process to repeat through a folder that has 50 xlsm files and 50 mdb files. They have the same names, so for example the loop should do this:

  1. Open both xlsm and mdb files called "2001".
  2. Perform copying and pasting etc from xlsm to mdb (I have written this part).
  3. Save the mdb file.
  4. Close both xlsm and mdb files called "2001".
  5. Repeat steps 1-4 for "2002", "2003", etc in the folder.

I am really new to VBA so much help is appreciated! Looking forward to any guidance at all. Merry Christmas!

Upvotes: 2

Views: 598

Answers (1)

Velid
Velid

Reputation: 104

I just did today sample code for listing JPG files in folder, you can adopt and modify to do exactly what you like it to do, but would be very hard to give you exact code without being able to see your solution.

Public Sub listImages(folderPath As String)

    'define variables
    Dim fso As Object
    Dim objFolder As Object
    Dim objFolders As Object
    Dim objF As Object
    Dim objFile As Object
    Dim objFiles As Object
    Dim strFileName As String
    Dim strFilePath As String
    Dim myList As String

    'set file system object
    Set fso = CreateObject("Scripting.FileSystemObject")

    'set folder object
    Set objFolder = fso.GetFolder(folderPath)

    'set files
    Set objFiles = objFolder.files
    Set objFolders = objFolder.subfolders

    'list all images in folder
    For Each objFile In objFiles

        If Right(objFile.Name, 4) = ".jpg" Then
            strFileName = objFile.Name
            strFilePath = objFile.Path
            myList = myList & strFileName & " - " & strFilePath & vbNewLine
        End If

    Next

    'go through all subflders
    For Each objF In objFolders
        Call listImages(objF.Path)
    Next

    Debug.Print myList

    Set objFolder = Nothing
    Set objFile = Nothing
    Set fso = Nothing


End Sub

Upvotes: 2

Related Questions