VBA to Loop Through Files

Question

Hi.

I have a series of identical workbooks in a folder in the following location: C:\Users\hughesel\Desktop\Test

I want to create a macro which will open each of the files in turn and select all of the contents on the QPI sheet and paste special values the information (to remove the fomulas.  The QPI sheet is password protected so that would need to be turned off too

• the password is test

I then want the file to be saved and closed before the macro moves on to the next file in the folder.

I have tried to peice together bits of code myself but with no success.

Is anyone able to help?

Many thanks.

Kind Regards.

Liz.

Answer 1

If the workbooks contain automatic macros (for example Workbook_Open in the ThisWorkbook module), you can suppress them:

Sub ProcessWorkbooks()

    Const strPassword = "test"

    Const strPath = "C:\Users\hughesel\Desktop\Test"

    Dim strFile As String

    Dim wbk As Workbook

    Dim wsh As Worksheet

    ' Temporarily hide screen activity

    ' You may still see some flickering

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    ' Get first filename

    strFile = Dir(strPath & "*.xls*")

    ' Loop

    Do While strFile <> ""

        ' Open workbook

        Set wbk = Workbooks.Open(Filename:=strPath & strFile)

        ' Reference to QPI sheet

        Set wsh = wbk.Worksheets("QPI")

        ' Remove protection

        wsh.Unprotect Password:=strPassword

        ' Replace formulas with values

        With wsh.UsedRange

            .Value = .Value

        End With

        ' Apply protection again

        wsh.Protect Password:=strPassword

        ' Close and save workbook

        wbk.Close SaveChanges:=True

        ' On to the next file

        strFile = Dir

    Loop

    Application.EnableEvents = True

    ' Display changes again

    Application.ScreenUpdating = True

End Sub

Answer 2

Hi.

The line that is highlighted is:

 Set wbk = Workbooks.Open(strPath & strFile)

It opens the first workbook in the folder but then doesnt perform the actions on it.

Liz.

Answer 3

When the error occurs, click Debug in the error message dialog.

This will take you to the Visual Basic Editor with the offending line highlighted in yellow.

Which line is that?

(You can click the Reset button on the toolbar or select Run > Reset to end the macro)

Answer 4

Hi.

Thanks for your help - this initially didnt work, but then started to work and now is not working again.

When I get an error it is a 1004 one which i dont know how to fix - any ideas?  It says select method of worksheet class failed.

Kind Regards.

Liz.

Answer 5

Try this macro:

Sub ProcessWorkbooks()

    Const strPassword = "test"

    Const strPath = "C:\Users\hughesel\Desktop\Test"

    Dim strFile As String

    Dim wbk As Workbook

    Dim wsh As Worksheet

    ' Temporarily hide screen activity

    ' You may still see some flickering

    Application.ScreenUpdating = False

    ' Get first filename

    strFile = Dir(strPath & "*.xls*")

    ' Loop

    Do While strFile <> ""

        ' Open workbook

        Set wbk = Workbooks.Open(strPath & strFile)

        ' Reference to QPI sheet

        Set wsh = wbk.Worksheets("QPI")

        ' Remove protection

        wsh.Unprotect Password:=strPassword

        ' Replace formulas with values

        With wsh.UsedRange

            .Value = .Value

        End With

        ' Apply protection again

        wsh.Protect Password:=strPassword

        ' Close and save workbook

        wbk.Close SaveChanges:=True

        ' On to the next file

        strFile = Dir

    Loop

    ' Display changes again

    Application.ScreenUpdating = True

End Sub