+ Reply to Thread
Results 1 to 19 of 19

Batch job. Open a lot of excel files, delete the same values from each, save, then close

  1. #1
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Batch job. Open a lot of excel files, delete the same values from each, save, then close

    Hello.

    Would it be possible to make a script to open a lot of excel files, delete certain values, save, then close?

    I have to edit a lot of excel (xls) files each month, and its the same cells thats needs deleting, a script wich could do this would save me a LOT of time. The cells that need deleting are: B8 to P8, B9 to P9, B10 to P10, B11 to P11, B12 to P12, B13 to P13, B14 to P14.

    Any help would be much appreciated.
    Thank you

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Do you want the cells to be cleared or the cells to be deleted and the contents of the below cell moved up?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Just the cells to be cleared. (The values deleted)

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Try this
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button

  5. #5
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Hello, and thank you for your help, i have done everything, just edited to use the correct path (myPath = "C:\test") , but when i run the macro nothing happens, any ideas?

    Edit:
    I also changed Sheet1 to Ark1, as my excel is in Norwegian When i did that, the area i wanted to delete got Marked when running the macro, but nothing was deleted, and it didnt do anything to the other .xls files in the dir. This is how my macro looks:

    Please Login or Register  to view this content.
    Last edited by greendale; 10-30-2013 at 05:03 AM.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Where have you placed your code?

  7. #7
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Just as you said:

    "Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste" then i saved, and opened macro, and run.

  8. #8
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    This is how it looks: Check attached screenshot
    Attached Images Attached Images
    Last edited by greendale; 10-30-2013 at 06:21 AM.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Can you please upload a sample of your file so i can check?

  10. #10
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    I actualy got it working with a VBS script:

    Please Login or Register  to view this content.
    I have to write the filename/path of each .xls which is a bit annoying, but i only have to do it once, because the files have the same filnames from month to month.
    Would be better if the vbs could do some kind of *.xls search in a folder.
    I use office 2013 by the way.

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    My code works fine at my end and opens up each file. Are you using a MAC?

  12. #12
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    No mac, windows 7, office 2013. I will try it on my home computer just to see if it works there.

    Thank you for your patience.

  13. #13
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Okay, so on my home computer, it worked on one of the xls files, but not the one i ran the macro from, weird, even though i had 10 xls files in the dir, it only worked on one. I will upload an example later today.

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    You have put the macro in an independent workbook right? This macro will then open up each of the files where the contents need to be cleared, clear it and save and close.

  15. #15
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Yup, but it didnt work, except for on other excel file, weird.

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Can you please attach a few of your files so i can test it? It works fine at my end.

  17. #17
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Here are my workbooks. The xlsm is the one with the macro i think.
    Attached Files Attached Files

  18. #18
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    The line

    Please Login or Register  to view this content.
    are looking for "xls" files but your uploaded samples are ".xlsx" files and the script that "works" are also checking for a ".xlsx" fie so try to change arluette's code to

    Please Login or Register  to view this content.
    and test this instead.

    Alf

  19. #19
    Registered User
    Join Date
    10-25-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Batch job. Open a lot of excel files, delete the same values from each, save, then clo

    Ah, i should have seen that, geez, it worked fine now, thank you. Thank you both

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Open, Save and Close Excel Files
    By az! in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2011, 04:19 AM
  2. VBA to open, change, save and close all files in DIR
    By jdkoeppen in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-18-2011, 03:35 PM
  3. Using excel vba to open, save as, and close embedded pdf files
    By mcledavid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-22-2009, 05:22 PM
  4. Open excel file, enter values, save & close
    By JCanyoneer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-04-2005, 12:06 PM
  5. Open Save & Close All Files in Dir
    By Diane Alsing in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-31-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1