dcsimg
Results 1 to 5 of 5

Thread: Help With QR Code VBA Macro Modifications for Word Mail merge purposes

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2019
    Posts
    3

    Help With QR Code VBA Macro Modifications for Word Mail merge purposes

    Hello,

    Im currently looking to see how i can modify a macro i found from a tutorial online which deals with QR codes. The VBA macro currently generates the QR codes and adds them to the sample worksheet.

    Listed below are the goals im trying to achieve:

    1. writing data from each rows column to the QR code

    For example in the sample sheet the data is "Names", "ID #", and "Parent"
    Say that the data for each is as follows: Jane Doe, 1, and Mary Joe respectively

    The format i would like to generate for the QR code when scanned is:
    Name: Jane Doe
    ID number: 1
    Parent: Mary Joe

    Currently the example is set up to write data only from one column and im not sure how to modify the code to take each column and have the predefined text for the data being shown


    2. Changing column to which the QR Code image is saved to:

    Currently the code writes the QR image to E4 to E21 however if i add more data before column e the images will no longer be added to the excel sheet
    Therefore i wanted to see what part of the "PUtTheQR" function i need to modify to control the column the images are stored to when running the macro

    3. How to grab QR Code image and insert it as a merge field in word

    Once i can solve issues one and two my final goal is to be able to grab the QR Code image and pass it to a word doc as a mergefield

    This way i can generate the word doc for each data set with a unique QR Code


    Listed below is the youtube Tutorial and sample data

    Any help would be appreciated, thanks!

    The Data_2.zip
    https://www.youtube.com/watch?v=qzqaHBgLuO0
    https://www.youtube.com/watch?v=-37BAGKzOv8

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,444

    Re: Help With QR Code VBA Macro Modifications for Word Mail merge purposes

    2. if you want column D to be in the same place but put some other columns inbetween, increase the offset
    Code:
    val = ActiveCell.Offset(0, -1).Value
    if you want the qr in column H make it 0, -4
    or you can specify which column you want to use and avoid relying on the active cell

    also, i do not believe you need the extra column (D), as it can easily be managed without

    1. you can concatenate a string to submit to the qr like
    Code:
    ' for each row, where rw is the row number
    qrdata = "Name: " & cells(rw, 1) & vbnewline & "ID number: " & cells(rw, 2) & vbnewline & "Parent: " & cells(rw,3)
    '    then change the line below like
    QR = "http://chart.googleapis.com/chart?chs=" & Size & "x" & Size & "&cht=qr&chl=" & qrdata
    currently the qr data is actually jane doe.png rather than jane doe
    and the qrfile is saved as jane doe.png.png

    most of the above is quite straight forward and for myself i would simplify quite a bit

    i will look at the word document merge later
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2019
    Posts
    3

    Re: Help With QR Code VBA Macro Modifications for Word Mail merge purposes

    For some reason the "vbnewline" does not create a new line

    When concatenating the text the QR code shows the info in a single line


    Also for "Changing column to which the QR Code image is saved to" item if I change the offset to any other value but -1 it will not print the QR Code to the excel sheet

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2019
    Posts
    3

    Re: Help With QR Code VBA Macro Modifications for Word Mail merge purposes

    For some reason the "vbnewline" does not create a new line

    When concatenating the text the QR code shows the info in a single line


    Also for "Changing column to which the QR Code image is saved to" item if I change the offset to any other value but -1 it will not print the QR Code to the excel sheet

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,444

    Re: Help With QR Code VBA Macro Modifications for Word Mail merge purposes

    For some reason the "vbnewline" does not create a new line
    maybe you need a <br/> for a new line?
    on researching and testing use %0A like
    Code:
        qrdata = "Name: " & Cells(rw, 1) & "%0AID number: " & Cells(rw, 2) & "%0AParent: " & Cells(rw, 3)
    Also for "Changing column to which the QR Code image is saved to" item if I change the offset to any other value but -1 it will not print the QR Code to the excel sheet
    i belive the whole setup of the woksheet design is rather messy, but on testing, changing the offset does work correctly

    what ever column the acticecell is in requires the offset to the name of the .png file
    the helper column makes it easier to make the formula, but it is as easy to use the name column then add the.png in the showpic function, in which case the offset would always be the activecell.column -1
    Code:
    ActiveCell.FormulaR1C1 = "=ShowPic(RC[-" & (ActiveCell.Column - 1) & "])"
    the offset is always automatic in this case

    personally i would want to get rid of all the code relying on the activecell, to using full qualified ranges
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width