Consulting

Results 1 to 11 of 11

Thread: For Loop Label1 to LabelXX

  1. #1

    For Loop Label1 to LabelXX

    I have a macro that I wrote recently to populate a table in a word document from excel data,
    I used this script to pull the data from excel using labels,


    Private Sub CommandButton1_Click()
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    
    
    Set exWb = objExcel.Workbooks.Open("C:\Users\Brad\Desktop\expenses.xlsx")
    
    
    ThisDocument.Label1.Caption = exWb.Sheets("Sheet1").Cells(12, 2)
    
    
    exWb.Close
    
    
    Set exWb = Nothing
    End Sub


    The thing is, i'd like to loop through many labels...Labels1 to Labels36, and each time the cell location changes from 12,2 to 13,2 etc. How would I do this using an for loop or something similar?
    And better yet, is there a way to create the word table on the fly? I may not know the total number of rows, it could be 12, 16 or 36 depending on the excel data. Is it possible to create a label in the loop?
    Also, is it possible for the user to write down in the word document the file path, then have the vba script pull the path from there and then use it in the script?

  2. #2
    Why not simply use mail merge to populate the labels
    https://www.gmayor.com/merge_labels_with_word_2007.htm
    Note that this link is good for all versions of Word.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    I could, but the idea was to have an interactive document with the user

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    What kind of interactivity? There's nothing about the code you posted that suggests any.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Interactivity? Take a look at https://www.gmayor.com/Envelope_Label_Add_In.htm which will use Excel data laid out as you require.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    Quote Originally Posted by macropod View Post
    What kind of interactivity? There's nothing about the code you posted that suggests any.
    It's also just meant to be a VBA exercise. Do you know how to use a for loop for these labels?

  7. #7
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    That doesn't even go close to answering my question. Moreover, if it's "just meant to be a VBA exercise", find someone else's time to waste.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    Quote Originally Posted by macropod View Post
    That doesn't even go close to answering my question. Moreover, if it's "just meant to be a VBA exercise", find someone else's time to waste.
    There's no need to be hostile here, I'm just trying to learn a little bit more about VBA. I don't really understand much about it, and I was hoping to find some helpful kind people here who could point me in the right direction.

    As far as interactivity goes, there is a piece of my code that suggests that. There's a button, that's pretty much the limit of the interaction. Also, what I meant by 'vba exercise' is, I know that I can use other methods like mail merge, but I am trying to learn VBA. So I don't want to use any other options.

    I would really appreciate it if you could help me out though. I'm sure the solution is pretty simple, but for some reason I just can't for the life of me find anywhere online that states how to loop through labels in the way i've described. And i'm just really, really, not familiar with the language. Usually looping through something like that is pretty straight forward.

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    There are probably thousands of posts here (millions if you include other forums as well) showing how to implement loops in various situations. Do a search.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  10. #10
    Quote Originally Posted by macropod View Post
    There are probably thousands of posts here (millions if you include other forums as well) showing how to implement loops in various situations. Do a search.
    Oh I don't doubt that there is, but there isn't any threads which are specifically answering what I'm asking.
    Last edited by matlab4lyfe; 05-04-2019 at 02:32 PM.

  11. #11
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by matlab4lyfe View Post
    Look man, if you don't want to be of any help,you can show yourself the door.
    This forum is not a free coding service for those who are too lazy to do a bit of basic research and instead prefer to waste someone else's time for their amusement. Thread closed. And kindly take your attitude elsewhere.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

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