Doubt If This Is Possible - But Here Goes

LearningVBA

New Member
Joined
Sep 16, 2006
Messages
14
I have approximatly 300 workbooks sent to me each month, all of which are saved to a holding folder (one folder for each month of the year). Is there an automatic way in which the files can be opened up in turn and the cells B33:AZ 5033 can be copied and pasted back directly as values before closing and saving the workbook. The worksheet is called "Summary".

I've had a look at old posts but cannot see what I'm after.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
Yes you can, but some questions first:

1) how do you know which folder you are going to access
2) where do you want the data pasted to?

this is an example of a macro that opens all the files in a particular folder and gets the date they were last modified. It would be easy to change this for what you want:

Code:
Sub GetMostRecentFile()
'macro to get all the dates of each file
'requires reference set to Microsoft Scripting runtime
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim i As Integer

        
    'set path for files - change for your folder
    Const myDir As String = "C:\Temp\"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and get date last modified. Output on WorkSheet
    i = 1
    For Each objFile In myFolder.Files
        With ThisWorkbook.Sheets("Sheet1")   'change for your sheet name
            .Cells(i, 1) = objFile.Name
            .Cells(i, 2) = objFile.DateLastModified
            i = i + 1
        End With
        
    Next objFile
    
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,533
Members
410,547
Latest member
htran4
Top