Code but let's you know explore more in today's so before going ahead and let me just tell you that this are not complicated code you don't have to be a programmer these are very simple syntax.
That I'm gonna introduce to you and then I'm sure you'll be able to follow along if you have any questions from earlier videos or from this video like I said please do not you know hesitate but just.
Put in a comment and then I'll try to reach out come back to you as soon as possible alright so first thing what we need to do with working with a do is I'm just.
Gonna create a new particular workbook I'm just gonna save this as X alyssum file and I'm gonna call this as a do VBA and then SharePoint just for now okay and then this gonna first go and copy.
Not understand this a do DB connection so this because one you have to work with a do you need to create a reference to the a do library so first thing what you need to do here is you need to go to.
Tools and then you need to go to your reference and then you need to add a reference to Microsoft ActiveX data objects whatever is latest so I'm just gonna show you how.
You can get this all this you know every level references are sorted alphabetically so just press on em and then we'll look for Microsoft you know this one Microsoft ActiveX data objects.
And then I have two point two point eight which is the latest so all those references and then I'll click on ok so if I try to go back here and click on the web now it works because you know it.
Has a reference to that particular system files basically so first thing we're gonna learn today is the thing that they do from SharePoint using a do method ok programming so here first.
Posts Related:
- Painel de controle moderno do Excel Sistema de análise de vendas Tutorial em vÃdeo #1 Office
- Excel 2016 Advanced
- How to Design a Certificate of Achievement in MS Word DIY Tutorial
Thing first this is gonna walk you
Through this a little bit so first part here is basically this do is saying that CNT dim CNT as a DoD V dot connection so I'm just saying that this is gonna be my.Connection you know I'm declaring that CNTs will be a connection and then rst you know will be record set and then my signal will be string where I'll be putting a string later on to connect it.
To our list and then this connection string is coming from I got this string for your friend reference I'm gonna put this in our slide as well I'm just gonna go here so if you go to this connection.
String dot-coms last share point you'll be able to see all the you know connection string that are available you know for you to connect to SharePoint let me see if you want the connection.
String for anything like for any of the database or servers it's available in this part site is really awesome I recommend you to go there and you know find out you.
Know connection string for your axle for your MS Access database for your sequel server sharepoint server and so on if you're working with other you know database and cubes as well you can go.
There and try to find it so I'm just gonna just go here and this is where you can need to scroll down you'll see that there are different type of you know string based on you know for read update.
And delete you can use this and for it only you can use this and so on so I've put in I've already put in the string together for you so you don't have to worry you can use this for retrieving.
The information and even to write by so that's the one I'm using right now read update and delete so if I copy this and then it has a little bit of documentation on what is what in this.
Particular string so this this is just
For a reference I've copied it copied it from there so if you look here like the provider is Microsoft you know all this 12.0 and then you have to retrieve ideas.And then you have the name of the site and then you have the name of the list so if you've been following along with the previous videos I have given instruction on everything on how to get.
The list ID which is basically the ggu ID and then the site name all this so let me go back to our list that we have created which is called fin data TV okay this is our database and then let me go.
Back and get our geo ID here this is the the idea that we generated earlier and then I'm gonna you know update this the name of the site still remains the same someone I'm gonna leave it as it is but.
For you you have to update this as well as this so anything within this curly bracket and then for for now since are the name of the database is fin de da TV I'm just gonna copy this how do you copy.
This okay let me just take this come back to our code and I'll say select all from this table so for now I'm just gonna use this it's gonna say that my sickle is this so basically this SQL.
String right now the comment that we want the past in this connection is we're saying that select everything from this table and then later on we'll write a code to retrieve the data that is.
Returned from this connection okay so first is a declaration and then we are are setting up or a connection and then with this connection we're putting that this will be you know like the drivers.
Or you know the things that will bridge you know Excel with SharePoint and then this is my connection string and then I'm saying using this string just open this connection and then after it is.
Open I'm opening my rst which is basically the table so I'm saying open at the table and then the candy all the parameters that you need to open right now is open this is my SQL that will be.
Select all from this particular table alright so if you just put a comma to any of this you'll see that open and then here is my source and then this is my connection which is coming from here.
And then this is my cursor type and then this is the log type okay so since we'll be just retrieving the data I'm just using forward only and Lock read-only because we don't have to.
Update the data and anything so this part is opening my connection and then this is where I'm opening my table and then after them I open my table I'm saying that if this particular syntax is.
Basically just checking that if there is a data or not so if this table does not have any data or let's say that if I put in a parameter or a filter it might not have the data that you're looking for so.
It's some time it's important to have this particular line basically this line is saying that if this particular cycle or the table that I'm opening right now on this is not having any record then no.
If it has the data then just bring this data just copy from record set to you know to this particular cell which is a 1 cell 1 1 is a 1 row number and column number and then it's copying from this.
Rst that way is this open and then after that is checking this cebú is basically just convert or statement to true or false so basically this part is just checking if the table is open just close.
Them and then just clear clean up the memory and the same thing for the connection so that's all for now trying to explain the code structure let's try to run this and see if this works for us.
So I'm just gonna go to our new data you know table that we have created and then let's run it and see if it will return the data all right so it returned all that they done for us from there it has.
29 so if you go to list and then if you go to thin data table it has 29 records so it synchronized or it retrieves all the information for us from the from the data from ours.
Sharepoint ended on TV from this table alright so before we go ahead to our next topic let's put in one more code let's say that you want to do a retrieval for the.
Item which is where the item is equal to salaries so I'm just gonna get this okay just gonna go back here then I'll say where were item is equal to salaries item okay some saying that retrieve from.
This table and then where the item you know the UM you see that I'm putting these brackets you know the score brackets on the object names so it becomes easier for the code to you know.
Understand it and then I'm putting my filter here it's my salaries so let's try to run it and see if it works so I'm gonna just clear everything off here I'm just gonna go back and run this so it.
Does retrieve all the information where you know my item is equal to salaries okay so that's all for now in this video please feel free to come in and reach out to me you can find my email id as.
Well as you know other references available in the description and then I'll try to get back to you as soon as possible if you have any questions thank you so much for watching see you in the.
