VBA to Loop Through Files

Anonymous
2013-12-04T07:22:18+00:00

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.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

10 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.7K Reputation points MVP Volunteer Moderator
    2013-12-04T10:46:43+00:00

    Are you sure that is the highlighted line? From the error message that you reported I'd expect it to be one of the lines below that...

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-12-04T10:35:22+00:00

    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.

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2013-12-04T10:23:23+00:00

    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)

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-12-04T09:32:38+00:00

    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.

    Was this answer helpful?

    0 comments
  5. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more