+ Reply to Thread
Page 1 of 7 1 2 3 ... LastLast
Results 1 to 15 of 98

Create code to recreate ActiveSheet contents at run-time

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Cool Create code to recreate ActiveSheet contents at run-time

    This is going to be a) difficult to explain and b) even more difficult to solve (but not impossible ). Any takers? :D


    EXPLANATION OF WHAT I WANT TO ACHIEVE:
    INPUT: An active worksheet with cells containing values, formatting, borders etcetcera
    OUTPUT: A VBA module containing code to re-create an (almost-) exact copy of this sheet

    Another way to describe it - I am after an equivalent of de-compiling code. Except IDE would examine the sheet and work out what code is required to create an almost exact copy of the sheet.


    THOUGHTS ON HOW TO ACHIEVE/POTENTIAL DIFFICULTIES:
    I am aware that solving this will be extremely difficult. But I can't see it as being totally impossible.

    At worst case scenario, IDE could be used to loop through each cell in the used range and examine the cell contents, cell border, cell formatting etc. (Of course that would be the least efficient way to do it. It would probably easier to examine the used range for contiguous "blocks" of cells containing the same similar formatting or borders etc. and then loop through these "blocks". e.g. IDE would loop through the used range and pick up all the cell borders, then it would loop through again and pick up all the cell values and so on).

    I am also aware that the output code would be a total mess but, in most cases, it would probably much easier to tidy the output code rather than try to recreate the sheet from scratch with Macro Recorder.

    If it makes it easier to solve this, I don't require an exact copy. Number formatting can be ignored. So could Conditional Formatting etc. In fact, Cell values, Cell formulas and Cell borders are the only essential items that I can think of.


    OPTIONAL - WHY DO I NEED THIS:
    I have a large number of XLSMs. I wish to convert a lot of these to XLAMs. Converting the "input sheets" to User Forms is tricky but not impossible. The big headache I am finding is how to dynamically recreate the "output sheets" in an active workbook. If this thread was solved, it would give me the nearest equivalent of macro recorder output on creating the said sheet - then it's just a matter of me manually tidying this code.
    Last edited by arlu1201; 06-28-2013 at 01:57 AM. Reason: Thread title edited on OP request.
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,636

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Why not you just duplicate the worksheet and clear the contents alone?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,131

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Or copy and paste it?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,021
    Just copy the sheet.
    If posting code please use code tags, see here.

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    @ Sixth Sense, Kyle123 & Norie:
    But I don't want to copy the sheet. I want to dynamically recreate the sheet.


    @ Everyone else:
    Folks, please don't try to "second guess" me on what I am hoping to achieve from this thread.
    I do have some practical uses for this code
    (supposing anyone is talented enough to pull off the solution ).
    So please refrain from posting any suggestions that involve copying sheets - that is not what the thread is asking!
    Thank you
    Last edited by mc84excel; 05-29-2013 at 08:14 PM.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,021

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Copying the sheet is dynamically creating the sheet.

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Norie View Post
    Copying the sheet is dynamically creating the sheet.
    ...

    I wont have the original sheets any more.
    I wont have the original workbooks any more.
    I need the code to recreate these sheets "on the fly". Not copied.

    To mark this thread as completed, I need IDE code that will produce an output that would be an extremely rough equivalent to what macro recorder would have produced after creating these sheets originally.

  8. #8
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I got it: you want to create a Macro that records what was on the old sheet and save it as vbs (for example). then use that macro later on to create an identical version of the old one. Am I right?
    Last edited by adyteo; 05-29-2013 at 05:03 AM.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,131

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I really don't get why you don't just copy and paste. The whole question is seriously over-complicating and over-engineering a simple operation

  10. #10
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by adyteo View Post
    I got it: you want to create a Macro that records what was on the old sheet and save it as vbs (for example). then use that macro later on to create an identical version of the old one. Am I right?
    YES! At least someone understood what I want to achieve!

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,621

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Kyle123 View Post
    I really don't get why you don't just copy and paste. The whole question is seriously over-complicating and over-engineering a simple operation
    +1 .
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by Kyle123 View Post
    I really don't get why you don't just copy and paste. The whole question is seriously over-complicating and over-engineering a simple operation
    Please read the latter section of post #5. Thank you.

  13. #13
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Quote Originally Posted by shg View Post
    +1 .
    Please read the latter section of post #5. Thank you.

  14. #14
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Angry Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    Before posting to this thread please read the latter section of post #5.


    I accept that very few people will ever have a use for this solution. However I am one of those few.

    If my explanation of what I want to achieve was so obtuse that it took a while before anyone even understood what I am looking for (thanks adyteo), how do you think my explanation of what I want this code for would fare? I'm not even going to try. (I can visualize what I want to do with the code, I'm just not good at putting this into words).

    If you can't/don't want to provide code towards helping solve this thread - then please don't post in this thread at all. Address the thread question or not at all.
    (I'm sorry I have to be so blunt but I don't want any more copy/paste suggestions nor do I want any more 'you don't need/why do you need this solution' posts).

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,021

    Re: Advanced VBA-Use IDE to examine sheet and work out code to dynamically create

    I actually understood what you meant from the get go.

    I didn't reply then because I couldn't think of an easy way to do it.

    Where and how are you going to store all the properties of each cell on the worksheet?

    Also, how are you going to extract that information in an efficient manner?

    One thought I did have was to do something with the XML files that make up a workbook but that would only apply to files that were OpenXML format.

+ Reply to Thread
Page 1 of 7 1 2 3 ... LastLast

Thread Information

Users Browsing this Thread

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

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