+ Reply to Thread
Results 1 to 4 of 4

Looping Query on Long Code & Table of Data For Multiple Bookmark Population

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    25

    Post Looping Query on Long Code & Table of Data For Multiple Bookmark Population

    Hi All,

    I've a query regarding looping, and how best to integrate such functionality into an existing code that I have. Apologies, as I'm a little bit of a noob when it comes to these things. I have managed to fudge my way through and create spreadsheets and macros to date which have really helped with various things I've been trying to achieve - generally work-related. Although it takes a little time browsing the various forums and trying to locate other people who have had similar queries, do a bit of reading around certain points, amend existing code, do some testing and through trial and error generally achieve what I want - one thing that I've always struggled to get my head around is looping. I have managed to use this previously; however potentially on something more straightforward than what I am currently trying to do.

    The code is a real mess and quite lengthy, and so I won't insert it here (plus it has a fair amount of sensitive information in it, so probably best not to attach the document either). Hopefully this won't stop me from being able to get my point across. This currently allows a user to input various data, and then this data will be extracted and inserted into a number of bookmarks (127 of them) within a template document and then save a copy to specified location to produce a document that is ready to be sent out.

    Initially, there is just one column of data to fill out; however there is an option to add more columns (via another macro which prepares the next column for data entry). I would say on average, per project, there could be between around 10 - 30 columns of data. A document / letter would then need to be created for the majority of these (but not all) - one separate document for each column, with the bookmarks within populated from the data within that column and then saved. On a previous iteration of this workbook, I had a link at the bottom of each column which allowed you to generate the relevant document / letter for that column. However that meant following multiple links, clicking, waiting, moving onto the next etc. And this just seems on the face of it to be quite an avoidable issue, and one which I would have thought should be resolvable with the right bit of code.

    I have now added a row, with a dropdown which states "Include in Document Generation" - the dropdown options are simply "Yes" or "No". The intention is that once the various data has been entered across the various columns, that for those which the user would like a document / letter to be generated for, they simply select yes from the dropdowns in those relevant columns, and then press a button which runs the macro and this loops through those particular columns and generates a document / letter for each (populating the bookmarks within each one from the relevant column, then saving each document).

    I'm struggling however to apply my basic understanding of looping to this however. Can anyone point me in the right direction or suggest a process where this might be achievable? As a basic structure, I expect this to look something like as follows:

    ----------
    1. Initial declarations
    2. A little bit of housekeeping (unlock password protection, screen updating etc.)
    3. Set save folder location (for saving all documents to)
    4. Then this is where I think the loop bit will need to start from
    5. Open up MS Word and open a letter template document
    6. Set variable names as the value of numerous cells in a particular column
    7. Replace the bookmarks in the template document with the relevant data
    8. Save the document to the previously specified folder
    9. And then this is where I would like it to return to the beginning of the loop and repeat for the next column containing "Yes" in the relevant dropdown
    10. End Sub
    ----------

    I can find info about looping through and making amendments to the specific cells within a range - however I would have thought that range would be just the single row with the "Yes" / "No" dropdowns. I'm not sure how to expand this to then manipulate / extract data from elsewhere within that same column (and create multiple documents from a single template all based upon the data in those columns).

    Any advice on this would be most gratefully appreciated.

    Finally, apologies if this is not the normal way to pose such a question (i.e. without including the relevant code - I could include this, if it is to be necessary; however I just felt it's a little long and could perhaps just detract from the basic principles which I'm trying to fathom). If this is against forum etiquette, let me know and I will look to adjust accordingly.

    Many thanks in advance,
    Ady
    Last edited by Skins11; 05-12-2021 at 05:08 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: Looping Query on Long Code & Table of Data For Multiple Bookmark Population


    Hi,

    wrong forum section as here it's upon Excel …

  3. #3
    Registered User
    Join Date
    01-16-2014
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    25
    Thanks Marc,

    This is an Excel query - all of the code and data is within Excel - just then used to populate a template Word doc.

  4. #4
    Registered User
    Join Date
    01-16-2014
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Looping Query on Long Code & Table of Data For Multiple Bookmark Population

    Okay - I've actually looked into this a little more and found a solution that does what I need it to. It's actually not too complex; however as mentioned above, my understanding of looping procedures is fairly basic. That said, an example of the code which I got to work for this is as below in case any stumbles upon this query and wants to know the solution:

    Please Login or Register  to view this content.

+ 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. Multiple lookup & table population
    By ExcelHurtsMyBrain in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-06-2020, 03:09 PM
  2. Access Query/SQL display table code descriptions for multiple codes
    By WBravard in forum Access Tables & Databases
    Replies: 1
    Last Post: 02-04-2018, 07:40 AM
  3. [SOLVED] Excel VBA code to bring cell data into Word bookmark
    By DMA in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-14-2017, 03:06 PM
  4. Web query Looping from table selection
    By Lehoi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-07-2016, 12:59 AM
  5. [SOLVED] Clearing Data As Long and Looping
    By 5150 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-01-2014, 05:05 PM
  6. Find last row with data and pass the found row number to bookmark code
    By beenbee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-14-2014, 08:11 AM
  7. VBA Web Query Looping multiple outcomes
    By huey in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-10-2009, 07:16 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