• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Manipulating excel from another file

Kumar Salui

New Member
Hello, I have 150+ excel files in one folder. Each file is having ten worksheets. I need to open up all excels and do some data manipulation from 10 sheets to create three new worksheets in each of the excel files. I have created the code for data manipulation and creating three new worksheets and dealing older ones for one individual sheet. Now I have to automate this for all 150+ files.

Can you please share an example code on how to open up each excel (.xlsx) file from a list located at an external .xlsm file, run the above code (you can just show one-line code for testing purpose), and then "save as" with a new file name? I am attaching the .xlsm file. Thank you!
 

Attachments

  • open excel file from path.xlsm
    9.1 KB · Views: 4
Hi
May help
Code:
Sub AllWorkbooks()

    Dim MyFolder As String    'Path collected from the folder picker dialog
    Dim myPath As String
    Dim MyFile As String    'Filename obtained by DIR function
    Dim wbk As Workbook    'Used to loop through each workbook
    On Error Resume Next
    'Opens the folder picker dialog to allow user selection
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder"
        .Show
        .AllowMultiSelect = False

        If .SelectedItems.Count = 0 Then    'If no folder is selected, abort
            MsgBox "You did not select a folder"
            Exit Sub
        End If
        MyFolder = .SelectedItems(1) & "\"    'Assign selected folder to MyFolder
    End With

    MyFile = Dir(MyFolder)    'DIR gets the first file of the folder

    'Loop through all files in a folder until DIR cannot find anymore

    Do While MyFile <> ""

        'Opens the file and assigns to the wbk variable for future use

        Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile)

        'Replace the line below with the statements you would want your macro to perform

        ' Starat your code

        ' End  your code

        wbk.Close savechanges:=True
        MyFile = Dir    'DIR gets the next file in the folder
    Loop
End Sub
 
Back
Top