Dear Randy, I'am already working with Excel for years (and I love it) and I have already made some applications. I also have followed some special training/courses (not always cheap) but I have to be honest these videos are the best I haven seen in a (very) long time. So just a special thanks and lots of respect for the tremendous effort you put in these videos, keep up the good work.
Thank you so much Bart, I really appreciate your comments and they inspire me to push the envelope. Please make sure you also check out my newest 'Share & Sync' video which is much quicker than this older one here: ruclips.net/video/aKV5seZmiBs/видео.html
I do a fair amount of VBA and consulting in Excel, but will admit you guys are taking this to a whole new level. Thanks for sharing. Awesome set of videos. Worth every minute of them!
Thank you so much Yves, I really appreciate that. Make sure you check out a newer version of the 'Share & Sync' video right here: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you so very much, I really appreciate that I even have a better one that works faster and more secure here: To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.html
Hello wondering if you can assist. I just have an Excel worksheet that contains a table and each column has a filter. How do I make this a shared workbook? We do have Microsoft One Drive at my office. Thank you for the help.
Hi Diem, thanks so much. You can use any shared drive such as Dropbox or Google Drive or any other one. I also have an updated training on this here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
This is a great turorial.it really helped me.btw i tried this on the network drive however it wont open or copy a file from the source drive.it seems its not connecting or opening the connections.
HI and thanks so much for your comment Arnold. I am glad you like the training. Make sure you have user access rights and access to the drive and check connections. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Another great great idea, thanks Randy.May I ask in this application, are any way we can lock the customer data base?as it is sharing, so if anyone go to sheet CustDb and destroy data, which could destroy entire application data....
Hi Xuan, thanks so much. You bring up some great points and i have a few solutions 1. This was my first video on workbook sharing. I have a new video which updates only the changed cell and not the entire table here: ruclips.net/video/aKV5seZmiBs/видео.html 2. The actual data table, in my opinion should never be viewed or access by any user. In a well-developed application there are 3 total screens for a single database (1. A form to enter, load, view and modify data, 2. A visible table to allow the users to sort and filter data, but this is not the actual data. 3. The database table that is completely hidden from all users. It is this data that feeds both the form and the filtered and sorted list) When we build strong applications that have the above components it is very hard for users to break or damage the data. I am teaching this entire process in the Mentorship program in which we will build an entire accounting application that is shareable and able to be synced around the world. Thanks so much for the great question.
Hello, your videos are so helpful, thank you! What is the difference between this video and your other video titled 'How to share and sync your macro enabled workbook, from scratch, with anybody in the world'?
Hi Adam thanks for your comment. In this video the entire database is moved back and forth while in the updated Share & Sync, each cell by cell is synced here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
This is AMAZING! I want to add slicers to the front end of the Contacts table. How can I do that without the table messing up when I update or refresh? I have tried all possible methods I can think of, but cannot seem to make it work. Thank you for all that you do! You are a magician!
Hi an thanks so much. Anytime you want to use filters, sort or slicers, on something like this. Don't use your original data. That date should be hidden and away from any users. Use another sheet that pulls the original data. You can then add slicers, filters, sorting or anything else as the original data will remain intact. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Once again, you never fail me! This is awesome! However, i have a problem when i want to add rows to the contact info. I have 7 data instead of 6 as shown in the video. Can you do a quick video on how we can add the rows to it? Thanks in advance!
Hi and thanks I have an updated video which may help you here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Hello, thanks a lot for your nice tutorial, it seems fantastic. The only question is: Are we sure, that if 2 users click at the same time on "Save" it will not make an unexpected behavior ? Data that would have the same time (date, hour and second) of last update for both back end and front end for example ?
And maybe an answer to my question, but to which extend is this version better as you said in a comment below ? ruclips.net/video/aKV5seZmiBs/видео.html
HI and thanks for your question. Please watch my updated Share & Sync which is more optimized here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Fantastic video - this is exactly the solution that I'm looking for. Question: How many users do you think this method of sharing can realistically support?
Hi Christopher, there is really an unlimited # of users at the same time. Since each user uses their own workbook, there is really no issue with that. Only the data is shared. Before creating your own, please check out my newest Share & Sync method which is much quicker than the one you commented on: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for this video! Question - Do you know how I can share a macro with someone? I don't need to have a share file, but instead let them run their own file using the macro I built (it is under Personal.XLS). Let me know if you have a video on this or if I am missing it in this video. Thank you!
Hi and thanks for your comment. It's better to share a limited xlsm file with only the macros you want to run. However it depends on the type of automation you are looking for. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you so much for the video. My question is that I have an Excel File (i.e., My Excel Version is 2013) with Marcos. How do I upload the Excel File on Google Drive and still keep all of my Marcos and share the file to users inside and outside of my organization
Hi and thanks for your comment. Excel and Google are very different so you could not combine them when they contain macros. If you would like to share and sync your macro-enabled workbook with anyone in the world, you can follow my training here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Well done. I did something like this back in the late 90's (excel 97/2000) for a budget program that worked very well. You have taken it next level with your code and especially by sharing it with others. When I did this we had the front end as a single file that was read only so that any changes to the code were automatically accessed by all. One file with all the code that was managed and tested before rollout. Read only was unsettling to our users so we intercepted the "read only" message with a "company name" message. Not sure if you can still do that with newer versions but back then it was our solution.
Hi Gil thanks very much for your comment. That is a very novel solution. I am glad you liked this Sharing workbooks video. I will build on this Sharing Workbooks idea with our current Employee Manager series in which we will be sharing several databases in a single application (Employees, Events, Payroll, Timeclock, etc) so please watch this series for amazing tricks and brand new features never thought of in Excel.
@@ExcelForFreelancers I have subscribed as I do find this interesting. Glad to see others pushing excel to the limit. I'm not programming VBA anymore as I retired awhile back. When we wrote our application it was to replace a lotus123 spreadsheet that was just a temporary thing till they could find a better solution. The company ran ours for over 10 years so it turned out well for them. It ran on 6 citrix servers and a NAS to hold the app and data. It was accessed by users across North America over the internet. Pushed Citrix and pushed Excel and I think we found every bug in the software. Even used a few to get things done.
Hi Randy. I have been watching your videos for the past few months. On this video, is there a way to alphabetize the list. Thanks for great training videos.
HI Jacob, thanks so much. When you have a list in a shared workbook, its best to have 3 different levels of sheets (1. A sheet form in which users can add, update and delete records, 2) a List view in which users can sort and filter data 3) the Actual data which users do not have access to. This will allow you to sort and filter data without disrupting the actual data. I have an updated share and sync video here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Hi Randy. Next Id comes from cell B8. It is created by adding 1 to the last record in the local Db. This means for example two different users can use the same Id at the same time? Shouldn't it be necessary to check if the common database has changed and if local next Id used already, before create new Id?
Hi and thanks. Yes no doubt. When adding new records, before even the next ID is calculated, we always want to check for updates from other users before setting a new ID. I share that with you in this updated video To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Thanks for a very nice application. I have a slight problem. After adopting the application to my needs I did all testing and everthing worked nice.I was able to add, update and remove data. However after hiding columns, rows and 2 sheets I made some additional testing and then the database was deleted but newer recreated! Have any had this problem? Any idees of what can cause it.
Hi Kristian, thanks so much for your comment. I do understand the issue. I have an updated Share & Sync video that does not replace the database but changes on a cell-by-cell basis here: ruclips.net/video/aKV5seZmiBs/видео.html
Your work just impressed me alot,so i tried to replicate the same thing with small modifications but i didn't get the same way the result while im running marco of save and update contact im unable establish relationship with dashboard and database it was always asking the database path.im happy if you help me.
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
@@ExcelForFreelancers i can't wait to try it 🙂 i hope i can succeed. I just did a 3 hour presentation today for employees of a document i made that use a VBA program that add/edit/delete from a sheet in the same document. It is uploaded to Microsoft Teams. And when i showed it... Errors. It asked something of if I want to create a copy or some ActiveX problem, i don't remember what it said. I was so embarrassed. I had to make them look at a document that I couldn't even promise if I could get to work. I had to test it live because I don't have anyone that can help me test multiple users. For me it worked opening in Teams as long as I opened it in desktop app.
This is an amazing file. Created one with your help. The only issue what i encountered is that if, for example, I delete rows from dbfile, even if it is newer, will not overwrite the existing data in the main contact file. It will overwrite only the number of rows in the recordset - if there are less, then less rows will be updated. Is there anything from my side doing wrong? Or an easy fox for this? Thank you so much!
HI and thanks Tudor. Much appreciated. For Sharing & Sync, rows should never ben deleted. You can clear the contents of a row except for the first column (usually ID/Key) When deleting you will want to clear the contents on a cell by cell basis. I show you how to do that in this updated video: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Amazing!! This is the best Excel vba channel on youtube! I have a question regarding syncfromDDBB. What happens if for any reason the recordset is empty? For instance maybe if the file is killed by other user. How can you check if the recordset is empty? If I´m not wrong, If the recordset is empty the syncfromDDBB will blank the local DDBB. Thanks and congratulations!!
Hi Xavier, you are right. We can never have a blank record. What we can do is void or disable a row from appearing in Searches. For Example, lets say we want to delete an invoice. We Keep the invoice #, but mark it void and remove all of the other data within the row, cell by cell. This is why it's so important to separate the front end and the back end and have VBA control the process of adding, updating and removing records. I do have an updated version of this which is faster and records, cell-by-cell modifications here: ruclips.net/video/aKV5seZmiBs/видео.html (Also this next Tuesday I will be showing you how users can reset their own passwords securely without admin approval via their email) Also I will be building an entire Accounting Application which will have shared & sync capabilities in our Mentorship Program starting in just a few weeks so keep an eye out for that.
Hi Randy, very impressive macros. I have a question: with this shared workbook if multiple users work with the database simultaneously, will it work? For example, USER 1 and USER 2 both loaded the database at 12:00 and started to work with it, they both added and deleted some records, then at 12:30 and 12:35, USER 1 and USER 2 both saved their changes. Will User 1's saved data got overwritten by User 2?
Hi Warren, thanks so much. Yes this will work with multiple users, simultaneously making changes, since each user is using their own workbook. Make sure you follow the updated training on Sharing & Sync here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers Thanks for your reply Randy. Which way do you think is better? The whole page update or the individual cell update in the video you shared above? My understanding is the individual cell update is quicker but there is no "centralized" database that could be used to set up a new user. Is that correct?
Hi sir randy thank you for this tutorial. I have used this template and have recrated syncing for 2 workbooks. Just wondering how can you sync the workbooks if you share one file via onedrive to other computers? Will the other computer create a different shared folder? If yes? How can the original file check the folder from the other computer and vise versa? Thanks sir and more power
Hi and thanks so much. Each user gets their own workbook and only data is shared via any shared folder. You can watch this updated Share & Sync video here: ruclips.net/video/aKV5seZmiBs/видео.html
Hey Randy, Great video! i manage to get it to work for my excel but after sync, the data that i get was converted to text. Do you know how to solve this? i'm using excel 365.
Hi Mohamad, thanks for your comment. You can make sure the cells are formatted properly and you can also use the Format function in VBA to format certain columns however you like. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi Randy! Thanks for sharing all of your knowledge on this! I just have one question. When there's 2 files generating contacts and the next situation happens. Person 1 - Has his own file open (with the next ID being 10) and doing some other work. Person 2 - Opens his own file and generate a new contact. This new contact is the contact ID 10 on the File Database. And closes his own file. This means that the next "new contact ID" is now 11 Person 1 - Remembers he has the file open and generates a new contact. Since the code reads the Next Contact ID in his own file (Contact ID 10), it rewrites Person 2 contact he just created. Should I create a line of code that if my LastLocalChange < DateLastModified it must first pull the information before assigning an ID that's already being used on the Databaste? Thanks!
Great question, firstly make sure you have seen this updated Sharing & Sync Video: ruclips.net/video/aKV5seZmiBs/видео.html Secondly, anytime a new record is generated (new row with new ID), a check must be run to make sure that there are no other changes by other users. If so, then those updates are then brought in, and the sheet must be calculated, therefore updating the next User ID within the Max formula. This is only really important for new records which need new ideas on new rows. Basically before we push out new records we must first pull in any changes by other users. I hope this helps and thanks so much.
HI , thanks for the information. There is no doubt we can help you with that. While I am unable to take on any projects I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com Thanks so much.
Mr.Randy thanks for your video about excel. They are awesome. But I has a question: maybe you can help me. So my question is: how I can create my own view with 12 columns ( like on video) from big database wich is include 50 collumns info. This 12 collumns are separrete in database, I don't know how i can combine those 12 columns wich is not spep by step in database.If you understand my pain, please, help).Thank you in advance
Hi and thanks. You can just expand the cell references within the code for as many cells as you want. This video will also hep you share and sync your macro-enabled workbook ruclips.net/video/aKV5seZmiBs/видео.html
Thank you again for the great video! I am currently working on implementing the solution in my company but I am encountering an issue. Changes to my table are made via an input form which makes it possible that several cells change simultaneously. It seems the code can't handle this issue as the file always crashes when more than onr cell changes at the same time... Do you have a solution for that? I was thinking about updating the entire row instead of the individual cell but I don't know how to implement
Hi and thanks so much for your comment Merlin. I have an updated solution that should handle that issue here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Hello Mr.Randy, do you plan to share or improve topic about sharing WorkBook with separate database? For example: as minimum 3 zones like your zone 'contact info', but with own design of fields and using one database for it? How it synchronize? How to generate datatable view and headname of columns must changed too based on each of zones?
In the Accounting application I an designing in my Mentorship program I have several databases which will be shared. This application has specific Front and Back ends which will be able to be synced and shared using a shared folder for any users within the world. You can learn more information on this Mentorship Program here: bit.ly/MentorProgram_YTComment
Just another of many amazing videos you've made, and I've had the pleasure of watching quite a few. I'm attempting to make a shared workbook while adding some features of some of your other projects. I'm hoping to add your login security feature (the video that allowed you to toggle through various users' access privileges and was hoping you could tell me if there's any complications I should expect from adding the two together. Also, I'm hoping to change your columns, maybe add a few, and likely will add some filters to allow me to sort them. Are there any risks to do that and what code will I have to modify to make all those changes possible. Thanks for sharing your amazing videos; I've learned a lot. With these changes I should be able to make an application that will be extremely useful. Thanks again for all of your professionalism and hard work. v/r Mike Frey
Hi and thanks so much. You are doing well and there are no risks and I have another video that can help you with that To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/f_1HsR9305I/видео.html I also cover proper workbook protection in this video: ruclips.net/video/fW2yf47bAQ8/видео.html I hope this helps and thanks so much.
Randy, Thank YOU very much. Your channel is by far one of the best. Subscribed! I have also checked the Share and Sync video. Very Powerful and seems like take cares some of the issues this one has. Hopefully you can make a video on how to sync excel with SharePoint List. That should help to take care the multiuser related issues.
Hi and thanks so much. I am really glad you like the training. I do have an updated training on sharing and syncing your macro enabled workbook here: ruclips.net/video/aKV5seZmiBs/видео.html I have not tested this with SharePoint however you can give it a try. Thank you for your Likes, Shares & Comments. It really helps.
Hi, I got question. I am not sure if you mentioned it in video, but how is this solution protected against adding new contact by two or more users in the same time? Users can get the same last row right? Thanks for your answer.
Hi thanks the application will check for conflicts such as this and add the new contact to the next available line. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thank you for sharing your knowledge! One doubt... When needing to know which is the last row of your data, you usually write "Range('X99999')", right?! Isn't better using cells(rows.count).end(xlUP).row?
HI thanks Paulo, there are many, many ways to determine the last row of data, so of course you can use whichever one works best for you. I also have an updated Sharing & Sync video training here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Hi there! Hope you read this! I'm new here. I managed put together a data entry workbook mostly similar to this thanks to you. But it'll be very helpful for me if you can show a way to put the sync to/from database on a loop. Since certain shared drives tend to be slow due to various reasons, the "Kill object" and subsequent creation of new backup can take a few extra seconds. This can lead to "objfile = nothing" for any other user during that time frame. Is there any way around this obstacle?
HI and thanks for your comment. I found an even better way for share and sync and I put it all in a training video here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Hi Rick, I have not tried this in SharePoint. Any folder that can be shared among users around the world would work fine as long as you can assign users access to those folders. However the advantage is you don't need to use SharePoint. You can use any shared folder. In fact I have an updated training on an even simpler method. You can see that video here: ruclips.net/video/aKV5seZmiBs/видео.html
@@ExcelForFreelancers Oh ok thanks but we are restricted at work to having all our files in SharePoint so even though your app is great I wont be able to use it or its methods from within SharePoint.
What a great video well done! I am planning to make an excel based database referring on this concept. It will be a log-in/log-out of borrowed equipment in different workstations. There will be 3 excel files namely Workstation1, Workstation2 and Main Database. The idea is to record the time of the equipment being borrowed and returned. But borrowed equipment in Workstation1 can be returned to Workstation2 and vice versa.
Hi, thanks for your comment. That should work very well as long as you have a central database to lookup the equipment. Also make sure each equipment has a unique ID so you can refer to it by this ID. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Thanks for the answer. Will it still be efficient if the lines/rows will reach 300k lines and files are stored in a local shared drive? Or how can it be done through google drive/Dropbox?
Hello Mr. Randy. Your videos so brilliant. Thank you for that. Nevertheless I have an issue with file: if I clear data from file CustData, I get error into cell B8 = #link!. After when I want to put new record, filled all cells and pressed 'Save button' i get #link! Into d13 instead number 1. How it fixed? Thanks
Hi it would be difficult to understand your issue unless we see your workbook screenshot and code, Can you please upload it in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 25,000 members who can help you. Thanks so much
Hello Randy, quick question the main dashboard has to be stored in computer of multiple users , or it is possible to have it on sharepoint a people can open it from one folder in sharepoint in the same time and store the custdata in another folder in sharepoint? Thank you
Hello Tomasko, a Dashboard should simply be a reporting tool in which data is brought into Excel. In my opinion everyone should have their own, duplicate Excel copy, and simply run their own dashboard reports, based on the same data. Now if that data changes, then the only the data is synced around machines. In this video I show you how to sync just the data: ruclips.net/video/3E6ExWYscHQ/видео.html i hope that helps
Hi and thanks so much. Yes you can use your LAN connection as long as all of the users have direct access to it and their own unique link to the folder. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you so very much, I really appreciate that. I have an updated video on Sharing & Sync here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Thank you for another great tutorial - 5 star from me. Thus, just an extra features in this workbook since it has the ability to be used by multi-user, is it possible to have the user to view & edit only on their own data? Only user with ALL-ACCESS can view ALL data - like your previous tutorial on User Level Security
Hi and thanks so much. Yes it is certainly possible using a filter on Sheet Activate which will only filter based on the logged in user. I have an updated Share & Sync application and training here: ruclips.net/video/aKV5seZmiBs/видео.html
hi sir just a query... if you use conditional formatting , shapes and tones of differnt formatting options then it makes a file heavy... please advice me to know how you will manage to control the size and use the file without any lag.
Hi it really depends on your file. You can remove any formatting, and make sure that any formatting that is needed is only on used cells. You can see my video here on how to make Excel faster: ruclips.net/video/M71kllagKzw/видео.html
I think this system has a flaw. The problem is the delay in synchronizing systems like DropBox .... Internal network will be more efficient. Even so I would recommend (briefly) a flag while someone is occupying the shared database, others in simultaneous update, should have to wait with a loop until the flag disappears. The flag can be a txt file with a user name code in the file name, which is occupying the database. I do this. Even so, in synchronization networks like DropBox, there may be failures. What do you think about, Randy? "Text translated by Google (Portuguese from Portugal to English)"
HI VIctor thanks so much for your comment. I have an updated Sharing & Sync training that takes care of all of these issues here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Great lesson, Randy. I have got two simple questions - 1- when I turn on the pc, it will get some time to sync all Dropbox folders (which contains other heavy files), HOW to understand the computer is still syncing? 2- when two users (working at the same front desk but using two computers) insert the same individual in the DB AT APPROXIMATELY same time, how is possible to prevent the DB to contain twice the same information inserted separately by the two users? Thanks again and regards roberto
HI Roberto, Thanks for your comment and I am glad you like the lesson. I am happy to answer your questions 1. The Dropbox sync folder (the one that you have designated for sharing) should just contain the small text file. Place your other larger files in other folders. At the beginning of the macro you can display a shape (or text in a cell) that says "Syncing. One moment please", then before the macro ends, hide this shape. 2. It is unlikely that two people at the exact same second are changing the exact same cell. For this reason the text names are based on the cell address. this avoids conflict. IN the event that more than one user changes the same cell, at the same second, Dropbox will make a choice (or show a conflicted file) when two files with the same name are used. Theoretically you can program to look for files with this conflict, then alert the user, however that would slow down the macro, and speed is an issue here. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thanks Randy. I will do it. As far as point 1 is regarding, actually I was asking if there is any way to understand (in Vba) that the DBFILE has not been synced (yet) to latest version. It happens when you turn on computer B early in the morning when last change were made by computer A late evening. Sometimes Dropbox takes 3/5 minutes before syncing all files changed in the meantime. And even if folders are different there is no way to ask Dropbox to sync folder with DBFILE with a sort of priority
Hi Roberto, one way would be to add a shape, based on cell text, that says "Last Sync on 3/29/2010 at 10:50 PM" along with a "Sync Now" button, this would both tell you when the last sync was done, and also allow a user to initiate the sync process. Also if the sync process is long (with tons of files) during the sync, you can have a "Syncing in Progress" message to help the user understand what is going on. (as far as priority of files that get synced, that is handled by dropbox so i am not sure how that order can be changed) I hope this helps. I will probably do another lesson on this since the last "Global Workbook Sharing" video was part of a larger Employee Manager, I think some of the details got lost. I want to simplify this with just a single table and program it from scratch during a video. Do you think that would help?
Excel For Freelancers YES! Definitely it will help. I agree with your suggestions but the issue is a bit more subtle than that. Actually we Sono in un meeting. Non posso rispondere. Se urgente invia sms. Grazie a più tardi another property of DBFile which provides us with the information it is going to be updated through Dropbox synchronization. So we use the LastModified Property to know if proceed or not but BEFORE that we MUST know if the LastModified field is not going to change within few seconds.. EG: Computer A turned off at 6 pm and Dbfile.lastmodified = 5.57 pm March 30th Computer B and C still working up to 10 pm making several changes and finally Dbfile.lastmodified = 9.59 pm March 30th. That’s all correct for Dropbox. In the meantime, since March 30th 6pm, many other shared files are created or modified. On Monday morning at 8am computer A turns on and user proceeds to make its own change to database and actually he will write to Dbfile as the computer had got no time (still) to actually update the shared Dbfile. Now we could solve the issue if another property of Dbfile tells us the Dbfile is under syncing process (we know that because the Dropbox icon is blue and not green...). I put the same question to Dropbox but still did not receive an answer. Let me know if I could help in any way to make your newer lesson even more fantastic because let me say you are really GREAT! And clear!
PS. I switched to Fileformat 50, binary file, smaller size and quickest saving time. For huge DB it helps a lot (I am working with 3 files more than 10.000 lines each)
Hi and thanks, I do have a simpler way and I share it with you in this video ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Hello, great video. Thank you for sharing. I am having a problem with the database file being deleted from the folder and a new one not being added back to the folder. Any suggestions on what I need to do?
Hi and thanks for your comment Nick. This was my first 'shot' as Sharing & Sync. I found even a better way in which only cell-by-cell changes are synced. You can see a complete training on that method here: ruclips.net/video/aKV5seZmiBs/видео.html
@@ExcelForFreelancers Hello, thanks for feedback. Can you apply the new method with this data entry form? I have 24 categories of data that I am collecting for each line item. I like the data entry form for the ease of entering the data and also for viewing the data 1 line item at a time in that data entry form instead of having to scroll through all 24 columns. My data entry form is all within one screen and does not require scrolling. Hopefully this makes sense. Thanks!
Hi Nick, you can use Data mapping for that. I have a few videos that cover that so please search my channel for "Data Mapping. I hope this helps and thanks.
@@ExcelForFreelancers I appreciate the feedback. I know you have provided me several different suggestions based on my requests. I have setup a file based on this original video. The only setback I have right now is sometimes the files takes some time to perform steps and I have issues with the master database file being deleted and a new one not being created. Trying to figure out the best way to combine these different methods into one concept. Look forward to your feedback. Is there any other way to communicate outside of commenting on here?
Hi Nick as mentioned above, its best not to share the entire database and only sync one sell at a time. This video will solve that solution for you: : ruclips.net/video/aKV5seZmiBs/видео.html You can also add your questions in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 25,000 Members who would love to help you with this. Thanks so much.
Excellent Video ! I've fully followed it but encountered on a small problem. Sometimes ADO does not copy data from recordset fully. I've did it with two different PC but when on a second PC I opened database file in that moment even on my pc local database got updated. Otherwise it did not. Could a reason be, because there were empty cells/rows in database file, so recordset did not recognize rows? Also, could it be that it's a problem because under numbers I wrote text and versa so it had problems to recognize columns fully (it was just a test with some gibberish data, so I could check does it work properly)?
Hi and thanks so much. I have an updated video which may be a bit easier to share and sync your macro-enabled workbook here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
@@ExcelForFreelancers yeah I saw this also... I had similar idea before some time, but I did not think about one cell per time so it would be a complete mess with symbols and semicolons (like CSV...). You're amazing and very talented and the most brilliant thing are your ideas, definitely ! Thank you for your time :)
Again a great lecture on Excel. I love the database connection facility. It works great. Does it mean other users need a OneDrive account? Or is it possible to share my file on OneDrive with others and they can save wherever they want it on their computer?
Hi thanks for your comment and glad you love the training. You would want to make sure that all users have the same shared folder (OneDrive, DropBox or Google Drive will all work) This is important because you want changes they made to be able to come to you as well. Also i am making an other video on Global Workbook sharing this Tuesday (January 29th, 2019) So make sure you watch that, as I will show you a whole new way to sync your application with unlimited users around the world using a shared folder)
@@ExcelForFreelancers Thanks Randy, it works fine. I like to mention, that with one of your tips on security to hide the CustDb sheet so users can't change anything there, will cause a problem when syncing to the database. It will kill the DbFile (and no longer available on OneDrive), but an error will appear when copying the changed data. It can't find the CustDb sheet. You have to unhide the sheet first, sync and unhide it again, it seems.
Hi Bert, thanks so much for the suggestion and much appreciated it. Will make sure to undide, then hide within the code. Tomorrow (Tuesday 1/29/2019) I have a whole new way to Share and Sync your macro-enabled workbook with anyone in the world. So please don't miss it. I introduce an even better way to sync: ruclips.net/video/3E6ExWYscHQ/видео.html
Thanks for your amazing video. My database has cell interior colors and font colors. .CopyFromRecordset copies the values only. How can I copy cells' formats like colors?
Hi Iman, thanks for the comment. You can use a separate database to store cell details such as font, colors, borders, size, etc. Then this database can be synced and updated among users. I am actually teaching all of this with Invoice and Purchase Order templates in my Excel For Freelancers Mentorship Program. I hope you can join us. You can see the details here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Great content!! Why not use MS Access though? It has all those features built in. I also see a problem with capacity. Once you get 1 milion customers the "database" should be archived, cleaned
Hi and thanks for your comment. We don't use access because people around the world love and want to use Excel. We are not trying to build the best application possible, we are trying to build the best application possible using a platform that people already have and love using. This is a perfect solution for those small and medium sized business with less then 1 million records. (that already covers millions and millions of business around the world which is a sizable marketplace) Thanks for the great question and feedback.
Mr Randy, excellent, efficiency at its best !!!!. I have just one concern that maybe sounds silly but I haven't wrapped my head how it fits in the code, probably I am missing some key concept, but here it ts. Let's say I am working on my workbook and do changes, modify the data, but don't commit the changes right away, let';s say I step out for a coffee, now someone somewhere edited the file, making my version older, in fact they deleted the record that I was updating ( don't know it yet ), now I am back at my desk and press UPDATE, first the syncfromdb module will pull the newer data overwriting mine because my changed date is lower than the depository's file, does this scenario enforce the user to hit the update/delete button right away, or ...else ????. Thank you for your awesome videos and kudos for your great work..
Hi Edward, thanks very much for the feedback and glad you love the videos. The video you are commenting on was the first video on Sharing Workbooks. I have actually made another one with some updated options and streamlined features here: ruclips.net/video/3E6ExWYscHQ/видео.html Concerning your questions, in this new video, as soon as you Exit a cell after making a change, that change is then sent to every user in a small text file, Also, in this updated record, users can clear data from a row, but not delete the actual row, so if you make a change after they make a chance, your changes will take presence. (the newest changes always win). When you are programming this type of application, the only thing you want careful of is making changes to more than one cell at one time, which should not be done (no deleting rows/columns and no clearing out entire rows/column. You can use VBA to run For/Next loops to clear out a data record in a row. The newer video will make things a bit clearer and it works very well. I hope this helps
I just love how you spoon feed us with these trainings. Just a question, can you use this program with OneDrive though? Because unlike Dropbox and Gsheet, Onedrive's sharedfolder doesn't hide the local filepath on other people's PC when shared. Therefore, the code you put in filepath will return an error on other people's PC when using OneDrive as your cloud storage. I am trying to find a solution on this issue and I hope you can help me with this. Thank you and God bless.
Hi and thanks. I have not tested this in OneDrive because I really did not like the restrictions put on. I am sure you can get your answer by posting your question in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Otherwise just use DropBox. Its free and its great. Thanks so much.
Hi Randy. Thanks for this valuable lesson. One question please. Will this work in a Microsoft Teams Environment? I want to build a CRM and I am thinking of adding the front end spreadsheet as a tab in a Teams channel "CRM" and the backend workbook in the Sharepoint documents folder for that channel. I am guessing that this will enable my team users to update and add contacts in an online Multi User environment. On another note, if you can point me to any of your workbooks (I just bought and downloaded the 120 workbooks) to get the user logged into Office365 while working in the Teams, I would appreciate it. Thanks again and I will certainly be a follower while building my MS Teams site.
Hi Silvan, thanks very much for your purchase and comment. I am happy to help. This should work for any type of a shared 'folder' environment, and that means that a local folder on your users computer MUST also be shared with all other users (I am not sure if Microsoft Teams or Sharepoint provide a shared folder on a local drive) However DropBox, Google Drive, and Microsoft One drive, plus a host of other applications does provide that feature. Most of them free. Here is also an updated video of Share & Sync that is a lot better: ruclips.net/video/aKV5seZmiBs/видео.html
Thanks for this awesome video. You are the best. I just have one question. I notice that on your program if there will be two user who will click new contact at the same time or almost the same time. They will have the same id no. Now when one of them click save. The first user who click save will be recorded on the database however if the second user click save the record will be overwritten on the first one. Is there a way to avoid this? Thanks
HI Bernard. Thanks so much for your comment. A new ID is not created until a contact is actually saved, so that helps for sure. as soon as it is saved, the Contact ID' is saved and placed in the shared folder. Theoretically there could be duplicates but with just a few dded lines of code to make the updates if ever there was a duplicate would solve this issue. So for example each time a new contact is saved, just a quick formula to see if there are any duplicates and make the update if needed. Almost any issue can be solved with just an added check or few lines of code. Make sure these small issues do not prevent you from creating your application. Also please feel free to post your questions in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel Thanks so much
Amazing video! Would you say that it is possible to apply that logic to a larger scale (80-90 people that have permission to make changes and a total of 40 columns) without suffering too much on response speed? Thank you very much for your help!!
HIU Merlin yes this should work just fine. You can use my updated training here as a guide: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Great tutorial! Very detailed and excellently voiced, couldn’t ask for a better teacher to help me with the automation process for my little shop. But I found a sort of bug? Or is it a feature? Anyway whenever someone is saving a row of data on a sheet and then someone else is doing the same - they get overlapped and one data is totally lost and they need type it again. As I understand it, it couldn’t be fixed because whenever someone pushes create button for a new row of information, it “reserves” an ID number for it which then it uses to know on which row to put it and it couldn’t be done whenever two or three persons trying to do it at the same time, cause excel isn’t dynamically changeable system. Am I wrong?
HI thanks very much. I am glad you like the training. I have an updated version of the Share & Sync which solves this issue by only syncing one cell at a time. You can see that video right here: ruclips.net/video/aKV5seZmiBs/видео.html
Hi, love this tutorial and the concept! i'll subscribe. I have created a similar version, but the data i am tracking is dates, but when i refresh & sync the date format changes between UK and US date formats and sometimes deletes dates. All non date fields are fine...any ideas?
Hi Anne thanks so much. When you save dates to the table, save them in their number format, such as 43831 instead of 1/1/2020 This way they will work regardless of the date format used. Make sure to use this format in any filters as well. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers thanks so much, yes, it works fine when saving them in number format, the problem is displaying them so users can view or changes dates on the front end. I found a workaround although its not pretty :) i'll join the book group! thanks again!
You will always want to separate your front end (formatted dates) with your back end (unformatted dates) which users don't see) I cover this a great deal in my Mentorship course here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Would this be scalable for a Returns database - the table is 19 columns and currently 27,742 lines (527,098 cells) and growing by the end of 2021 I expect to have around 40,000 lines. There is conditional formatting that depending on what is entered into a specific cell the whole line is coloured accordingly. A Data Entry form and search from another one of your videos would also be added. Yes, there are better programs, etc for this but Excel is what I have to work with.
Hi Matt, yes certainly it would, a few hundred thousand lines is acceptable in Excel. When it gets into the millions then you may want to look for another option for data storage. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thanks for the reply and I'll look into joining the Facebook group. I would prefer another method for data storage but unfortunately, it's not my decision and is highly unlikely to change. I can't upload the workbook or screenshots anywhere due to GDPR.
Hi William, thanks so much. I have an updated training on this here: ruclips.net/video/aKV5seZmiBs/видео.html Feel free to download the workbook using the links in the description. I hope this helps and thanks so much.
HI Michael, yes that is correct. Each user actually uses different Excel files, but they all share the same synced back-end database files, so its far better than Excel's Shared Workbook feature in which users actually share the same file, which causes lots of issues.
HI, Only 1 question, You mentioned that people can access it from "all around the world" Can you please confirm how this is possible if the workbook sits on the local or a shared drive at work? Thank you
Hi Imre, thanks for your comment and question. It can be shared all around the world when they have a shared folder such as DropBox, Google Drive or Microsoft One Drive. The workbooks themselves do not have to be in this folder however all users much have a single shared folder in in which the data moves back and forth. I have an updated video training on this here: ruclips.net/video/aKV5seZmiBs/видео.html
Hi I have one question here, let’s say two people are using this at a same time and they loaded the information at the same time without clicking on update will both the information gets loaded in the file ?
Yes they will, but use my newest sharing workbook training video here, this way the updates are automatic on every change: ruclips.net/video/aKV5seZmiBs/видео.html
HI yes it is possible you can institute a Change Log. I have described how you can do that in these two videos: Part 1: ruclips.net/video/6TnxPvLz_44/видео.html Part 2: ruclips.net/video/fE15W72vnEE/видео.html
Hi Randy. Great training as always! Quick question. Does it matter if the dates being compared in the Sync macros come from different time zones? If so, what modifications would be needed to the code?
HI and thanks so much Albert, I have an updated method that will work, regardless of time zone here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
@@ExcelForFreelancers Hi Randy, thanks for this. I've watched it all. Using your updated method I can see you can sync single cell changes. What approach would you suggest to sync if say, the change made involved deleting an entire row from the data by a user in an entirely different time zone?
HI Albert thanks so much. You would never want to delete rows when using Share & Sync. You would just want to clear the contents of all but 1 column (leaving the 1st column with the ID in tact) You would clear the cells within the row by looping through the columns from the 2nd column to the last column, cell by cell. This way changes are only made to a single cell at a single time. I hope this helps and thanks so much.
Hi and thanks for your question. Multiple users, can access the same data using duplicate applications when the data between them is shared and synced using a shared folder such as Dropbox. I explain how to do this in this updated video here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Please, sir, I need your help. I have created a simple hostel management system with Microsoft Excel vba, but my mum said she wants to be monitoring the operations from her end because we don't stay together . How do I do this please?
Hi and thanks, so much. The easiest way is to have both you and her install the free Dropbox software. Then you have a shared folder between the both of you and she can see it just as well as you. If you both want to make changes at the same time then to share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/f_1HsR9305I/видео.html I hope this helps and thanks so much.
Please, good evening... I have been following your studies since. But don't you know whether you can help develop a pharmacy inventory management system with Excel VBA
Hi Newton sorry about that. Did you check your spam folder? Also did you enter the correct name and email? (I will also check with my software and search for your name to see if i can find it) I want to make sure you always get the download that you requested. Just let me know on the above. Thank you.
Hello Sir, Thank you very much for such a helpful video. I have a question, so if my company have 100 employees, i have to create 100 copies of the Front-end file? And how's it gonna work then? I hope I can receive your message soon. Have a nice day sir
Hi Thao, xin chao. You can simply create a single application and give it to each user. Each user uses their own application and only the data is shared using a dropbox file. Also make sure that each user has shared access to the same dropbox folder. I have a better and easier training for sharing and syncing here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks. Cam on ban
I used this and it is awesome. However we are coming across a problem where the file in the share drive gets deleted and does not write a new one and we are left with the sheet asking us where the database is and its been erased on the share drive. We cannot figure out why it is doing this. Any hints or ideas on how to fix this?
Mr. Randy, Excelent!! I've tryied to run it and got it separetedly wonking perfectly, but a i miss how or when the syncFormDatabse is called. I didn't see any call for it. Can you help? Many thanks
Hi thanks you can sync from database at any point you want such as workbook open, sheet activation or at timed intervals. It is entirely up to you. Also make sure to see the updated Share & Sync video here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
HI Pedro thanks that is a bit part of the Accounting application I am teaching in my Mentorship Program here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Hi Mr.Randy lovely tutorial,I made a similar model watching your video ,however when I run it ,it says "Complie error " Sub or function not defined And SetonKey is highlighted in blue. Kindly assist
Hi make sure the line is "SendKeys" also please post your code in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 25,000 Members who would love to help you with this. Thanks so much.
Good morning sir! I've just finished writing all the macros that you taught us and I had one problem which was everytime i pressed the SAVE or DELETE button, the excel screen went all white and delayed about 2 seconds. May you help me to solve this problem? I'll be thankful Thank you and best wishes for you
Hi and thanks you can try turning off screen updating at the start of the macro and turning it on before the macro ends. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi Randy, thank tou for your tutorials. I have a problem with the cells which contain numbers when using copyfromrecordset. The value of the cell is simply not copied over and the cell is blank. Can you please advise?
Hi sure you can try a value to value copy for example Sheet1.Range("A1:B1").value = Sheet3.Range("A1:B1").value You would just need to make sure that the # of cells in each range is exactly the same. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Sir.. i had marco enbled application . After seeing ur video I also want to save data in backend using Dropbox... Can i send u my application so that i can save data in Dropbox folder
HI Vinay, thanks so much for your comment. I have an updated video which allows you to save data in a dropbox folder here: ruclips.net/video/aKV5seZmiBs/видео.html. Since I am developing courses I am unable to take on any additional work, however I would be happy to refer you to a competent developer who could handle this for you for a fair price. If so please email me your entire and detailed project scope here: Randy@ExcelForFreelancers.com Thank you for your Likes, Shares & Comments. It really helps.
Hi and thanks, this may help, you. To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.html
Hi Randy, Still enjoying this project but about to make a big change since I noticed that some of the fields in the database I imported have mixed data types and that ADODB sometimes fails to bring in field data with mixed data types. I played around with the IMEX=0, IMEX=1 in the connection string with mixed results so I thought, why not use the External File refresh for the SyncFromDatabase. First impression is that it might be a tad slower but all the data comes in especially since I was able to set the Data Types in the query The SyncFromDatabase macro was simplified as below: Sub SyncFromDatabase() If blDebug Then Debug.Print Format(Time, "hh:mm:ss") & " Sub: SyncFromDatabase", "Events = " & Application.EnableEvents, "Calc = " & Application.Calculation, "ScreenUpd = " & Application.ScreenUpdating ' Replaces the StaffDB sheet contents with data from the Main shared Database LastLocalChange = Sheets("Staff").Range("Last_Local_Change").Value 'Check Database file is newer than the our last update If FileDateTime(Range("DBFile")) > LastLocalChange Then 'Database Change was made, update Local Database Sheets("StaffDB").Cells(1, 1).ListObject.TableObject.Refresh End If DoStaffDBFormulas 'Formulas not brought over Exit Sub FileMissing: MsgBox "Please browse for the database file" BrowseForFile End Sub I have formulas that calculate age, length of employment, payrate, etc in the main database so I automated putting the formulas in the refreshed database, the Data Entry part of the form and the temp table on the main form. In your SyncToDatabase macro, I made a mod when I realized that if I have the shared database open for maintenance, the Kill(DbFile) will fail and the error handler will just ask you to browse for the file. I forgot I had it open so got stuck in a loop. Now the error message will give me a hint... On Error GoTo FileMissing Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.GetFile(DbFile) If objFile.DateLastModified < LastLocalChange Then 'Local Change was made, update Database Kill (DbFile) 'Delete the current database version ThisWorkbook.Sheets("StaffDB").Copy ActiveWorkbook.SaveAs DbFile, FileFormat:=51 ActiveWorkbook.Close False End If Set objFSO = Nothing Exit Sub FileMissing: Set objFSO = Nothing msg = "The file: " & DbFile & vbCrLf & "is either missing or inuse..." msg = msg & vbCrLf & "Please close the file or select another file" BrowseForFile End Sub
Hi and thanks so much. That is a nice update. I have an updated version of this that focuses on a cell-by-cell change which does not risk removing and replacing the database. You may like this method better. ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Thanks so much for your comment. By learning these skills in Excel, many companies would be happy to pay you more. Investing in yourself and your learning will certainly help you reach a higher salary in the near future.
Thanks a lot sir , i have a question actually , i had designed an excel work book to save marks of students ,my file hold macro and VBA code , is it possible for me to share this file with many teachers online and make them insert their marks ??
Hi yes you can. You can follow this 'Share & Sync' training to do that: ruclips.net/video/aKV5seZmiBs/видео.html Thanks for your Likes, Shares, & Comments. It really helps Yousef.
Great job man. Can you please teĺl if I need additional 5-6 columns in my database that needs to be maped, is that too much work, and which part of macro I should change in order to work correctly?
Hi thanks very much for your comment. There should be no problem making those changes to add to the code both in the macros and on-sheet code to customize this for your specific application. You can update the ranges based on your specifications. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Hi Boja, I've completed your request to add 7 more columns. can you send me a email to forward the zip file with content.Please send towards R.Haman@ziggo.nlBr. Rob
@@robhaman4424 I'm also trying to add additional columns but it's not working, when i refresh the file it shows the REF error. how do you go about adding additional columns and data entry box?
Hello Randy, I would like to ask how can I use it with sharing file. If IU share the CustDATA throught one drive , I send the link to receiver , but it always open the Cust data on web , and I am not able to identify the source on second computer through browser? do you have any idea how it should be shared?
Hi Tomasko, Thanks for your comment. When you share this file using one drive, you want to make sure that the person you are sharing it with has the One Drive software installed on their computer AND that one folder in their One Drive is shared with you. This way when you save your file they Automatically get it on their computer. You will not have to share any link at all since any changes in your shared folder will Automatically come to their computer. (When you share a link to a file this will go to the website which is not needed)
HI Thanks so much. I did just check both links and they seem to be working. The Email link goes to my website in which you can enter your email and name, the 2nd link goes directly to Facebook Messenger to get the download. (Make sure you download on your computer and not your phone) Please let me know if you have any other questions. Also feel free to join our Excel For Freelancers Facebook group here; bit.ly/groupexcel
Hi I tried this and working fine. Really obliged for such amazing training Just one thing when I try to password protect the main database file it is showing error on SyncFromDatabase macro as
You have a few choices when it comes to protection. You can unprotect the cells of the database sheet and just hide the sheet, or you can also unprotect, then reprotect during the macro or you can add xlUserInterfaceOnly which allows VBA to make changes but not the user. (any of these should solve the issue.) Thanks so much for following.
Hi I have watched your video mentioned that the back end database can be stored in onedrive for sharing, Do you have video that teach how to give access in onedrive to other users that doesnt share the same local drive with me? thanks!
Hi thanks for your comment. i have used DropBox, but OneDrive should work just fine. Just right click the folder and select the One Drive Sharing option to add the email address of the person you want to share with. Thanks very much.
@@ExcelForFreelancers Hi I am so happy to hear from you. I noticed when i right click on the folder to give access to someone, it only allows me to share with someone with same network access with me. Could you mind to advise if this macro could be changed to connect to an excel that is saved in web version onedrive/sharepoint business instead in a local drive folder?
Hi would just use DropBox it is so much easier and free, then you can add anyone in the world as long as they have a Free Dropbox account as well. Thanks again.
HI thanks for your message, Using the "Now()" formula and adding seconds to the time formula would not be a problem as well, so that will remove any doubt as to when the file was updated, even without seconds. I will be adding this 'Shared Workbook' feature, with seconds, to our Employee Manager Application in which we are designing in our current series, so make sure you watch that one as there are multiple databases that we will be working with. Thanks again for your comment.
@@ExcelForFreelancers so how do you share this database xls file? Via dropbox or inbuilt excel sharing option? As im in desperate need of centralising my database for my program which is used by my friend in india and im in uk.
HI thanks for your comment. The workbook is shared via DropBox (others sharing applications may also work but Dropbox is the best in my opinion) I explained it all in this video on exactly how to share your workbook with anyone in the world, so just download the sample, follow the video and then you have your exact solution.
Hi, Randy! Very interesting way of sharing indeed. I have a question though regarding the local time. Supposing the DbFile is stored in country X, GMT - 2. The front-end file is shared among different countries, A, B, C, with GMT - 4, GMT + 2 and GMT + 4 respectively. I don't see a way of comparing two "local time" variables coming from two countries with different GMT-s. Wouldn't it be better to change the "local time" variable from every country according to GMT + 0? This way we would be sure that changes in the database occur every single time. What is your point of view? Thank you!
Hi Daniel, Thanks very much, and yes that makes perfect sense when sharing an application across time zones. However in my updated 'Share & Sync' training, time-zones are automated since I no longer need to share the entire database, i simply share changes cells in small text files. Feel free to check it out here: ruclips.net/video/aKV5seZmiBs/видео.html
Hi Randy, I tried this in my computer but problem is syncfromdatabase is not working so when one user put some changes , these changes donot appear in other users CustDb file when pressing refresh. Can you please help where is thing going wrong.thanks in advance
Hi Hima thanks for your comment. I have an updated Share & Sync video I think you will really enjoy and its a lot easier. Please try this one here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
It's a great learning, helped me to connect to database without opening the file. Howevere just wanted to check I can have a password protected database, if yes? then how would I ensure that Syncfrom and Syncto macro has that password, any suggestion.
Hi Adil thanks so much for your comment. I have an updated training which will only share a single cell & data at a time. This will allow you to securely transfer data without a password. Please see this updated training on Sharing & Sync: ruclips.net/video/aKV5seZmiBs/видео.html
Pl share this video step by step wise from a scratch. It is very useful video. Thanks in advance. Hope you will upload this step by step video soon, because I tried to mirror this video but unsuccessful.
Hi Thanks so much. I have a more updated version of this which is step by step from scratch right here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Can i use this with one drive synced folder? The problem i have is when a user makes an update on the front end form is the delay of the onedrive sync, it may end up replacing data coming from other users.
Hi and thanks. If you find that there is too much of a delay, just use DropBox, which I have found to be very fast. You can test all of them and see which one is the fastest for you. Also please make sure you watch an updated version of this here: ruclips.net/video/aKV5seZmiBs/видео.html
📅Excel Calendar Pro: Your Solution to Stress-Free Scheduling 👉 rebrand.ly/CalendarPro_PinnedComm
Dear Randy,
I'am already working with Excel for years (and I love it) and I have already made some applications. I also have followed some special training/courses (not always cheap) but I have to be honest these videos are the best I haven seen in a (very) long time.
So just a special thanks and lots of respect for the tremendous effort you put in these videos, keep up the good work.
Thank you so much Bart, I really appreciate your comments and they inspire me to push the envelope. Please make sure you also check out my newest 'Share & Sync' video which is much quicker than this older one here: ruclips.net/video/aKV5seZmiBs/видео.html
I do a fair amount of VBA and consulting in Excel, but will admit you guys are taking this to a whole new level. Thanks for sharing. Awesome set of videos. Worth every minute of them!
Thank you so much Yves, I really appreciate that. Make sure you check out a newer version of the 'Share & Sync' video right here: ruclips.net/video/aKV5seZmiBs/видео.html
Mr. Randy, You are God Sent to us that love to learn. May God continue to bless you and keep you. Love to you and yours from Israel...Arad
Thanks so much Tyrone, I really do appreciate that and thanks so much for your kind words.
What a genius approach of non-conflict data syncing. Thank you so much.
Thank you so very much, I really appreciate that
I even have a better one that works faster and more secure here: To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.html
Hello wondering if you can assist. I just have an Excel worksheet that contains a table and each column has a filter. How do I make this a shared workbook? We do have Microsoft One Drive at my office. Thank you for the help.
Hi Diem, thanks so much. You can use any shared drive such as Dropbox or Google Drive or any other one. I also have an updated training on this here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
This is a great turorial.it really helped me.btw i tried this on the network drive however it wont open or copy a file from the source drive.it seems its not connecting or opening the connections.
HI and thanks so much for your comment Arnold. I am glad you like the training. Make sure you have user access rights and access to the drive and check connections.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Another great great idea, thanks Randy.May I ask in this application, are any way we can lock the customer data base?as it is sharing, so if anyone go to sheet CustDb and destroy data, which could destroy entire application data....
Hi Xuan, thanks so much. You bring up some great points and i have a few solutions
1. This was my first video on workbook sharing. I have a new video which updates only the changed cell and not the entire table here: ruclips.net/video/aKV5seZmiBs/видео.html
2. The actual data table, in my opinion should never be viewed or access by any user. In a well-developed application there are 3 total screens for a single database (1. A form to enter, load, view and modify data, 2. A visible table to allow the users to sort and filter data, but this is not the actual data. 3. The database table that is completely hidden from all users. It is this data that feeds both the form and the filtered and sorted list)
When we build strong applications that have the above components it is very hard for users to break or damage the data. I am teaching this entire process in the Mentorship program in which we will build an entire accounting application that is shareable and able to be synced around the world.
Thanks so much for the great question.
Hello, your videos are so helpful, thank you!
What is the difference between this video and your other video titled 'How to share and sync your macro enabled workbook, from scratch, with anybody in the world'?
Hi Adam thanks for your comment. In this video the entire database is moved back and forth while in the updated Share & Sync, each cell by cell is synced here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
@@ExcelForFreelancers thank you for the response!
This is AMAZING! I want to add slicers to the front end of the Contacts table. How can I do that without the table messing up when I update or refresh? I have tried all possible methods I can think of, but cannot seem to make it work. Thank you for all that you do! You are a magician!
Hi an thanks so much. Anytime you want to use filters, sort or slicers, on something like this. Don't use your original data. That date should be hidden and away from any users. Use another sheet that pulls the original data. You can then add slicers, filters, sorting or anything else as the original data will remain intact. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Once again, you never fail me! This is awesome! However, i have a problem when i want to add rows to the contact info. I have 7 data instead of 6 as shown in the video. Can you do a quick video on how we can add the rows to it? Thanks in advance!
Hi and thanks I have an updated video which may help you here: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
Hello, thanks a lot for your nice tutorial, it seems fantastic. The only question is: Are we sure, that if 2 users click at the same time on "Save" it will not make an unexpected behavior ? Data that would have the same time (date, hour and second) of last update for both back end and front end for example ?
And maybe an answer to my question, but to which extend is this version better as you said in a comment below ? ruclips.net/video/aKV5seZmiBs/видео.html
HI and thanks for your question. Please watch my updated Share & Sync which is more optimized here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Fantastic video - this is exactly the solution that I'm looking for. Question: How many users do you think this method of sharing can realistically support?
Hi Christopher, there is really an unlimited # of users at the same time. Since each user uses their own workbook, there is really no issue with that. Only the data is shared. Before creating your own, please check out my newest Share & Sync method which is much quicker than the one you commented on: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for this video! Question - Do you know how I can share a macro with someone? I don't need to have a share file, but instead let them run their own file using the macro I built (it is under Personal.XLS). Let me know if you have a video on this or if I am missing it in this video. Thank you!
Hi and thanks for your comment. It's better to share a limited xlsm file with only the macros you want to run. However it depends on the type of automation you are looking for. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you so much for the video. My question is that I have an Excel File (i.e., My Excel Version is 2013) with Marcos. How do I upload the Excel File on Google Drive and still keep all of my Marcos and share the file to users inside and outside of my organization
Hi and thanks for your comment. Excel and Google are very different so you could not combine them when they contain macros. If you would like to share and sync your macro-enabled workbook with anyone in the world, you can follow my training here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Well done. I did something like this back in the late 90's (excel 97/2000) for a budget program that worked very well. You have taken it next level with your code and especially by sharing it with others. When I did this we had the front end as a single file that was read only so that any changes to the code were automatically accessed by all. One file with all the code that was managed and tested before rollout. Read only was unsettling to our users so we intercepted the "read only" message with a "company name" message. Not sure if you can still do that with newer versions but back then it was our solution.
Hi Gil thanks very much for your comment. That is a very novel solution. I am glad you liked this Sharing workbooks video. I will build on this Sharing Workbooks idea with our current Employee Manager series in which we will be sharing several databases in a single application (Employees, Events, Payroll, Timeclock, etc) so please watch this series for amazing tricks and brand new features never thought of in Excel.
@@ExcelForFreelancers I have subscribed as I do find this interesting. Glad to see others pushing excel to the limit. I'm not programming VBA anymore as I retired awhile back. When we wrote our application it was to replace a lotus123 spreadsheet that was just a temporary thing till they could find a better solution. The company ran ours for over 10 years so it turned out well for them. It ran on 6 citrix servers and a NAS to hold the app and data. It was accessed by users across North America over the internet. Pushed Citrix and pushed Excel and I think we found every bug in the software. Even used a few to get things done.
Wow that is amazing. I do remember Lotus 123. That is where it all started. Thanks so much for sharing.
Hi Randy. I have been watching your videos for the past few months. On this video, is there a way to alphabetize the list. Thanks for great training videos.
HI Jacob, thanks so much. When you have a list in a shared workbook, its best to have 3 different levels of sheets (1. A sheet form in which users can add, update and delete records, 2) a List view in which users can sort and filter data 3) the Actual data which users do not have access to.
This will allow you to sort and filter data without disrupting the actual data.
I have an updated share and sync video here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Hi Randy. Next Id comes from cell B8. It is created by adding 1 to the last record in the local Db. This means for example two different users can use the same Id at the same time? Shouldn't it be necessary to check if the common database has changed and if local next Id used already, before create new Id?
Hi and thanks. Yes no doubt. When adding new records, before even the next ID is calculated, we always want to check for updates from other users before setting a new ID.
I share that with you in this updated video To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Thanks for a very nice application. I have a slight problem. After adopting the application to my needs I did all testing and everthing worked nice.I was able to add, update and remove data. However after hiding columns, rows and 2 sheets I made some additional testing and then the database was deleted but newer recreated! Have any had this problem? Any idees of what can cause it.
Hi Kristian, thanks so much for your comment. I do understand the issue. I have an updated Share & Sync video that does not replace the database but changes on a cell-by-cell basis here: ruclips.net/video/aKV5seZmiBs/видео.html
Your work just impressed me alot,so i tried to replicate the same thing with small modifications but i didn't get the same way the result while im running marco of save and update contact im unable establish relationship with dashboard and database it was always asking the database path.im happy if you help me.
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Does this work with VBA too? Multiple users opening the VBA forms and using it
Yes for sure it does. I have an updated video that works even better here: ruclips.net/video/aKV5seZmiBs/видео.html
@@ExcelForFreelancers i can't wait to try it 🙂 i hope i can succeed. I just did a 3 hour presentation today for employees of a document i made that use a VBA program that add/edit/delete from a sheet in the same document. It is uploaded to Microsoft Teams. And when i showed it... Errors. It asked something of if I want to create a copy or some ActiveX problem, i don't remember what it said. I was so embarrassed. I had to make them look at a document that I couldn't even promise if I could get to work. I had to test it live because I don't have anyone that can help me test multiple users. For me it worked opening in Teams as long as I opened it in desktop app.
This is an amazing file. Created one with your help. The only issue what i encountered is that if, for example, I delete rows from dbfile, even if it is newer, will not overwrite the existing data in the main contact file. It will overwrite only the number of rows in the recordset - if there are less, then less rows will be updated. Is there anything from my side doing wrong? Or an easy fox for this? Thank you so much!
HI and thanks Tudor. Much appreciated. For Sharing & Sync, rows should never ben deleted. You can clear the contents of a row except for the first column (usually ID/Key)
When deleting you will want to clear the contents on a cell by cell basis. I show you how to do that in this updated video: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Your videos's are excellent and you make your coding examples easy to follow. thanks so very much for sharing your knowledge
Thanks so much I really appreciate that Alan. I look forward to making new and interesting videos each and every week.
Amazing!! This is the best Excel vba channel on youtube! I have a question regarding syncfromDDBB. What happens if for any reason the recordset is empty? For instance maybe if the file is killed by other user. How can you check if the recordset is empty? If I´m not wrong, If the recordset is empty the syncfromDDBB will blank the local DDBB. Thanks and congratulations!!
Hi Xavier, you are right. We can never have a blank record. What we can do is void or disable a row from appearing in Searches. For Example, lets say we want to delete an invoice. We Keep the invoice #, but mark it void and remove all of the other data within the row, cell by cell. This is why it's so important to separate the front end and the back end and have VBA control the process of adding, updating and removing records. I do have an updated version of this which is faster and records, cell-by-cell modifications here: ruclips.net/video/aKV5seZmiBs/видео.html (Also this next Tuesday I will be showing you how users can reset their own passwords securely without admin approval via their email)
Also I will be building an entire Accounting Application which will have shared & sync capabilities in our Mentorship Program starting in just a few weeks so keep an eye out for that.
Hi Randy, very impressive macros. I have a question: with this shared workbook if multiple users work with the database simultaneously, will it work? For example, USER 1 and USER 2 both loaded the database at 12:00 and started to work with it, they both added and deleted some records, then at 12:30 and 12:35, USER 1 and USER 2 both saved their changes. Will User 1's saved data got overwritten by User 2?
Hi Warren, thanks so much. Yes this will work with multiple users, simultaneously making changes, since each user is using their own workbook. Make sure you follow the updated training on Sharing & Sync here: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers Thanks for your reply Randy. Which way do you think is better? The whole page update or the individual cell update in the video you shared above? My understanding is the individual cell update is quicker but there is no "centralized" database that could be used to set up a new user. Is that correct?
Hi sir randy thank you for this tutorial. I have used this template and have recrated syncing for 2 workbooks. Just wondering how can you sync the workbooks if you share one file via onedrive to other computers? Will the other computer create a different shared folder? If yes? How can the original file check the folder from the other computer and vise versa? Thanks sir and more power
Hi and thanks so much. Each user gets their own workbook and only data is shared via any shared folder. You can watch this updated Share & Sync video here: ruclips.net/video/aKV5seZmiBs/видео.html
Hey Randy, Great video! i manage to get it to work for my excel but after sync, the data that i get was converted to text. Do you know how to solve this? i'm using excel 365.
Hi Mohamad, thanks for your comment. You can make sure the cells are formatted properly and you can also use the Format function in VBA to format certain columns however you like.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi Randy! Thanks for sharing all of your knowledge on this! I just have one question. When there's 2 files generating contacts and the next situation happens.
Person 1 - Has his own file open (with the next ID being 10) and doing some other work.
Person 2 - Opens his own file and generate a new contact. This new contact is the contact ID 10 on the File Database. And closes his own file. This means that the next "new contact ID" is now 11
Person 1 - Remembers he has the file open and generates a new contact. Since the code reads the Next Contact ID in his own file (Contact ID 10), it rewrites Person 2 contact he just created.
Should I create a line of code that if my LastLocalChange < DateLastModified it must first pull the information before assigning an ID that's already being used on the Databaste?
Thanks!
Or maybe run a check to see if the new contact ID hasn’t been used with a NewContactIId >than the Max contact ID in the data base.
Great question, firstly make sure you have seen this updated Sharing & Sync Video: ruclips.net/video/aKV5seZmiBs/видео.html
Secondly, anytime a new record is generated (new row with new ID), a check must be run to make sure that there are no other changes by other users. If so, then those updates are then brought in, and the sheet must be calculated, therefore updating the next User ID within the Max formula. This is only really important for new records which need new ideas on new rows.
Basically before we push out new records we must first pull in any changes by other users.
I hope this helps and thanks so much.
Hi sir....
Can you help me develop a pharmacy inventory management system in Excel, vba
HI , thanks for the information. There is no doubt we can help you with that. While I am unable to take on any projects I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com
Thanks so much.
Thank you Randy for your support... I have a question: How connect the database file if this DB is password open secured? thanks!!
Hi and thanks for your comment and question. I am not sure what you mean by 'open secured' Can you please explain
@@ExcelForFreelancers Hello, I mean How to Connect to a Open password protected workbook. Thank you!
Hi and thanks, you will want to contact the owner of the workbook and ask him to open it for you. I hope this helps and thanks so much.
@@ExcelForFreelancers Give me the e-mail of the owner, please
Mr.Randy thanks for your video about excel. They are awesome. But I has a question: maybe you can help me. So my question is: how I can create my own view with 12 columns ( like on video) from big database wich is include 50 collumns info. This 12 collumns are separrete in database, I don't know how i can combine those 12 columns wich is not spep by step in database.If you understand my pain, please, help).Thank you in advance
Hi and thanks. You can just expand the cell references within the code for as many cells as you want. This video will also hep you share and sync your macro-enabled workbook ruclips.net/video/aKV5seZmiBs/видео.html
Thank you again for the great video! I am currently working on implementing the solution in my company but I am encountering an issue. Changes to my table are made via an input form which makes it possible that several cells change simultaneously. It seems the code can't handle this issue as the file always crashes when more than onr cell changes at the same time... Do you have a solution for that? I was thinking about updating the entire row instead of the individual cell but I don't know how to implement
Hi and thanks so much for your comment Merlin. I have an updated solution that should handle that issue here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Hello Mr.Randy, do you plan to share or improve topic about sharing WorkBook with separate database? For example: as minimum 3 zones like your zone 'contact info', but with own design of fields and using one database for it? How it synchronize? How to generate datatable view and headname of columns must changed too based on each of zones?
Hi thanks so much, I have an updated video with a better technique on share and sync right here: ruclips.net/video/aKV5seZmiBs/видео.html
@@ExcelForFreelancers thanks for video, it's great too , but it is not what I need to know...
In the Accounting application I an designing in my Mentorship program I have several databases which will be shared. This application has specific Front and Back ends which will be able to be synced and shared using a shared folder for any users within the world. You can learn more information on this Mentorship Program here: bit.ly/MentorProgram_YTComment
Just another of many amazing videos you've made, and I've had the pleasure of watching quite a few. I'm attempting to make a shared workbook while adding some features of some of your other projects. I'm hoping to add your login security feature (the video that allowed you to toggle through various users' access privileges and was hoping you could tell me if there's any complications I should expect from adding the two together.
Also, I'm hoping to change your columns, maybe add a few, and likely will add some filters to allow me to sort them. Are there any risks to do that and what code will I have to modify to make all those changes possible. Thanks for sharing your amazing videos; I've learned a lot. With these changes I should be able to make an application that will be extremely useful. Thanks again for all of your professionalism and hard work. v/r Mike Frey
Hi and thanks so much. You are doing well and there are no risks and I have another video that can help you with that To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/f_1HsR9305I/видео.html
I also cover proper workbook protection in this video: ruclips.net/video/fW2yf47bAQ8/видео.html
I hope this helps and thanks so much.
Randy, Thank YOU very much. Your channel is by far one of the best. Subscribed! I have also checked the Share and Sync video. Very Powerful and seems like take cares some of the issues this one has. Hopefully you can make a video on how to sync excel with SharePoint List. That should help to take care the multiuser related issues.
Hi and thanks so much. I am really glad you like the training. I do have an updated training on sharing and syncing your macro enabled workbook here: ruclips.net/video/aKV5seZmiBs/видео.html
I have not tested this with SharePoint however you can give it a try. Thank you for your Likes, Shares & Comments. It really helps.
Hi, I got question. I am not sure if you mentioned it in video, but how is this solution protected against adding new contact by two or more users in the same time? Users can get the same last row right? Thanks for your answer.
Hi thanks the application will check for conflicts such as this and add the new contact to the next available line. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thank you for sharing your knowledge!
One doubt... When needing to know which is the last row of your data, you usually write "Range('X99999')", right?! Isn't better using cells(rows.count).end(xlUP).row?
HI thanks Paulo, there are many, many ways to determine the last row of data, so of course you can use whichever one works best for you. I also have an updated Sharing & Sync video training here: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
Hi there! Hope you read this! I'm new here. I managed put together a data entry workbook mostly similar to this thanks to you. But it'll be very helpful for me if you can show a way to put the sync to/from database on a loop. Since certain shared drives tend to be slow due to various reasons, the "Kill object" and subsequent creation of new backup can take a few extra seconds. This can lead to "objfile = nothing" for any other user during that time frame. Is there any way around this obstacle?
HI and thanks for your comment. I found an even better way for share and sync and I put it all in a training video here:
ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
Amazing ! That's it. I try to find this video for a long time. Thank Mr Randy for sharing this video. You're amazing.
You are welcome Samnang, I am quite happy to create these for you.
Hi Randy. Will this method work within a SharePoint environment where the file needs to be 'checked out' ? Thanks
Hi Rick, I have not tried this in SharePoint. Any folder that can be shared among users around the world would work fine as long as you can assign users access to those folders. However the advantage is you don't need to use SharePoint. You can use any shared folder. In fact I have an updated training on an even simpler method. You can see that video here: ruclips.net/video/aKV5seZmiBs/видео.html
@@ExcelForFreelancers Oh ok thanks but we are restricted at work to having all our files in SharePoint so even though your app is great I wont be able to use it or its methods from within SharePoint.
What a great video well done!
I am planning to make an excel based database referring on this concept. It will be a log-in/log-out of borrowed equipment in different workstations. There will be 3 excel files namely Workstation1, Workstation2 and Main Database. The idea is to record the time of the equipment being borrowed and returned. But borrowed equipment in Workstation1 can be returned to Workstation2 and vice versa.
Hi, thanks for your comment. That should work very well as long as you have a central database to lookup the equipment. Also make sure each equipment has a unique ID so you can refer to it by this ID. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Thanks for the answer. Will it still be efficient if the lines/rows will reach 300k lines and files are stored in a local shared drive? Or how can it be done through google drive/Dropbox?
Excel has some limitations however you would have to try and check the speed to see if it's fast enough.
Hello Mr. Randy. Your videos so brilliant. Thank you for that. Nevertheless I have an issue with file: if I clear data from file CustData, I get error into cell B8 = #link!. After when I want to put new record, filled all cells and pressed 'Save button' i get #link! Into d13 instead number 1. How it fixed? Thanks
Hi it would be difficult to understand your issue unless we see your workbook screenshot and code, Can you please upload it in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 25,000 members who can help you. Thanks so much
Hello Randy, quick question the main dashboard has to be stored in computer of multiple users , or it is possible to have it on sharepoint a people can open it from one folder in sharepoint in the same time and store the custdata in another folder in sharepoint? Thank you
Hello Tomasko, a Dashboard should simply be a reporting tool in which data is brought into Excel. In my opinion everyone should have their own, duplicate Excel copy, and simply run their own dashboard reports, based on the same data. Now if that data changes, then the only the data is synced around machines. In this video I show you how to sync just the data: ruclips.net/video/3E6ExWYscHQ/видео.html
i hope that helps
Wow this is really amazing! By the way can I use my LAN storage rather than a cloud drive to share my database?
Hi and thanks so much. Yes you can use your LAN connection as long as all of the users have direct access to it and their own unique link to the folder. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thank you very much!
Amazing Randy
Thank you soo much
Love you.....
Thank you so very much, I really appreciate that. I have an updated video on Sharing & Sync here: ruclips.net/video/aKV5seZmiBs/видео.html Thank you for your Likes, Shares & Comments. It really helps.
All my thanks to you. Great job sir!!!
HI thanks so much. I look forward to bringing you new videos each and every week. Make sure you have downloaded the sample workbooks.
Ok
Thank you for another great tutorial - 5 star from me. Thus, just an extra features in this workbook since it has the ability to be used by multi-user, is it possible to have the user to view & edit only on their own data? Only user with ALL-ACCESS can view ALL data - like your previous tutorial on User Level Security
Hi and thanks so much. Yes it is certainly possible using a filter on Sheet Activate which will only filter based on the logged in user. I have an updated Share & Sync application and training here: ruclips.net/video/aKV5seZmiBs/видео.html
hi sir just a query... if you use conditional formatting , shapes and tones of differnt formatting options then it makes a file heavy... please advice me to know how you will manage to control the size and use the file without any lag.
Hi it really depends on your file. You can remove any formatting, and make sure that any formatting that is needed is only on used cells. You can see my video here on how to make Excel faster: ruclips.net/video/M71kllagKzw/видео.html
I think this system has a flaw. The problem is the delay in synchronizing systems like DropBox ....
Internal network will be more efficient. Even so I would recommend (briefly) a flag while someone is occupying the shared database, others in simultaneous update, should have to wait with a loop until the flag disappears.
The flag can be a txt file with a user name code in the file name, which is occupying the database. I do this.
Even so, in synchronization networks like DropBox, there may be failures.
What do you think about, Randy?
"Text translated by Google (Portuguese from Portugal to English)"
HI VIctor thanks so much for your comment. I have an updated Sharing & Sync training that takes care of all of these issues here: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
Excellent! Another tutorial packed full of usefulness. 👍🏻😊
Philip McDonnell Thanks so much Philip, much appreciated.
Great lesson, Randy.
I have got two simple questions -
1- when I turn on the pc, it will get some time to sync all Dropbox folders (which contains other heavy files), HOW to understand the computer is still syncing?
2- when two users (working at the same front desk but using two computers) insert the same individual in the DB AT APPROXIMATELY same time, how is possible to prevent the DB to contain twice the same information inserted separately by the two users?
Thanks again and regards roberto
HI Roberto, Thanks for your comment and I am glad you like the lesson. I am happy to answer your questions
1. The Dropbox sync folder (the one that you have designated for sharing) should just contain the small text file. Place your other larger files in other folders. At the beginning of the macro you can display a shape (or text in a cell) that says "Syncing. One moment please", then before the macro ends, hide this shape.
2. It is unlikely that two people at the exact same second are changing the exact same cell. For this reason the text names are based on the cell address. this avoids conflict. IN the event that more than one user changes the same cell, at the same second, Dropbox will make a choice (or show a conflicted file) when two files with the same name are used. Theoretically you can program to look for files with this conflict, then alert the user, however that would slow down the macro, and speed is an issue here.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thanks Randy. I will do it. As far as point 1 is regarding, actually I was asking if there is any way to understand (in Vba) that the DBFILE has not been synced (yet) to latest version. It happens when you turn on computer B early in the morning when last change were made by computer A late evening. Sometimes Dropbox takes 3/5 minutes before syncing all files changed in the meantime. And even if folders are different there is no way to ask Dropbox to sync folder with DBFILE with a sort of priority
Hi Roberto, one way would be to add a shape, based on cell text, that says "Last Sync on 3/29/2010 at 10:50 PM" along with a "Sync Now" button, this would both tell you when the last sync was done, and also allow a user to initiate the sync process. Also if the sync process is long (with tons of files) during the sync, you can have a "Syncing in Progress" message to help the user understand what is going on. (as far as priority of files that get synced, that is handled by dropbox so i am not sure how that order can be changed)
I hope this helps. I will probably do another lesson on this since the last "Global Workbook Sharing" video was part of a larger Employee Manager, I think some of the details got lost. I want to simplify this with just a single table and program it from scratch during a video. Do you think that would help?
Excel For Freelancers YES! Definitely it will help. I agree with your suggestions but the issue is a bit more subtle than that. Actually we Sono in un meeting. Non posso rispondere. Se urgente invia sms. Grazie a più tardi another property of DBFile which provides us with the information it is going to be updated through Dropbox synchronization. So we use the LastModified Property to know if proceed or not but BEFORE that we MUST know if the LastModified field is not going to change within few seconds..
EG:
Computer A turned off at 6 pm and Dbfile.lastmodified = 5.57 pm March 30th
Computer B and C still working up to 10 pm making several changes and finally Dbfile.lastmodified = 9.59 pm March 30th.
That’s all correct for Dropbox.
In the meantime, since March 30th 6pm, many other shared files are created or modified.
On Monday morning at 8am computer A turns on and user proceeds to make its own change to database and actually he will write to Dbfile as the computer had got no time (still) to actually update the shared Dbfile. Now we could solve the issue if another property of Dbfile tells us the Dbfile is under syncing process (we know that because the Dropbox icon is blue and not green...). I put the same question to Dropbox but still did not receive an answer.
Let me know if I could help in any way to make your newer lesson even more fantastic because let me say you are really GREAT! And clear!
PS. I switched to Fileformat 50, binary file, smaller size and quickest saving time. For huge DB it helps a lot (I am working with 3 files more than 10.000 lines each)
What you give us for free👏👏, Thank you.
I don't know if you can help, what if i want use Access as the DB now instead of excel. Is there a simpler way
Hi and thanks, I do have a simpler way and I share it with you in this video ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
@@ExcelForFreelancers Thank you, i loved this one.. Is it possible to use MS Access as the database & Excel as the front end
Yes it is possible however it would be a bit slower, You can test it out on your system. I hope this helps and thanks so much.
Hello, great video. Thank you for sharing. I am having a problem with the database file being deleted from the folder and a new one not being added back to the folder. Any suggestions on what I need to do?
Hi and thanks for your comment Nick. This was my first 'shot' as Sharing & Sync. I found even a better way in which only cell-by-cell changes are synced. You can see a complete training on that method here: ruclips.net/video/aKV5seZmiBs/видео.html
@@ExcelForFreelancers Hello, thanks for feedback. Can you apply the new method with this data entry form? I have 24 categories of data that I am collecting for each line item. I like the data entry form for the ease of entering the data and also for viewing the data 1 line item at a time in that data entry form instead of having to scroll through all 24 columns. My data entry form is all within one screen and does not require scrolling. Hopefully this makes sense. Thanks!
Hi Nick, you can use Data mapping for that. I have a few videos that cover that so please search my channel for "Data Mapping. I hope this helps and thanks.
@@ExcelForFreelancers I appreciate the feedback. I know you have provided me several different suggestions based on my requests. I have setup a file based on this original video. The only setback I have right now is sometimes the files takes some time to perform steps and I have issues with the master database file being deleted and a new one not being created. Trying to figure out the best way to combine these different methods into one concept. Look forward to your feedback. Is there any other way to communicate outside of commenting on here?
Hi Nick as mentioned above, its best not to share the entire database and only sync one sell at a time. This video will solve that solution for you: : ruclips.net/video/aKV5seZmiBs/видео.html
You can also add your questions in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 25,000 Members who would love to help you with this. Thanks so much.
You’re amazing. You just got a new subscriber 👍👍👍
Thanks so much Pap, I really appreciate that.
Excellent Video !
I've fully followed it but encountered on a small problem. Sometimes ADO does not copy data from recordset fully.
I've did it with two different PC but when on a second PC I opened database file in that moment even on my pc local database got updated. Otherwise it did not.
Could a reason be, because there were empty cells/rows in database file, so recordset did not recognize rows?
Also, could it be that it's a problem because under numbers I wrote text and versa so it had problems to recognize columns fully (it was just a test with some gibberish data, so I could check does it work properly)?
Hi and thanks so much. I have an updated video which may be a bit easier to share and sync your macro-enabled workbook here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
@@ExcelForFreelancers yeah I saw this also... I had similar idea before some time, but I did not think about one cell per time so it would be a complete mess with symbols and semicolons (like CSV...).
You're amazing and very talented and the most brilliant thing are your ideas, definitely !
Thank you for your time :)
Thank you so very much, I really appreciate that Dragic :)
Again a great lecture on Excel. I love the database connection facility. It works great. Does it mean other users need a OneDrive account? Or is it possible to share my file on OneDrive with others and they can save wherever they want it on their computer?
Hi thanks for your comment and glad you love the training. You would want to make sure that all users have the same shared folder (OneDrive, DropBox or Google Drive will all work) This is important because you want changes they made to be able to come to you as well.
Also i am making an other video on Global Workbook sharing this Tuesday (January 29th, 2019) So make sure you watch that, as I will show you a whole new way to sync your application with unlimited users around the world using a shared folder)
@@ExcelForFreelancers Thanks Randy, it works fine. I like to mention, that with one of your tips on security to hide the CustDb sheet so users can't change anything there, will cause a problem when syncing to the database. It will kill the DbFile (and no longer available on OneDrive), but an error will appear when copying the changed data. It can't find the CustDb sheet. You have to unhide the sheet first, sync and unhide it again, it seems.
Hi Bert, thanks so much for the suggestion and much appreciated it. Will make sure to undide, then hide within the code. Tomorrow (Tuesday 1/29/2019) I have a whole new way to Share and Sync your macro-enabled workbook with anyone in the world. So please don't miss it. I introduce an even better way to sync: ruclips.net/video/3E6ExWYscHQ/видео.html
Thanks for your amazing video. My database has cell interior colors and font colors. .CopyFromRecordset copies the values only. How can I copy cells' formats like colors?
Hi Iman, thanks for the comment. You can use a separate database to store cell details such as font, colors, borders, size, etc. Then this database can be synced and updated among users. I am actually teaching all of this with Invoice and Purchase Order templates in my Excel For Freelancers Mentorship Program. I hope you can join us. You can see the details here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Great content!! Why not use MS Access though? It has all those features built in. I also see a problem with capacity. Once you get 1 milion customers the "database" should be archived, cleaned
Hi and thanks for your comment. We don't use access because people around the world love and want to use Excel. We are not trying to build the best application possible, we are trying to build the best application possible using a platform that people already have and love using. This is a perfect solution for those small and medium sized business with less then 1 million records. (that already covers millions and millions of business around the world which is a sizable marketplace)
Thanks for the great question and feedback.
Mr Randy, excellent, efficiency at its best !!!!. I have just one concern that maybe sounds silly but I haven't wrapped my head how it fits in the code, probably I am missing some key concept, but here it ts.
Let's say I am working on my workbook and do changes, modify the data, but don't commit the changes right away, let';s say I step out for a coffee, now someone somewhere edited the file, making my version older, in fact they deleted the record that I was updating ( don't know it yet ), now I am back at my desk and press UPDATE, first the syncfromdb module will pull the newer data overwriting mine because my changed date is lower than the depository's file, does this scenario enforce the user to hit the update/delete button right away, or ...else ????.
Thank you for your awesome videos and kudos for your great work..
Hi Edward, thanks very much for the feedback and glad you love the videos. The video you are commenting on was the first video on Sharing Workbooks. I have actually made another one with some updated options and streamlined features here: ruclips.net/video/3E6ExWYscHQ/видео.html
Concerning your questions, in this new video, as soon as you Exit a cell after making a change, that change is then sent to every user in a small text file, Also, in this updated record, users can clear data from a row, but not delete the actual row, so if you make a change after they make a chance, your changes will take presence. (the newest changes always win). When you are programming this type of application, the only thing you want careful of is making changes to more than one cell at one time, which should not be done (no deleting rows/columns and no clearing out entire rows/column. You can use VBA to run For/Next loops to clear out a data record in a row.
The newer video will make things a bit clearer and it works very well. I hope this helps
I just love how you spoon feed us with these trainings. Just a question, can you use this program with OneDrive though? Because unlike Dropbox and Gsheet, Onedrive's sharedfolder doesn't hide the local filepath on other people's PC when shared. Therefore, the code you put in filepath will return an error on other people's PC when using OneDrive as your cloud storage. I am trying to find a solution on this issue and I hope you can help me with this. Thank you and God bless.
Hi and thanks. I have not tested this in OneDrive because I really did not like the restrictions put on. I am sure you can get your answer by posting your question in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 40,000 Members who would love to help you with this. Otherwise just use DropBox. Its free and its great. Thanks so much.
Hi Randy. Thanks for this valuable lesson. One question please. Will this work in a Microsoft Teams Environment?
I want to build a CRM and I am thinking of adding the front end spreadsheet as a tab in a Teams channel "CRM" and the backend workbook in the Sharepoint documents folder for that channel. I am guessing that this will enable my team users to update and add contacts in an online Multi User environment.
On another note, if you can point me to any of your workbooks (I just bought and downloaded the 120 workbooks) to get the user logged into Office365 while working in the Teams, I would appreciate it.
Thanks again and I will certainly be a follower while building my MS Teams site.
Hi Silvan, thanks very much for your purchase and comment. I am happy to help. This should work for any type of a shared 'folder' environment, and that means that a local folder on your users computer MUST also be shared with all other users (I am not sure if Microsoft Teams or Sharepoint provide a shared folder on a local drive) However DropBox, Google Drive, and Microsoft One drive, plus a host of other applications does provide that feature. Most of them free.
Here is also an updated video of Share & Sync that is a lot better: ruclips.net/video/aKV5seZmiBs/видео.html
Thanks for this awesome video. You are the best.
I just have one question.
I notice that on your program if there will be two user who will click new contact at the same time or almost the same time. They will have the same id no. Now when one of them click save. The first user who click save will be recorded on the database however if the second user click save the record will be overwritten on the first one. Is there a way to avoid this? Thanks
HI Bernard. Thanks so much for your comment. A new ID is not created until a contact is actually saved, so that helps for sure. as soon as it is saved, the Contact ID' is saved and placed in the shared folder. Theoretically there could be duplicates but with just a few dded lines of code to make the updates if ever there was a duplicate would solve this issue. So for example each time a new contact is saved, just a quick formula to see if there are any duplicates and make the update if needed. Almost any issue can be solved with just an added check or few lines of code. Make sure these small issues do not prevent you from creating your application. Also please feel free to post your questions in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel Thanks so much
Amazing video! Would you say that it is possible to apply that logic to a larger scale (80-90 people that have permission to make changes and a total of 40 columns) without suffering too much on response speed? Thank you very much for your help!!
HIU Merlin yes this should work just fine. You can use my updated training here as a guide: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers thank you :)
Great tutorial! Very detailed and excellently voiced, couldn’t ask for a better teacher to help me with the automation process for my little shop. But I found a sort of bug? Or is it a feature? Anyway whenever someone is saving a row of data on a sheet and then someone else is doing the same - they get overlapped and one data is totally lost and they need type it again. As I understand it, it couldn’t be fixed because whenever someone pushes create button for a new row of information, it “reserves” an ID number for it which then it uses to know on which row to put it and it couldn’t be done whenever two or three persons trying to do it at the same time, cause excel isn’t dynamically changeable system. Am I wrong?
HI thanks very much. I am glad you like the training. I have an updated version of the Share & Sync which solves this issue by only syncing one cell at a time. You can see that video right here: ruclips.net/video/aKV5seZmiBs/видео.html
Hi, love this tutorial and the concept! i'll subscribe. I have created a similar version, but the data i am tracking is dates, but when i refresh & sync the date format changes between UK and US date formats and sometimes deletes dates. All non date fields are fine...any ideas?
Hi Anne thanks so much. When you save dates to the table, save them in their number format, such as 43831 instead of 1/1/2020 This way they will work regardless of the date format used. Make sure to use this format in any filters as well. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers thanks so much, yes, it works fine when saving them in number format, the problem is displaying them so users can view or changes dates on the front end. I found a workaround although its not pretty :) i'll join the book group! thanks again!
You will always want to separate your front end (formatted dates) with your back end (unformatted dates) which users don't see) I cover this a great deal in my Mentorship course here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Would this be scalable for a Returns database - the table is 19 columns and currently 27,742 lines (527,098 cells) and growing by the end of 2021 I expect to have around 40,000 lines. There is conditional formatting that depending on what is entered into a specific cell the whole line is coloured accordingly.
A Data Entry form and search from another one of your videos would also be added. Yes, there are better programs, etc for this but Excel is what I have to work with.
Hi Matt, yes certainly it would, a few hundred thousand lines is acceptable in Excel. When it gets into the millions then you may want to look for another option for data storage. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thanks for the reply and I'll look into joining the Facebook group. I would prefer another method for data storage but unfortunately, it's not my decision and is highly unlikely to change. I can't upload the workbook or screenshots anywhere due to GDPR.
I am only interested in the shared workbook without using shared xl? Any options on getting just that single one?
Hi William, thanks so much. I have an updated training on this here: ruclips.net/video/aKV5seZmiBs/видео.html
Feel free to download the workbook using the links in the description.
I hope this helps and thanks so much.
I purchased the 100 wkbk nice 👍
Thank you so very much, I really appreciate that William :)
Hi great video just want to clear lets say i have 3 users will this allow them to edit or update the working file simultaneously?
HI Michael, yes that is correct. Each user actually uses different Excel files, but they all share the same synced back-end database files, so its far better than Excel's Shared Workbook feature in which users actually share the same file, which causes lots of issues.
Excel For Freelancers thank you got the logic now..
HI,
Only 1 question, You mentioned that people can access it from "all around the world" Can you please confirm how this is possible if the workbook sits on the local or a shared drive at work?
Thank you
Hi Imre, thanks for your comment and question. It can be shared all around the world when they have a shared folder such as DropBox, Google Drive or Microsoft One Drive. The workbooks themselves do not have to be in this folder however all users much have a single shared folder in in which the data moves back and forth. I have an updated video training on this here: ruclips.net/video/aKV5seZmiBs/видео.html
Hi I have one question here, let’s say two people are using this at a same time and they loaded the information at the same time without clicking on update will both the information gets loaded in the file ?
Yes they will, but use my newest sharing workbook training video here, this way the updates are automatic on every change: ruclips.net/video/aKV5seZmiBs/видео.html
Hi Mate , Is it possible to show who did change DB and of course save backup
of changes , Just in case other user deletes all . Great info Thanks
HI yes it is possible you can institute a Change Log. I have described how you can do that in these two videos:
Part 1: ruclips.net/video/6TnxPvLz_44/видео.html
Part 2: ruclips.net/video/fE15W72vnEE/видео.html
You can create users on your program. So they log in. Using a form on vba?
So all data they pit in. Their name gets copied into the next cell on table
Hi Randy. Great training as always! Quick question. Does it matter if the dates being compared in the Sync macros come from different time zones? If so, what modifications would be needed to the code?
HI and thanks so much Albert, I have an updated method that will work, regardless of time zone here:
ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
@@ExcelForFreelancers Hi Randy, thanks for this. I've watched it all. Using your updated method I can see you can sync single cell changes. What approach would you suggest to sync if say, the change made involved deleting an entire row from the data by a user in an entirely different time zone?
HI Albert thanks so much. You would never want to delete rows when using Share & Sync. You would just want to clear the contents of all but 1 column (leaving the 1st column with the ID in tact) You would clear the cells within the row by looping through the columns from the 2nd column to the last column, cell by cell. This way changes are only made to a single cell at a single time. I hope this helps and thanks so much.
Randy -- What is multiple users access the same record ?
Hi and thanks for your question. Multiple users, can access the same data using duplicate applications when the data between them is shared and synced using a shared folder such as Dropbox. I explain how to do this in this updated video here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Please, sir, I need your help. I have created a simple hostel management system with Microsoft Excel vba, but my mum said she wants to be monitoring the operations from her end because we don't stay together . How do I do this please?
Hi and thanks, so much. The easiest way is to have both you and her install the free Dropbox software. Then you have a shared folder between the both of you and she can see it just as well as you.
If you both want to make changes at the same time then to share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/f_1HsR9305I/видео.html
I hope this helps and thanks so much.
Please, good evening...
I have been following your studies since.
But don't you know whether you can help develop a pharmacy inventory management system with Excel VBA
Dear Mr. Randy, just amazing. But, the link to download the file is not working. I didn't receive any file. How do I proceed to get this one?
Hi Newton sorry about that. Did you check your spam folder? Also did you enter the correct name and email? (I will also check with my software and search for your name to see if i can find it) I want to make sure you always get the download that you requested. Just let me know on the above. Thank you.
Hi MR. RANDY, thanks for the video it was really helpful. Is it possible to get a video on how to make this exact type of work please.
Hi thanks very much Sadig, I do have a video on how to create an Excel based form from scratch here: ruclips.net/video/SnA3cct-2IM/видео.html
Hello Sir, Thank you very much for such a helpful video.
I have a question, so if my company have 100 employees, i have to create 100 copies of the Front-end file? And how's it gonna work then?
I hope I can receive your message soon. Have a nice day sir
Hi Thao, xin chao. You can simply create a single application and give it to each user. Each user uses their own application and only the data is shared using a dropbox file. Also make sure that each user has shared access to the same dropbox folder. I have a better and easier training for sharing and syncing here: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks. Cam on ban
@@ExcelForFreelancers Thank you sir
I used this and it is awesome. However we are coming across a problem where the file in the share drive gets deleted and does not write a new one and we are left with the sheet asking us where the database is and its been erased on the share drive. We cannot figure out why it is doing this. Any hints or ideas on how to fix this?
Hi please make sure to see my updated 'Sharing & Sync' video to solve those issues here: ruclips.net/video/aKV5seZmiBs/видео.html
Mr. Randy, Excelent!! I've tryied to run it and got it separetedly wonking perfectly, but a i miss how or when the syncFormDatabse is called. I didn't see any call for it. Can you help? Many thanks
Hi thanks you can sync from database at any point you want such as workbook open, sheet activation or at timed intervals. It is entirely up to you. Also make sure to see the updated Share & Sync video here: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers i'll try! thanks again!
@Excel For Freelancers another help please! After sync all format cells in the front end turn to Date. How I can fix it? thanks!
HI Pedro thanks that is a bit part of the Accounting application I am teaching in my Mentorship Program here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Hi Mr.Randy lovely tutorial,I made a similar model watching your video ,however when I run it ,it says "Complie error "
Sub or function not defined
And SetonKey is highlighted in blue.
Kindly assist
Hi make sure the line is "SendKeys" also please post your code in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 25,000 Members who would love to help you with this. Thanks so much.
Good morning sir!
I've just finished writing all the macros that you taught us and I had one problem which was everytime i pressed the SAVE or DELETE button, the excel screen went all white and delayed about 2 seconds. May you help me to solve this problem? I'll be thankful
Thank you and best wishes for you
Hi and thanks you can try turning off screen updating at the start of the macro and turning it on before the macro ends. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers It wwwwwwwwworked Sir. Love you
Hi Randy, thank tou for your tutorials. I have a problem with the cells which contain numbers when using copyfromrecordset. The value of the cell is simply not copied over and the cell is blank. Can you please advise?
Hi sure you can try a value to value copy for example Sheet1.Range("A1:B1").value = Sheet3.Range("A1:B1").value
You would just need to make sure that the # of cells in each range is exactly the same. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Excel For Freelancers thank you. I changed HDR=No & IMEX=1 and this worked
Ok great. I am very glad you got it working.
thank you for this helpful video
For sure, you are very welcome
Sir.. i had marco enbled application . After seeing ur video I also want to save data in backend using Dropbox... Can i send u my application so that i can save data in Dropbox folder
HI Vinay, thanks so much for your comment. I have an updated video which allows you to save data in a dropbox folder here: ruclips.net/video/aKV5seZmiBs/видео.html. Since I am developing courses I am unable to take on any additional work, however I would be happy to refer you to a competent developer who could handle this for you for a fair price. If so please email me your entire and detailed project scope here: Randy@ExcelForFreelancers.com
Thank you for your Likes, Shares & Comments. It really helps.
hiii, it's amazing .... i need a video that you design this worksheet , thanks
Hi and thanks, this may help, you. To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.html
Hi Randy,
Still enjoying this project but about to make a big change since I noticed that some of the fields in the database I imported have mixed data types and that ADODB sometimes fails to bring in field data with mixed data types. I played around with the IMEX=0, IMEX=1 in the connection string with mixed results so I thought, why not use the External File refresh for the SyncFromDatabase. First impression is that it might be a tad slower but all the data comes in especially since I was able to set the Data Types in the query
The SyncFromDatabase macro was simplified as below:
Sub SyncFromDatabase()
If blDebug Then Debug.Print Format(Time, "hh:mm:ss") & " Sub: SyncFromDatabase", "Events = " & Application.EnableEvents, "Calc = " & Application.Calculation, "ScreenUpd = " & Application.ScreenUpdating
' Replaces the StaffDB sheet contents with data from the Main shared Database
LastLocalChange = Sheets("Staff").Range("Last_Local_Change").Value
'Check Database file is newer than the our last update
If FileDateTime(Range("DBFile")) > LastLocalChange Then 'Database Change was made, update Local Database
Sheets("StaffDB").Cells(1, 1).ListObject.TableObject.Refresh
End If
DoStaffDBFormulas 'Formulas not brought over
Exit Sub
FileMissing:
MsgBox "Please browse for the database file"
BrowseForFile
End Sub
I have formulas that calculate age, length of employment, payrate, etc in the main database so I automated putting the formulas in the refreshed database, the Data Entry part of the form and the temp table on the main form.
In your SyncToDatabase macro, I made a mod when I realized that if I have the shared database open for maintenance, the Kill(DbFile) will fail and the error handler will just ask you to browse for the file. I forgot I had it open so got stuck in a loop. Now the error message will give me a hint...
On Error GoTo FileMissing
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(DbFile)
If objFile.DateLastModified < LastLocalChange Then 'Local Change was made, update Database
Kill (DbFile) 'Delete the current database version
ThisWorkbook.Sheets("StaffDB").Copy
ActiveWorkbook.SaveAs DbFile, FileFormat:=51
ActiveWorkbook.Close False
End If
Set objFSO = Nothing
Exit Sub
FileMissing:
Set objFSO = Nothing
msg = "The file: " & DbFile & vbCrLf & "is either missing or inuse..."
msg = msg & vbCrLf & "Please close the file or select another file"
BrowseForFile
End Sub
Hi and thanks so much. That is a nice update. I have an updated version of this that focuses on a cell-by-cell change which does not risk removing and replacing the database. You may like this method better. ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
I am respectfully thank you. You help me get a job with higher salary
Thanks so much for your comment. By learning these skills in Excel, many companies would be happy to pay you more. Investing in yourself and your learning will certainly help you reach a higher salary in the near future.
Thanks a lot sir , i have a question actually , i had designed an excel work book to save marks of students ,my file hold macro and VBA code , is it possible for me to share this file with many teachers online and make them insert their marks ??
Hi yes you can. You can follow this 'Share & Sync' training to do that: ruclips.net/video/aKV5seZmiBs/видео.html
Thanks for your Likes, Shares, & Comments. It really helps Yousef.
Great job man. Can you please teĺl if I need additional 5-6 columns in my database that needs to be maped, is that too much work, and which part of macro I should change in order to work correctly?
Hi thanks very much for your comment. There should be no problem making those changes to add to the code both in the macros and on-sheet code to customize this for your specific application. You can update the ranges based on your specifications. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Hi Boja, I've completed your request to add 7 more columns. can you send me a email to forward the zip file with content.Please send towards R.Haman@ziggo.nlBr. Rob
@@robhaman4424 I'm also trying to add additional columns but it's not working, when i refresh the file it shows the REF error. how do you go about adding additional columns and data entry box?
@@Sketchy.Panda13 , please send your file towardsmy email: r.haman@ziggo.nl , so I can investigate the issue you're talking about.
Hello Randy, I would like to ask how can I use it with sharing file. If IU share the CustDATA throught one drive , I send the link to receiver , but it always open the Cust data on web , and I am not able to identify the source on second computer through browser? do you have any idea how it should be shared?
Hi Tomasko, Thanks for your comment. When you share this file using one drive, you want to make sure that the person you are sharing it with has the One Drive software installed on their computer AND that one folder in their One Drive is shared with you. This way when you save your file they Automatically get it on their computer. You will not have to share any link at all since any changes in your shared folder will Automatically come to their computer. (When you share a link to a file this will go to the website which is not needed)
Man I'm such a fan of yours
Thank you very much. I do appreciate that Marco
Nice tutorial, should help in a current project. However the links to download the workbooks are not working.
HI Thanks so much. I did just check both links and they seem to be working. The Email link goes to my website in which you can enter your email and name, the 2nd link goes directly to Facebook Messenger to get the download. (Make sure you download on your computer and not your phone)
Please let me know if you have any other questions. Also feel free to join our Excel For Freelancers Facebook group here; bit.ly/groupexcel
Hi I tried this and working fine. Really obliged for such amazing training
Just one thing when I try to password protect the main database file it is showing error on SyncFromDatabase macro as
You have a few choices when it comes to protection. You can unprotect the cells of the database sheet and just hide the sheet, or you can also unprotect, then reprotect during the macro or you can add xlUserInterfaceOnly which allows VBA to make changes but not the user. (any of these should solve the issue.) Thanks so much for following.
Hi I have watched your video mentioned that the back end database can be stored in onedrive for sharing, Do you have video that teach how to give access in onedrive to other users that doesnt share the same local drive with me? thanks!
Hi thanks for your comment. i have used DropBox, but OneDrive should work just fine. Just right click the folder and select the One Drive Sharing option to add the email address of the person you want to share with. Thanks very much.
@@ExcelForFreelancers Hi I am so happy to hear from you. I noticed when i right click on the folder to give access to someone, it only allows me to share with someone with same network access with me. Could you mind to advise if this macro could be changed to connect to an excel that is saved in web version onedrive/sharepoint business instead in a local drive folder?
Hi would just use DropBox it is so much easier and free, then you can add anyone in the world as long as they have a Free Dropbox account as well. Thanks again.
this was incredibly helpful,
my concern is what if the database was updated within same minute, will it notice the "seconds" difference as well?
HI thanks for your message, Using the "Now()" formula and adding seconds to the time formula would not be a problem as well, so that will remove any doubt as to when the file was updated, even without seconds. I will be adding this 'Shared Workbook' feature, with seconds, to our Employee Manager Application in which we are designing in our current series, so make sure you watch that one as there are multiple databases that we will be working with. Thanks again for your comment.
@@ExcelForFreelancers so how do you share this database xls file? Via dropbox or inbuilt excel sharing option? As im in desperate need of centralising my database for my program which is used by my friend in india and im in uk.
HI thanks for your comment. The workbook is shared via DropBox (others sharing applications may also work but Dropbox is the best in my opinion) I explained it all in this video on exactly how to share your workbook with anyone in the world, so just download the sample, follow the video and then you have your exact solution.
@@ExcelForFreelancers okay. I apologise, i must have missed it on the video. I skip a lot of times and miss oit important info :/ bad habits.
Hi, Randy! Very interesting way of sharing indeed. I have a question though regarding the local time. Supposing the DbFile is stored in country X, GMT - 2. The front-end file is shared among different countries, A, B, C, with GMT - 4, GMT + 2 and GMT + 4 respectively. I don't see a way of comparing two "local time" variables coming from two countries with different GMT-s. Wouldn't it be better to change the "local time" variable from every country according to GMT + 0? This way we would be sure that changes in the database occur every single time. What is your point of view? Thank you!
Hi Daniel, Thanks very much, and yes that makes perfect sense when sharing an application across time zones. However in my updated 'Share & Sync' training, time-zones are automated since I no longer need to share the entire database, i simply share changes cells in small text files. Feel free to check it out here: ruclips.net/video/aKV5seZmiBs/видео.html
Hi Randy, I tried this in my computer but problem is syncfromdatabase is not working so when one user put some changes , these changes donot appear in other users CustDb file when pressing refresh. Can you please help where is thing going wrong.thanks in advance
Hi Hima thanks for your comment. I have an updated Share & Sync video I think you will really enjoy and its a lot easier. Please try this one here: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
It's a great learning, helped me to connect to database without opening the file. Howevere just wanted to check I can have a password protected database, if yes? then how would I ensure that Syncfrom and Syncto macro has that password, any suggestion.
Hi Adil thanks so much for your comment. I have an updated training which will only share a single cell & data at a time. This will allow you to securely transfer data without a password. Please see this updated training on Sharing & Sync: ruclips.net/video/aKV5seZmiBs/видео.html
Pl share this video step by step wise from a scratch. It is very useful video. Thanks in advance. Hope you will upload this step by step video soon, because I tried to mirror this video but unsuccessful.
Hi Thanks so much. I have a more updated version of this which is step by step from scratch right here: ruclips.net/video/aKV5seZmiBs/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
Can i use this with one drive synced folder? The problem i have is when a user makes an update on the front end form is the delay of the onedrive sync, it may end up replacing data coming from other users.
Hi and thanks. If you find that there is too much of a delay, just use DropBox, which I have found to be very fast. You can test all of them and see which one is the fastest for you. Also please make sure you watch an updated version of this here: ruclips.net/video/aKV5seZmiBs/видео.html