Learn How To Turn Excel Into An Incredible File Conversion Machine

Learn How To Turn Excel Into An Incredible File Conversion Machine

11.Aug.2021

hello this is randy with excel for
freelancers and welcome to the file
conversion manager
in this week's exclusive training i'm
going to show you how you can create
any type of file conversion all from
excel
whether it's a single file or multiple
files in a single
folder and of course that can be from
pdf
to word or from word back to pdf or
any other type of combination it's all
gonna be here
so many possibilities with this training
and that's gonna include
so many types of formats it's gonna be
an incredible training i cannot wait to
share with you
so let's get started
all right thanks so much for joining me
on this incredible unique
training i cannot wait to share it with
you first of all
why why would we want excel to be able
to help us convert
file formats what does excel have to do
with that well excel is a great tool
because it can not only keep track of
the files we converted
we can easily automate things just the
way we want to
you know we can convert all the files in
a single folder or a specific file
or we can automate different conversions
you know and with these tools
it can really help us expand our work
and automate things and save us a
lot of time so excel can be a really
powerful tool
in that factor so i bring you these
trainings each
and every tuesday i just ask a few
things for your help
i would just like if you could subscribe
that would really help us out don't
forget to click on that
notification icon bell that's going to
get you alerted when we bring
new trainings to you if you do like this
application
i've got 200 of my best applications
in a single zip file and that's just for
77
that's less than 40 cents per
application so it's going to be a really
incredible deal you can also get this
for free using the links down below
that is no problem just submit your
email or
facebook messenger and we're going to
send that right over to you all right
let's
get started because i've got a lot to
share with you today so
i've just basically started out with a
very very simple format you know we're
going to get beyond that we don't need
to focus on the formatting in the fields
here in this particular trading
because i really want to focus on the
conversion i want to focus on the
automatization i want to focus on the
automation
and the coding so that's going to go
into that so we can understand it so
what are we trying to do well basically
what i want to do is i want to create
conversions easily what kind of
conversions do i want to
create well i want to create a ton of
them i want to create possibly if we
have an existing pdf
a text file a document a csv an
xls or a jpg or
powerpoint i want to take that and i
want to convert
that pdf possibly into this
many different conversions or a text
document into this many type of folders
you know
so i really want to be able to expand on
this conversion right so where did i get
this table from if i have a csv
i want to maybe turn it into a jpeg or a
png or if i have a current pdf maybe i
want to
convert it into an excel file or maybe i
want to turn it into
a gif file or something like that so how
do we do that well there's many ways we
can do that actually i don't see excel
on this list but i may add it in the
future
so how do we do that well basically we
can use an
engine a conversion engine excel doesn't
have to do the hard work because there
are tools that do that so excel can
incorporate in that if we want to turn a
powerpoint into a png or an html
we can do that with this tool so we've
got this table here that's going to help
us determine that and we're going to use
another engine what is that engine
called we're going to use something
called
cloud convert cloud converter is a great
tool it's going to allow us to do that
so for example
if we want to convert inside this if we
want to conserve let's say we have
an image or something and we want to
convert
let's take a look at image we may want
to have a convert
tiff too we can convert it to different
formats or let's say we have something a
little bit more common why don't we do
a presentation if we have a powerpoint
presentation here
we may want to convert that to different
types in odp or something like that
also with pdfs they have a lot of them
even if you have a spreadsheet let's get
back there
even if you have a spreadsheet we can do
that inside
let's take a look at xls maybe you want
to convert that to a number we can
convert that to an image we convert that
to a different spreadsheet or even a
different document we can convert it to
an
xls to pdf so many different
combinations one of the most common
that have been requested is how do we
convert
pdf to word so we can do it like
document here
if we look down in the document we see
we have here pdf
so what are the conversions here well we
can convert that to
doc docx html ebook so there's tons of
combination
i've taken some of the most common ones
these common ones
and i've put them into this table here
so we can you can easily expand on this
i just used a few sample ones together
but basically that's where it generated
from it generated from this table here
into cloud convert
now this is a free account that lets you
use it for free
at least there's 25 conversions per day
that's pretty good you can expand on of
course you want to pay for that
but that'll allow you up to 1 800 per
month pretty cool if you want to do that
and so what we're going to do is we're
going to take this api and we're going
to use that within excel and we're going
to combine it with integro mat integram
we're going to create this really cool
function
we're going to pull it from a web hook
we're going to get that information from
excel we're going to send it into
dropbox we're going to create an
upload then what we're going to do is
we're going to send it over to the cloud
convert and we're going to get the
information back which is that converted
file
we're going to bring that back into
excel we're going to download it so
we're going to do
all of that in this training i'm going
to show you how to do that
now what we also want to do in this is
we we may want to convert
a single file or we may want to convert
all files in a single folder so we need
to browse for that folder
we want to convert it so we want to if
we have a pdf we may want to convert it
to any one of these
if we have let's say a document we may
want to convert it to any one of these
so we need a
dynamic drop down list based on this so
what do i mean by that
so for example if they've chosen a pdf i
want all these as
options if they've chosen a txt i want
all of these as the available all
conversion options
so for example here let's go to the top
pdf again we've got all these options
that we saw
or perhaps if we want a txt then we have
a little bit less options but here
so notice these options here are the
same ones here in this txt
and to do that we're going to use a
dynamic drop down list now in the past
we've used
vba but we can easily use just some
formulas on that we're going to call
that the destination
and it's right here this destination is
the dynamic destination
notice when i change this here to
the top one pdf notice that those
destination lists is going to change
right so how do we do that well we do
that with
just a few simple formulas the first
thing what i want to do is i want to
link
the destination this main destination
what are we starting out with we're
starting out with pdf so it's this so if
i
select this we have a named range that
named range is called
source type source type it's based on
this range right here
so it is that named range that i'm going
to use in this data validation if we
click on the data validation
here we see it's called source type okay
source type so
basically then what i want to do is
create another named range if we take a
look at this one
data validation it's going to be called
destination type now that destination
type is
dynamic and it's based on the source
type here
so we're going to use that here so if we
take a look inside
the named ranges under the formula name
manager
and we see the destination type it is
based on an
offset so it's dynamic and this time
what we're going to do is we're going to
use
countif count if anything that is not
blank
why do i want to do that or count
anything that's not 0 because
it's possible that if we select
something here like text
it doesn't have those three options so
what do i mean by that what i mean by
that is
this let's go to text now take a look at
this now i only want that drop down list
to contain starting here and going down
to here
so i don't want any blanks in that
drop-down list down here so we want to
use the
dynamic named range to achieve that and
it's going to be based on this but
notice these are formulas so we can't
use
blank because these are formulas but we
can use the zero so what i mean is i
don't want to count the zeros i only
want to count those
non zeros which is here so that's the
named
range so let's take a look inside that
name manager one more time and take a
look at the destination so basically
what we're doing is we're starting on q5
we're going to we don't need any rows
down or any columns now we're going to
keep it consistent
we're going to count if i'm going to
count all of the cells
admin q5 through q15 that are
not equal 0. so not does not equal and
zero
anything that does not equal zero and i
just want that single column
so what that's going to do is i'm going
to count only those cells that are
non-zeros in this case those only cells
are
starting on q5 all the way here to q12
it's only going to count those
so that way our dynamic named range is
going to be
that is based on this value here so
let's take a look at some of the
formulas here
so we know that this is linked to g3
inside our conversion management
link directly to here but now how do i
get this
dynamic name this dynamic drop-down list
well that's going to be based on the
indirect so i'm going to use the r1c1
let's just go over this formula
equals indirect right when we use
indirect we have the reference text what
is the reference text
like a1 and then what it's going to be
is like a1 so if we put in just the
reference
text here right then what we want to do
is we want to know
is it going to be r1 c1 style
which is false or a1 style in this case
what i want is i want
dynamic rows and i want dynamic columns
going to be based on that so
what i want to make sure is going to be
r1c1 so what we need to do is we need to
determine
what the row is and what the column is
in this style indirect so let's go back
into the formula
and see just how we did that so we want
remember dynamic rows
first thing what i want to do is i want
to find the row what are we going to
start
on the rows the row is going to be
basically whatever the current row is
here so we can use the current row in
other words
notice that i've started this on row
five also our list starts on row five so
if i use the same
rows our row i want to return whatever
is in
row and what about the column right we
know the current row
so it's either going to return this this
this this then all we need to do is
determine the column so how are we going
to determine the column
well the column is going to be based on
this right i want to find txt i want to
know what column this is
in in this case it's column four or if
we do a match if we run a match it's
going to be the second
right i'm running a match based on the
source type if i'm using match
and i'm going to look for txt it's going
to return 2 because the second one
but i don't really want two i want four
because i want the fourth column so how
do we do that well we
add two and that's just what we did here
so we're going to match
q4 remember that's absolute we're going
to base it on the source type
this right here will return 2. if i add
another 2 it's going to return 4.
4 is the column column d is what i want
so we know the column here
column 4. we know the row which is
whatever row it's on which is in this
case
5 or whatever then we want to make sure
that a1 is going to be
r1 c1 style r1 so i'm putting the
r for the row the c for the column
that's how we're notating that
that's going to give us exactly the
indirect so then all i need to do is
just copy that formula
all the way down notice we're using row
here that way
each row is going to change so the only
thing that changes is the row here
everything else stays the same the
column stays the same we're matching the
exact same q4
every single time that won't change so
that's how we can drop that down and
then we can bring it down it's going to
automatically
be relative based on whatever is in txt
so forget if i change that
to the last one powerpoint here we're
going to see that that automatically
changes here and that's going to help us
because
we need to know what options are
available what options when we
if i want to convert a powerpoint
presentation i want to know what i can
convert it to
can i convert it to this so i've placed
all these options so we want to make
sure that they can't convert it to
something you know like excel or
something that might be more difficult
we want to make sure that that drop-down
list is dynamic based on whatever
they've done here
okay also what i want to do is i want to
add a little bit of code because i want
to make sure that when they change this
this gets cleared out that way we insure
it and also
i want to do this i want to if i'm going
to add a single file i want a
button here that says browse for file so
i want to source that file
if we want all files i need to browse
for the folder
notice i've got a dynamic already a
formula in here
dynamic text if e3 equals single file
then
i want to put the words the label source
file
whereas if anything else source folder
so notice how this
changes between single file source file
we're going to browse for a file
and what i want if it's a file what i'm
going to do is i'm going to have a
button here that says browse
file and we're going to select a
specific file however if we
want to convert all of the csv
documents in a single folder to a single
format
then i want to browse for that folder
and we're going to loop through every
single document that's csv or whatever
we've chosen here
and we're going to convert every single
one to whatever formats here we're going
to give it a destination folder
once we convert it where do we want to
place it okay
also what do we want to do when i once
i've converted it what should i do with
the original should i delete the
original should i move the original
or should i copy the original or should
i just do nothing
right so what that's what i'm going to
do i want to set then when i once i
complete all that i want to know what
happened i want i want the status
was it converted from type 2 type i want
the file name i want to know when
and what date and what time it was
converted on and i want the download
link or message here i want to know what
when it's been download link or
perhaps there's a message so that's
going to provide us all that so we're
going to have
all that information here really really
a powerful
just a very very small screen but very
very powerful because you can use this
to convert almost
any single file and so really great i'm
going to share that with you now let's
get these buttons working i want i want
some
browse file buttons so let's do that
let's insert some buttons here i'm going
to insert some shapes
we'll take care of that it's relatively
easy on the brows so i'm going to have
two buttons up here one's going to be
browse file
one's going to be browse button so we're
just going to add that in here
we're going to call this this is going
to be for the browse file and browse
folder browse
file and then we'll have another one for
browse folder so then i'm going to
let's uh right justify that make sure
everything is just the way we want it
browse for the file and i'm going to
duplicate that and we're going to call
this browse for folder folder
and then we'll make that a little bit
big okay now browse for folder these are
going to be on top of each other
basically because it's going to either
be browse
for a file or browse for a folder
however we're also going to need to
browse i want to know where we're going
to put that where's the destination so
that's going to be a folder
automatically so i'm going to do is i'm
gonna duplicate that i'm gonna place
that down here
then what i wanna do is i possibly want
to move
the file or copy the file to another
folder so i need another browse button
right here
so i'm gonna duplicate that with ctrl d
and i'm gonna bring that down
here okay so then i'm going to line
everything up here on the left or
in the center and then left here bring
those over so that's what i want now we
just need to add some icons here
so we have browse folder we have browse
file and then that's all we need to do
so i'm going to
insert some pictures here and i've got
some pictures saved up so we've got a
file search a file and then i'll do a
refresh i also want to convert button so
i'm going to
insert all that that's good for now and
then i'm going to set those to the
height 0.2 that's going to be fine
so we're going to use let's take a look
at this i'm going to use this browse
folder i'm going to use this three times
so i'm going to duplicate that
because i want that for each of the
folder and then we're going to line
those up here
and of course we have the browse file
too that we need to add then i'm going
to highlight those
making sure that those are all lined up
here which they look good
and then also we're going to use this
for the browse file now the browse file
is right here
if we want to display that all we need
is just click the selection pane
and we look down here we just keep it i
think it was the first one we created
here inside the curve let's take a look
at this one right
here rounded rectangle i think it's this
one here
and i'm going to bring that to the front
here so i'm going to bring that to the
front there it is there's the browse
file so i'm going to group that here
that's what i want
bringing also this picture to the front
holding down the control
lining those up and grouping that now
we're going to give that a specific name
i'm going to call that the browse
source file button so browse
source file button and i'm going to copy
that because i'll make it easier and
then
i'm going to hit enter okay so that's
good now i'm just going to move that to
the back we've already set that now what
i want is i want the browse folder
button so
then again holding down the control here
lining those up
grouping them together and then we're
going to paste that in this is not going
to be the file but it's going to be
called the folder so we're going to
change that to folder
and hit enter okay so we might as well
name these two although it's not
absolutely important because we're not
going to be doing this this one will be
more important so again
i'm just going to group these and we're
going to call this browse destination
folder that's going to be a bit easier
browse
we'll just call this the destination
that way everything's
destination and then folder and then
this last one here is going to
call this the move to right so that's
going to be important because we need to
know whether we're going to move to it
and we're just going to call this browse
move to button that's fine
move move or copy move just say move or
copy because we don't know which ones
can move
copy to button okay kind of a long name
but that's good this one here is going
to be the one that actually activates
it's going to be the one that
we turn on so i'm going to insert one
more button here i just need to copy
this
control c can control v we're not using
control d
because that's going to duplicate it
inside the group and then what i'm using
i'm going to change the look of this one
because this one's going to be different
i want this one
is going to allow us to actually convert
it's going to call it convert because we
don't know if we're converting
files or files convert i'll call it file
and put the s because we don't know if
we're going to be converting one or more
so
that can be fine convert files and then
we're going to bring this down here this
one can be a little bit bigger because
it's going to be the most important one
so that's going to one that's where all
the action it's going to single macro
that's going to be able to take care of
all that for us
and then we'll bring the icon i'm going
to bring that on top here
and then we'll bring it down here so
that's going to be the one that converts
everything and that'll run our macro
okay
so we've got that there i like that way
that looks i'm just going to group that
and then we'll give it a name as well
and also we need to make sure we're
going to size but move but not size
convert
files button okay so now what we're
going to use we're going to use the
selection tool i want to make sure that
if i change the columns or something i
want to make sure that these
buttons do not size but they do move
with the cells so let's do that under
the properties
and then we'll go into here and then
under the properties
move but don't size only if you regroup
them you're going to need to do that as
well
okay saving our work so far we've got
our buttons everything's looking good
here
we've got a good head start we'll undo
the selection now what i want to do is i
just want to do some macros i want
if i select all files i want browse
folder to show up
because it's all files within a specific
folder if it's a single file i want the
browse
file to show up so let's write some code
to do that inside the developers in vba
alt f11 is going to get you there and
what we're going to do is we're going to
go into
here right the conversion manager and
we're going to write some worksheet
change events because when we actually
make a
change there that's when i want the
thing to happen that's when i want that
button to change over
it's going to be based on e3 when i make
a change to e3 depending upon the type
of change
that's when i want the buttons to show
or hide so we're going to write some
code
if not intersect based on that e3 so
we're going to change this to e3 here
and then what do we want to happen then
right first of all we want to make sure
we write the then in
then we want something to happen it's
going to be based on what they've
selected so
if the target dot value is going to be
equal to
all files then what do i want to do
else something else so if i if it's
all files then i want to show this
browser i want to show this and i want
to hide the other one
so let's write that in right now i just
copied that so
shapes what shape is that browse folder
dot visible
equals mso true where we want to show
that browse folder
and we also want to hide the browse file
so shapes
in this case we're going to i'm going to
change that in a minute dot visible
equals mso
false okay but that's not the folder
this is the file right
file that's the one we called it file
button okay and basically it's going to
be just the
opposite if we don't okay so if we've
chosen so in this case this is going to
be false
and this is going to be c true so i like
that saving our work so far
and we'll take a look at that that's
going to be on change so if they if i
double click that it's going to say
browse
file if we click all files it's going to
be browse folder that's what i want
perfect
okay we're going to do something here i
only want also the browse folder i only
want this
mover this i'm going to copy this in the
case of move to
or copy to then i want to make sure this
shows up otherwise i don't want to show
up at all
so that's going to be also based on a
change so let's write that up now let's
put some notes on that let's put
some memo here going to be basically
saying
on conversion file or folder change
and this one's going to be based on move
to or let's
just call it we'll just call it source
file after conversion it's called
on after conversion
type change and this one's going to be
based on
g9 right g9 let's take a look at that g9
is what i want on g9 then we want
something to happen so we're going to
change this to g9 we'll put in the word
then
we need that then again same thing
if the target value this time is going
to be 2. if
target dot value equals
what's it going to equal copy 2 file
either this one
copy 2 or move to so we need both of
those things
escape out of there go back into the
code paste that in there or
or what or it's going to be moved to
move to so we can do
or copy this paste this in there or
move file to let's just take a look to
make sure we have it
right move in this case it's going to be
move file to so
we're going to change this to move in
that case we want to make sure that
we're going to
show that otherwise we're going to move
file to
then what do we want to do then again
we're going to
this button here this one here we're
going to be show or hide that based on
the selection we only want to show it
if it's on move to or copy to so best
one
shapes paste dot visible
equals true mso true
or false else we put the else in here
shapes dot visible
equals i'm so false okay so only if that
button's only going to show if it's this
or this otherwise no okay so let's go
ahead and save our work always save it
and then we're going to test it out so
that button is only going to show delete
file it should be hidden
also should be hidden on do nothing and
we only want to show on move file to
or copy file to okay perfect that's the
way we like it
good all right so now what we can do is
we can start to get into some of these
conversions right so
let's add some macros going to be for
the browse folder browse file and we'll
start out with the browse file so the
first thing i want to do is write a
macro for this
when i select that i want to put that
file directly inside
i3 so let's write a macro to do just
that all right very good actually one
more thing i wanted to do
on this remember i mentioned that when i
make a change to here i really want to
clear
this out so let's do that right now on
any change of g3 i want to clear out
g6 because that's important while we're
on the sheet so
we'll clear this out and we'll put it up
here so
okay that's pretty easy if not
intersection g3
we'll put then what do we want to do
range
g let's take a look at that six clear
the contents out just on any change that
that's important because we want to make
sure that they don't get the wrong file
tape
six dot clear contents okay
put in the quotation here all right good
i like that though
on on change of it's going to be
basically a source type
change of source
file type clear destination we want to
clear that clear
destination type okay so that looks good
so now basically anytime they change
here
then i want this cleared out so they can
select it just we don't want to
write the wrong thing okay good so now
we can get into the browse macros now
we're pretty much done
on this sheet so what we're going to do
is we're going to go into the file
folder and the first thing i want to do
is
do browse for file now i've already
added some things to help us
source file as source folders file
dialog destination folder
so that will help us out to make things
a little bit quicker
so as we move through the first thing we
want to do is the browse for the file so
that's going to be this macro right here
so
first of all we're going to set the file
type is going to be
equal to i want to make sure that we're
going to be the right file the file type
is going to be equal to based on g3 so
it's going to be the conversion i've
already named these conversion manager
dot range g3 so that i want to know that
because
if we're going to browse for a file i
want to make sure that we're only
browsing for xls files right our we're
only browsing for csv so it's going to
be based on this
okay so also also what we can do is we
can add the lowercase to this if we want
to as well
okay so also if the file type is doesn't
lamp you i want to make sure that they
put in a file type so
if file type
does not equal you'll just put equals
empty and better
then let the user know message box
please
please select a
file type or let's put in from file type
a from file type exit sub nothing we can
do if we don't have that
okay assuming that we do have the file
type then we can do so we can
set the source file is going to be equal
to the application
dot file dialog that's the one right
here we're looking for a file picker
that's the first option right here file
picker right here
and so once we have that we can focus on
that so with the source the width now
we're going to
source file what do we want to do the
first thing what i want to do is set a
title to that

 

#googleurlshortenerapp #linkreducer#google#googlelink# shortener online#google short url link#google form link short#short url link google#google short link generator#google shortener online#google url shortener online free

We are social