+ Reply to Thread
Results 1 to 13 of 13

Macro to open file, copy paste as values and save as excel html

  1. #1
    Registered User
    Join Date
    02-15-2010
    Location
    Limassol, Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    6

    Macro to open file, copy paste as values and save as excel html

    Hi, any help with the following would be greatly appreciated:

    I have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:
    - Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)
    - Copy paste as values
    - Save this as excel html in the same folder as original excel files (keeping the original file name)
    - Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)
    - Since there will always be xHtml files with same name need the macro to replace the excisting file

    My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.

    Since this routine is to be run almost daily the macro should run all files, instead of one by one.

    I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.

    George

    Windows XP Pro, Office Pro 2003

    Note: I wonder whether a solution could be to record a macro for each file and create another macro to run all the others. But then again I need help with the macro to run the other macros.
    Last edited by gsav; 02-18-2010 at 07:16 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to open file, copy paste as values and save as excel html

    Put this code into a new module in a blank workbook, then run it. Be sure to edit the fPath at the top of the macro to the correct path to your files.
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 02-17-2010 at 02:45 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    02-15-2010
    Location
    Limassol, Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Macro to open file, copy paste as values and save as excel html

    Thanks JB for your quick answer.

    Did as you said, copied the code in the module of a new workbook and edited fpath (fPath = "C:\Documents and Settings\Administrator\Desktop\macro_testing") but when I run the macro nothing happens.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to open file, copy paste as values and save as excel html

    My fPath had a \ at the end. Did yours?

    Download the code above again, I made a small tweak that may help.

  5. #5
    Registered User
    Join Date
    02-15-2010
    Location
    Limassol, Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Macro to open file, copy paste as values and save as excel html

    Copied the changed code and added the missing back slash at the end of fpath.

    When I run macro I get a Microsoft Visual Basic 400 error with a red X. The help on the error button is not showing anything.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to open file, copy paste as values and save as excel html

    Try again:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    02-15-2010
    Location
    Limassol, Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Macro to open file, copy paste as values and save as excel html

    Thanks Jerry, we are almost there. Macro runs as asked except one thing:

    The name of the file is insertet in the first row of the xlHtml file, is there any way to avoid this?

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to open file, copy paste as values and save as excel html

    Sorry, I have no idea.

  9. #9
    Registered User
    Join Date
    02-15-2010
    Location
    Limassol, Cyprus
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Macro to open file, copy paste as values and save as excel html

    It is ok I will format the row so that it is not shown,

    Thanks much for your help, it saved me a lot of manual work.

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to open file, copy paste as values and save as excel html

    Glad to help where I could.

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

  11. #11
    Registered User
    Join Date
    03-18-2011
    Location
    qatar
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Macro to open file, copy paste as values and save as excel html

    i need to run a macro to open more than 1000 excel files in one folder to add 3 rows in diff location at each file and save the file and open the next file to do the same
    note all file are protected with one password "same at all files"

    i wish to receive your replay asap

    thx in advance

    worth mentioning that my knowldage wit
    h VB is 0
    Last edited by moh_zah99; 03-18-2011 at 10:10 AM.

  12. #12
    Registered User
    Join Date
    03-18-2011
    Location
    qatar
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Macro to open file, copy paste as values and save as excel html

    Quote Originally Posted by moh_zah99 View Post
    i need to run a macro to open more than 1000 excel files in one folder to add 3 rows in diff location at each file and save the file and open the next file to do the same
    note all file are protected with one password "same at all files"

    i wish to receive your replay asap

    thx in advance

  13. #13
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to open file, copy paste as values and save as excel html

    Quote Originally Posted by moh_zah99 View Post
    i wish to receive your replay asap

    Is that your wish? Then why did you post question where virtually no one will ever see it? In someone else's thread? Not only is that against the rule, you shot yourself in the foot. New threads gets the most attention, not ones with 10 replies already on the original topic.

    First of all, welcome to the forum. Secondly, be sure to read through the Forum Rules so you can use and follow them effectively. You broken rule #2 on your first post, not a good start, eh?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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