all right so i've been asked to do this video because apparently every other video is telling
people to use some sort of add-in so i'm going to show you how we can generate qr codes for
whatever type of data you have right now i'm going to use this hyperlinks so we're gonna
need to do a little bit of vba code for this but fortunately it's not really gonna be that much
so even if you're not a coder you should be able to follow so what we do need we need to
get to this website go qr dot me slash api i will link to this website underneath the
video if you want to get to it so here if you look through it there is this hyperlink that it
shows us as an example and if i click on this it will basically just show us if we go to this url
it will generate this qr code for us and that's exactly what we need so we need to copy this link
for this qr code go to our file and for now i'm not gonna use this data yet
i'm gonna go to a different tab for now i'll just paste that link here
and if you look in this link there is the actual hyperlink for this and then we have some
parameters like the size of this so you can see it's 150 150 and then there is the data that says
equals example so what we need to do if you want to replace it to be a different size you would
update that 150 by 150 part to do a different size and then finally we need to update this example
with our data so in my case that's going to be those urls in your case it could be whatever you
want this to be for the time being i'm going to leave this link alone and i'll make another tab
what we're going to do here first we need to basically
just size some rows here so i'll highlight bunch of rows and resize them let's say this size and
this size so this is where your qr code is going to go now what you want to do at this
point is do a little macro recording so to record a macro you need this developer tab
if you don't already have it activated you can go under file go under options
another options if you go to customize ribbon you will see there's this checkbox for developer for
me it's already checked there's nothing i have to do if you don't have it just check the box hit ok
you should have this tab now all i'm going to do here i'm going to just do a quick recording to add
a picture to the cell so what i'll do i'll click in the cell go to developer go to record macro
and i'll just keep it macro one that's fine i wanna make sure this stores it in this current
workbook this workbook i'm gonna hit okay and now we're recording so what i'm going to do here
i'm going to go under insert go under pictures and basically just add a picture here and any picture
you want to choose from your computer at this point so i'll just grab this picture hit insert
and as you can see that adds the picture now the important part here is to resize now
this picture for the cell so i'm going to go from this right side first and resize it
to the cell like this so you want to try to do this with one shot and take the second
and resize it to this don't worry if your picture doesn't look good
so once i have this picture in the cell i've recorded this macro all i'm gonna do now is
stop this recording so i'll go under developer stop recording and now if i go under macros
i should see that macro and i click it that should take me to the code that was recorded
to basically generate that particular picture so here for any of this active windows scrolls
i'm going to get rid of those we're not going to need that you'll see we do have this link for that
picture now what i'm going to do here i'm going to go on top here and i'll just create a file path
variable and i'll make it equal i'll do double quotes and i'll just basically just copy this
and paste it right here and then over here within this parentheses i'll just
replace it with this variable file path and i should probably type that correctly here
like that so file path here file path here so now with this code i'm gonna do a couple
of small customizations what i'm going to do i'm going to add a width block for this
so right here we'll do width like this remove this select and dot out of here and then
we'll go below this whole thing and do end that with block like this and then over here we don't
need the selection selection anymore we're just going to do dot whatever we have like this so
that's a little customization i'm going to do here i'm going to remove this comments
just so that this code is a little smaller so now we want to try what's going to happen if
we replace this path with that path that we had from that link so i'm going to go grab that link
so i had it over here so i'll just copy this
go back to that visual basic you can either click visual basic or go back to macros and click edit
whichever works for you i'm going to go back here and basically just remove this and paste
our other link so at this point we want to make sure that this
actually works the way it's supposed to because if we need to resize this we better do it now
so for me it's 150 150 i'm going to leave it at that if you want to change it like i said
make sure you change both so both height and width should be the same for your qr codes
so let's go to the sheet 3 delete the picture we already have click in one of these cells go to
macros and this time i'm going to click on this and round this macro and it may take a little
bit because it needs to go get that picture out of there and you can see we got the picture we placed
it in the cell but the size is a lot smaller than what i need so now i need to adjust my code to get
this size to be the correct size for this so i'm gonna get rid of it go to visual basic now because
the picture was smaller than what i need i need to basically make this scale larger if it was the
opposite you would pretty much do the opposite i'm literally just gonna try to do something like 1.25
and i'll use that same scale here let's try this again and see what happens so i'm going
to go under macros rerun this thing so as you can see that was too big so we basically have
to experiment until we get this to the right size so i'll just go back here we could do some other
things but this is probably going to be the easiest thing to do so i'll try 0.75 this time
so that's a lot better as you can see we
can resize it a little greater to get it to where we need this to be
so once you play around with this and get it to the size you actually want now we're ready to
finalize our code to generate bunch of qr codes instead of just one and to actually use the data
we need now the way i'm gonna do this i'm gonna make it happen in a way that if we go here and
select like this much we'll basically just put our qr codes right next to it to the right so
basically whatever data you select that's what we're going to use to actually generate qr codes
so let me just resize this really quickly here so i'll select all of these rows
resize these to give it some space for those qr codes that i'm going to put here
and for now for just testing i'll highlight two of these we'll go back to our
visual basic and we're going to have to do a little bit of adjustment here what we need to do
we need to basically loop through that selection that we highlighted and for that we'll go
above this code that we've done and we'll do for each cell in selection and then we'll go below
all of this code and then we'll do next cell which is this variable right here so this will now allow
us to loop through our selected cells and run this code over and over again for all the cells
so what i'm going to do instead of this example here i'm going to remove that example part
keep the equal sign and then i'm going to follow this with ampersand to join the value in that cell
so cell dot value and that would refer to the actual hyperlink right now that's in the cell
so this way we're basically adding dynamically the data part to this so that's good
now once we get that we want to insert that right next to it in this next column to the right so
what i'll do i'm going to go in this for each loop and we're going to select that cell so i'm going
to go here and do cell dot offset 0 comma 1 dot select so what this means is that from the cell
that was selected which would be one of these cells over here right we want to go same row
so zero offset for rows and one column right so that would be one if you wanted to go two
column right you would do two if you wanted to go one column left you would do negative one
so that's all i'm going to do just go select that cell to the right and then we'll grab
our image and put it in the cell so let's now try this code and see what happens
so i'm going to go back here i have these two selected so i'll go under macros run this thing
may take a second here so it went to that page it generated this urls and it's going to
place qr codes here in our boxes i'm going to delete this two and
i'm going to add one more thing to this so since i'm going to be using this data here
i want to url encode the data because there are some characters that should not actually appear in
your url and for that reason instead of just doing cell dot value i'm gonna do worksheet function
and here there should be a function url or maybe it's in code
let's see a b c d e
there it is encode url open parenthesis and we still need the cell dot value here so basically
what i'm doing here instead of this cell value i add this encode url function to make sure that
any characters that should be converted to valid characters in the url are converted right there
and that should be all we need as far as our macro if you want to rename this to some other name
you can rename this just make sure no spaces no special characters i could call this add qr
codes something like this so at this point i should be able to just go back to my spreadsheet
select as much as i want here let's say around this much and basically just run my macro
and if i give it enough time as you can see it's going to generate all of those qr codes
right here so that's pretty much all we need to make this happen one last thing i'm gonna do here
because it's possible when you generate bunch of qr codes like this you may want to just delete all
of these and repopulate them because something is not accurate for some reason if you keep it
like this you would have to basically just select all of these images one by one and delete them
so what we can do we can go back to our code editor and i'm gonna add a little bit of
code here so we're gonna do sub remove qr codes now this is really just gonna remove every picture
you have on that worksheet so keep that in mind so what i'll do i'll just grab this part that says
activesheet.pictures copy that and go here and i'm going to do for each pick short for picture
in and i'll paste that activesheet.pictures and we're gonna do next pick to go to our
next picture and what we'll do we'll just do pick dot remove or i think it should be delete
not remove let's actually type that correctly delete yeah it uppercased it that's a good sign
so now if i go back to this see i have some of these qr codes already here i can go under macros
and do remove qr codes hit run and see how it clears all of them for me so i don't have to
go one by one and do this and then obviously you could just go and highlight whichever ones
you want to generate qr codes for go under macros and run your previous macro to add your qr codes
and it should just go through them and basically populate those qr codes for you
and that should do it thanks for watching please subscribe and i'll see you in the next video