How to Create a MULTI-USER, macro enabled, Excel Workbook WITHOUT Using 'Share Workbook'

No video

How to Create a MULTI-USER, macro enabled, Excel Workbook WITHOUT Using 'Share Workbook'

Рет қаралды 229,134

Күн бұрын

🔥 GET THIS + 350 OF MY BEST DONE-FOR-YOU TEMPLATES HERE ► rebrand.ly/350...
📚 GET A DISCOUNTED DEVELOPERS LIBRARY HERE ► rebrand.ly/UDV...
🤖BRING AI INTO EXCEL WITH THIS INCREDIBLE ADD-IN ► rebrand.ly/AIT...
👉CREATE & SELL EXCEL BASED SOFTWARE IN MY MENTORSHIP PROGRAM ► rebrand.ly/Men...
▶️ GET ACCESS TO OVER 100 UPDATED TRAININGS, WORKBOOKS & PDF CODEBOOKS: rebrand.ly/Pat...
👨‍💼BECOME AN EXPERT AT DASHBOARDS ► rebrand.ly/Exc...
📣START LEARNING VBA HERE ► rebrand.ly/VBA...
🏝BECOME A SUCCESSFUL FREELANCER ► rebrand.ly/Fre...
⭐️JOIN KZfaq MEMBERS FOR UNRELEASED TRAININGS ► rebrand.ly/YTM...
In this week’s training, I will show you how to create a MULTI-USER Excel Workbook WITHOUT Using Share Workbook.
⬇DOWNLOAD THIS WEEKS WORKBOOK FREE:
📩 USING YOUR EMAIL: bit.ly/SharedWk...
🤑 START EARNING BIG $$$ WITH MY AFFILIATE PROGRAM 👉 rebrand.ly/Aff...
EXCEL COURSES:
►FREELANCER ACADEMY: rebrand.ly/Fre...
►MENTORSHIP PROGRAM: rebrand.ly/Men...
►DASHBOARD MASTERCLASS: rebrand.ly/Exc...
►BEGINNERS VBA COURSE: rebrand.ly/VBA...
►WEB AUTOMATION COURSE WITH EXCEL VBA: rebrand.ly/Web...
EXCEL PRODUCTS:
►Turbocharge Your App Development: rebrand.ly/UDV...
►Get 350 Templates In 1 Zip File: rebrand.ly/350...
►Get ChatGPT Into Excel With This Addin: rebrand.ly/AIT...
►The Ultimate Excel Resource Guide: rebrand.ly/UER...
►Get Your Actual Employee Hourly Cost: rebrand.ly/LBC...
►1000 Incredible Freelancing Resources: rebrand.ly/UFR...
►Secure Your Excel Applications From Theft: rebrand.ly/XLS...
🧐Find the Best Excel Products & Courses Here: rebrand.ly/Exc...
TIMESTAMPS:
0:00 - Introduction
0:24 - Overview
19:38 - Activate Worksheet
20:30 - Selection Change Event
22:44 - New Contact
25:22 - Save and Update Contact
38:17 - Cancel New Contact
38:53 - Delete Contact
40:04 - Load Contact
43:24 - Refresh Contact Table
47:27 - Sync to Database
51:27 - Sync from Database
JOIN OUR COMMUNITY:
►Facebook Group: rebrand.ly/EFF...
►Facebook Fan Page: rebrand.ly/EFF...
Follow me on my Social Media Accounts:
🤩TikTok: / excel4freelancers
🐦Twitter: / excel4freelance
🌏Website: www.ExcelForFr...
🔗LinkedIn: / excelforfreelancers
👤Facebook: / excelforfreelancers
📸 Instagram: / excelforfreelancers
🎥Rumble: rumble.com/c/E...
✉Telegram: t.me/ExcelForF...
About Us:
I help Microsoft Excel enthusiasts turn their passion into profits so they can earn passive income using their skills without having to trade time for money.
I love making these videos for you each and every week. A great way of supporting Excel For Freelancers to keep this training free each and every week is by supporting us through one of the amazing products offered that will help you skyrocket your Excel Skills and reach your dreams.
Get Alerted IMMEDIATELY with new Free Training Videos & Workbook by subscribing here:
👉 / excelforfreelancers
New Training Video Uploaded every Tuesday!
Thank you very much for your continued support,
Randy Austin
#exceltraining #workbooksharing #multiuserworkbook #macroenabledworkbook

Пікірлер: 526

@ExcelForFreelancers 3 ай бұрын

🔥Download 350 Of The Worlds Best Templates Here ► rebrand.ly/350Wkbks_YTPinnedCom

@bartfonteyne9237 4 жыл бұрын

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.

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@TyroneHillsmbw 5 жыл бұрын

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

@ExcelForFreelancers 5 жыл бұрын

Thanks so much Tyrone, I really do appreciate that and thanks so much for your kind words.

@YvesAustin 4 жыл бұрын

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!

@ExcelForFreelancers 4 жыл бұрын

Thank you so much Yves, I really appreciate that. Make sure you check out a newer version of the 'Share & Sync' video right here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@johnywhy4679 2 жыл бұрын

You could simplify with a single, read-only front-end data-entry excel file. All users would open that same file in read-only mode. Then you would be able to upgrade the front-end features, and all users would immediately get the new features. The entry front-end doesn't need to be writeable, because it saves data to the backend file. Only the backend file has to be writeable.

@ExcelForFreelancers 2 жыл бұрын

Hi Johny, thanks so much. That is pretty much what i have done in the updates Share and Sync video here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@markhuang368 Жыл бұрын

What a genius approach of non-conflict data syncing. Thank you so much.

@ExcelForFreelancers Жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@samnangchhing3333 6 жыл бұрын

Amazing ! That's it. I try to find this video for a long time. Thank Mr Randy for sharing this video. You're amazing.

@ExcelForFreelancers 6 жыл бұрын

You are welcome Samnang, I am quite happy to create these for you.

@gilgingras5599 5 жыл бұрын

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.

@ExcelForFreelancers 5 жыл бұрын

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.

@gilgingras5599 5 жыл бұрын

@@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.

@ExcelForFreelancers 5 жыл бұрын

Wow that is amazing. I do remember Lotus 123. That is where it all started. Thanks so much for sharing.

@ahbhunt 6 жыл бұрын

Your videos's are excellent and you make your coding examples easy to follow. thanks so very much for sharing your knowledge

@ExcelForFreelancers 6 жыл бұрын

Thanks so much I really appreciate that Alan. I look forward to making new and interesting videos each and every week.

@philipmcdonnell7168 6 жыл бұрын

Excellent! Another tutorial packed full of usefulness. 👍🏻😊

@ExcelForFreelancers 6 жыл бұрын

Philip McDonnell Thanks so much Philip, much appreciated.

@WarrenGuan 2 жыл бұрын

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?

@ExcelForFreelancers 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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@WarrenGuan 2 жыл бұрын

@@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?

@caglaraksoy8408 Жыл бұрын

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?

@ExcelForFreelancers Жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@tigrantt 4 жыл бұрын

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.

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.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.

@arnoldp6291 Жыл бұрын

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.

@ExcelForFreelancers Жыл бұрын

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

@Bigns-bi 6 жыл бұрын

All my thanks to you. Great job sir!!!

@ExcelForFreelancers 6 жыл бұрын

HI thanks so much. I look forward to bringing you new videos each and every week. Make sure you have downloaded the sample workbooks.

@Bigns-bi 6 жыл бұрын

@papnyanamah2748 5 жыл бұрын

You’re amazing. You just got a new subscriber 👍👍👍

@ExcelForFreelancers 5 жыл бұрын

Thanks so much Pap, I really appreciate that.

@marcogouveia4158 4 жыл бұрын

Man I'm such a fan of yours

@ExcelForFreelancers 4 жыл бұрын

Thank you very much. I do appreciate that Marco

@christopherkagy5319 4 жыл бұрын

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?

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@zarazulkifli9007 2 жыл бұрын

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!

@ExcelForFreelancers 2 жыл бұрын

Hi and thanks I have an updated video which may help you here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@mintmo 4 жыл бұрын

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!

@ExcelForFreelancers 4 жыл бұрын

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

@xaviersolerpares 4 жыл бұрын

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!!

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.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.

@appmanager8851 2 жыл бұрын

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

@ExcelForFreelancers 2 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@ManiSms-fr2jg 2 ай бұрын

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.

@ExcelForFreelancers Ай бұрын

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.

@venusflytrap779 3 жыл бұрын

Amazing Randy Thank you soo much Love you.....

@ExcelForFreelancers 3 жыл бұрын

Thank you so very much, I really appreciate that. I have an updated video on Sharing & Sync here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@tudormihaizamfirescu427 2 жыл бұрын

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!

@ExcelForFreelancers 2 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@cassiechristensen4650 2 жыл бұрын

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!

@ExcelForFreelancers 2 жыл бұрын

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

@TheVojta24 5 жыл бұрын

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.

@ExcelForFreelancers 5 жыл бұрын

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

@T_h_e_Best 3 жыл бұрын

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

@ExcelForFreelancers 3 жыл бұрын

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 kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@ariespeugenio1982 3 жыл бұрын

thank you for this helpful video

@ExcelForFreelancers 3 жыл бұрын

For sure, you are very welcome

@kristianpahlmark7992 4 жыл бұрын

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.

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@charlesdawnbeltran9603 6 жыл бұрын

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.

@ExcelForFreelancers 6 жыл бұрын

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

@charlesdawnbeltran9603 6 жыл бұрын

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?

@ExcelForFreelancers 6 жыл бұрын

Excel has some limitations however you would have to try and check the speed to see if it's fast enough.

@mukeshkadkol4759 4 жыл бұрын

One more masterpiece! Have a suggestion randy, videos with min vba's and more explanation on the subject would be more helpful. Thanks again

@ExcelForFreelancers 4 жыл бұрын

Thanks for the idea and I am happy you enjoyed it. Make sure you catch the updated version of Sharing & Sync right here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@diemdao3628 2 жыл бұрын

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.

@ExcelForFreelancers 2 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@JeffersonAMontoya 5 жыл бұрын

Genial la solución profesor 👍

@ExcelForFreelancers 5 жыл бұрын

Muchas gracias. Me alegro de que te haya gustado

@jacobnorman8936 2 жыл бұрын

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.

@ExcelForFreelancers 2 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@oldick 3 жыл бұрын

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!

@oldick 3 жыл бұрын

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.

@ExcelForFreelancers 3 жыл бұрын

Great question, firstly make sure you have seen this updated Sharing & Sync Video: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.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.

@mimeenmimineur7013 Жыл бұрын

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 ?

@mimeenmimineur7013 Жыл бұрын

And maybe an answer to my question, but to which extend is this version better as you said in a comment below ? kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@ExcelForFreelancers Жыл бұрын

HI and thanks for your question. Please watch my updated Share & Sync which is more optimized here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@uniQue_XL 6 жыл бұрын

Randy >> Awesome idea! by the way....In oder to pull the data into the active workbook you can use Power Query as well, with no VBA code at all.

@adewaleoke84 6 жыл бұрын

Please can you teach me how to do this? I mean pulling data from different worksheets/workbooks into one worksheet.

@ExcelForFreelancers 6 жыл бұрын

HI thanks so much glad you love it. I wanted to create a solution that did not require any add-ins. Power Query is great but requires users to install it. The solution in the video above can be used with core excel from 2007 and above without add-ins. I hope this helps.

@ExcelForFreelancers 6 жыл бұрын

Hi thanks for your comment. The video above will teach you exactly how to pull data from different worksheets & different workbooks.

@ricos1497 6 жыл бұрын

Indeed, it'd be interesting to see the speed of update vs Power Query refresh. The good thing about PQ would be its handling of large amounts of data if the database sheet got quite large and the ability to quickly amend your process if changing to a DB model instead. That said, you still need a mechanism to update the original DB workbook. One of the companies I work with uses excel 2010 and doesn't allow the PQ add-in so this might be of some use to them anyway, so it's definitely quite useful. I especially like the use of the recordset when querying Excel. Good to know.

@leomacenauer3433 5 жыл бұрын

WoW! - I have been searching for this half day long! Thanks a lot for this video! It does not look like, but its going to make our shirts better here in Prague :-)

@ExcelForFreelancers 5 жыл бұрын

HI Leo, thanks so much. I am glad you like it. Please also see an updated version of this here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@FreyFamily-pr1gs Жыл бұрын

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

@ExcelForFreelancers Жыл бұрын

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: kzfaq.info/get/bejne/nMVhe9aDnpmUZX0.html I also cover proper workbook protection in this video: kzfaq.info/get/bejne/nL1irMllnMilgWw.html I hope this helps and thanks so much.

@paulomarques9746 4 жыл бұрын

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?

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@XuanNguyen-sy6jl 4 жыл бұрын

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....

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.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.

@T_h_e_Best 4 жыл бұрын

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?

@ExcelForFreelancers 4 жыл бұрын

Hi thanks so much, I have an updated video with a better technique on share and sync right here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@T_h_e_Best 4 жыл бұрын

@@ExcelForFreelancers thanks for video, it's great too , but it is not what I need to know...

@ExcelForFreelancers 4 жыл бұрын

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

@wandererdark4269 4 жыл бұрын

Many thank sir, your videos teach me alot!

@ExcelForFreelancers 4 жыл бұрын

That is really great. I am so glad you like it. I have an updated video on Sharing and Sync which I think you will like a lot here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@CharlesBerg101 3 жыл бұрын

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'?

@ExcelForFreelancers 3 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@CharlesBerg101 3 жыл бұрын

@@ExcelForFreelancers thank you for the response!

@T_h_e_Best 4 жыл бұрын

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

@ExcelForFreelancers 4 жыл бұрын

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

@mundz004 3 жыл бұрын

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

@ExcelForFreelancers 3 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@saharbarakat7214 Жыл бұрын

hiii, it's amazing .... i need a video that you design this worksheet , thanks

@ExcelForFreelancers Жыл бұрын

Hi and thanks, this may help, you. To share and sync your macro-enabled Excel workbook, please watch this training: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@kytan13 5 жыл бұрын

Thanks, this just what i was looking for, for a long time.

@ExcelForFreelancers 5 жыл бұрын

That is great Christian. I am going to expand on this sharing for our current Employee Manager series where we share multiple databases within a single application, so stay tuned.

@naja1214 3 жыл бұрын

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.

@ExcelForFreelancers 3 жыл бұрын

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

@murtazabohra6601 5 жыл бұрын

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.

@ExcelForFreelancers 5 жыл бұрын

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: kzfaq.info/get/bejne/g51hns-dxs2vqqs.html

@keivariety7501 3 жыл бұрын

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.

@ExcelForFreelancers 3 жыл бұрын

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.

@affindi77 3 жыл бұрын

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

@ExcelForFreelancers 3 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@thaof1898 4 жыл бұрын

I am respectfully thank you. You help me get a job with higher salary

@ExcelForFreelancers 4 жыл бұрын

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.

@jmtcesnz 3 жыл бұрын

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

@ExcelForFreelancers 3 жыл бұрын

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. kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@JarloxRiv Жыл бұрын

Thank you Randy for your support... I have a question: How connect the database file if this DB is password open secured? thanks!!

@ExcelForFreelancers Жыл бұрын

Hi and thanks for your comment and question. I am not sure what you mean by 'open secured' Can you please explain

@JarloxRiv Жыл бұрын

@@ExcelForFreelancers Hello, I mean How to Connect to a Open password protected workbook. Thank you!

@ExcelForFreelancers Жыл бұрын

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.

@JarloxRiv Жыл бұрын

@@ExcelForFreelancers Give me the e-mail of the owner, please

@FixMeOfficial 4 жыл бұрын

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!

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@merlinthiemann433 2 жыл бұрын

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

@ExcelForFreelancers 2 жыл бұрын

Hi and thanks so much for your comment Merlin. I have an updated solution that should handle that issue here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@gexcel 4 жыл бұрын

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)"

@ExcelForFreelancers 4 жыл бұрын

HI VIctor thanks so much for your comment. I have an updated Sharing & Sync training that takes care of all of these issues here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@fajarnur34 4 жыл бұрын

thank you so much, your dedication is incredible

@ExcelForFreelancers 4 жыл бұрын

Thanks so much Fajar, Please also see this updated Sharing & Sync video, I think you will like it even more: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@T_h_e_Best 4 жыл бұрын

Hello experts: I have a question: how this sharing design Wbook combine with horizontal tabs? If i should have as minimum 6 tabs( each tabs belongs to some department: design, production, logistic.....) with special design info of each of tabs, but view of data must be like on video of "sharing Wbook" and transform ed accordingly based on active tab.

@ExcelForFreelancers 4 жыл бұрын

Hi thanks for your question. When it comes to tab design, this should be on a front-end form, which does not have to be shared. All of the source data, should be on a hidden database, which would be shared and sync with other users. If you want to learn more on how to do this, I am teaching it as I build an entire accounting application in our Mentorship program. More details here: bit.ly/MentorshipProgram_YTCom

@robhaman4424 6 жыл бұрын

Error in cursor movement (content area) Hi Randy, It has been a plesure to follow you in the explanation of sharing files with Excel. It has been noticed that when you use the cursors in the content area, an error occurs. Error 13, types do not match! Caused by VTabOrder is empty. I've created an adjustment so this doesn't appear any more and you're still able to use the cursor on a row level. Perhaps this is something you can add to your file. I'm curious on your response. vbacode: Sub UpOrDownArrow(Optional iDirection As Integer = xlUp) Dim vTabOrder As Variant Dim lRowClosest As Long, lRowTest As Long Dim i As Long, iSign As Integer Dim sActiveCol As String Dim bFound As Boolean iSign = IIf(iDirection = xlDown, 1, -1) If ActiveCell.Row < 12 Then 'roha 07-08-2018: to eliminate cursor move error '--get the tab order from shared function vTabOrder = GetTabOrder '--find TabCells in same column as ActiveCell in iDirection '-- rTest will include ActiveCell sActiveCol = GetColLtr(ActiveCell.Address(0, 0)) 'iSign = IIf(iDirection = xlDown, -1, 1) lRowClosest = IIf(iDirection = xlDown, Rows.Count + 1, 0) For i = LBound(vTabOrder) To UBound(vTabOrder) If GetColLtr(CStr(vTabOrder(i))) = sActiveCol Then lRowTest = Range(CStr(vTabOrder(i))).Row '--find closest cell to ActiveCell in rTest If iSign * lRowTest > iSign * lRowClosest And _ iSign * lRowTest < iSign * ActiveCell.Row Then '--at least one cell in iDirection of same columnn bFound = True lRowClosest = lRowTest End If End If Next i If bFound Then Application.EnableEvents = False Cells(lRowClosest, ActiveCell.Column).Select Application.EnableEvents = True End If Else 'adjustment roha (10-08-2018) to enable cursor moves in content With ActiveCell Application.ScreenUpdating = False If .Row > 12 Then Cells(.Row + iSign, .Column).Select Else Cells(13, .Column).Select End If Application.ScreenUpdating = True End With End If End Sub

@ExcelForFreelancers 6 жыл бұрын

Hi thanks so much for the code and message. I see the Up/Down arrow keys move to the rows properly, and without a bug. I have already updated my workbook so anyone who downloads this application from this point on will get the fix. Very Much appreciated. Also your expertise would greatly be appreciated in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel Please feel free to join

@assitv8261 2 жыл бұрын

Mr.Randy, I want to have the video you create this workbook exactly the procedure you make step by step thanks

@ExcelForFreelancers 2 жыл бұрын

HI and thanks you can download the workbook and also watch this updated training: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@arnoldroobhan3229 4 жыл бұрын

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

@ExcelForFreelancers 4 жыл бұрын

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.

@hamdee1412 5 жыл бұрын

Awesome video, thanks for sharing

@ExcelForFreelancers 5 жыл бұрын

For sure, you are very welcome I am glad you like it. (next week I have a new one on Global Sharing with our Employee Manager so Please watch that on Tuesday)

@sadiqbetto8798 4 жыл бұрын

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.

@ExcelForFreelancers 4 жыл бұрын

Hi thanks very much Sadig, I do have a video on how to create an Excel based form from scratch here: kzfaq.info/get/bejne/idRxZsaU2ZOWeYE.html

@AiaalRomanov 4 жыл бұрын

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?

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@tatianaburduja 5 жыл бұрын

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?

@ExcelForFreelancers 5 жыл бұрын

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

@tatianaburduja 5 жыл бұрын

Excel For Freelancers thank you. I changed HDR=No & IMEX=1 and this worked

@ExcelForFreelancers 5 жыл бұрын

Ok great. I am very glad you got it working.

@tomaskozvalcak3779 5 жыл бұрын

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?

@ExcelForFreelancers 5 жыл бұрын

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)

@himajoshi7419 4 жыл бұрын

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.

@ExcelForFreelancers 4 жыл бұрын

Hi Thanks so much. I have a more updated version of this which is step by step from scratch right here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@tomaskozvalcak3779 5 жыл бұрын

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

@ExcelForFreelancers 5 жыл бұрын

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: kzfaq.info/get/bejne/aatmeNuIvtnHeIU.html i hope that helps

@vindigo52 5 жыл бұрын

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?

@ExcelForFreelancers 5 жыл бұрын

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)

@vindigo52 5 жыл бұрын

@@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.

@ExcelForFreelancers 5 жыл бұрын

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: kzfaq.info/get/bejne/aatmeNuIvtnHeIU.html

@himajoshi7419 4 жыл бұрын

Pl help . I am having debug for set objfile=objFSO.GetFile(DbFile) in yellow highlight. Kind request to help

@ExcelForFreelancers 4 жыл бұрын

Hi thanks please check this video out on share and sync which is more helpful; kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@GauthamKrishnaChannel 4 жыл бұрын

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?

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@CletusBewaale 11 күн бұрын

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?

@ExcelForFreelancers 11 күн бұрын

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: kzfaq.info/get/bejne/nMVhe9aDnpmUZX0.html I hope this helps and thanks so much.

@chuckhamdan1283 6 жыл бұрын

Very smart approach!

@ExcelForFreelancers 6 жыл бұрын

Thank you very much.

@matthoyle951 3 жыл бұрын

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.

@ExcelForFreelancers 3 жыл бұрын

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

@matthoyle951 3 жыл бұрын

@@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.

@johnywhy4679 2 жыл бұрын

13:01 Confusing to use the word "share" here, because you just finished telling us the problems with Excel Sharing. I THINK you meant FILE sharing. It would be helpful to say.

@ExcelForFreelancers 2 жыл бұрын

Hi Johny thanks for your comment. The file itself is not shared, Each user has their own file. Only the data is shared and synced between users. You can find this updated video of that here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thanks again

@mdsaiful6294 2 жыл бұрын

Nice video good 👍

@ExcelForFreelancers 2 жыл бұрын

Thank you so very much, I really appreciate that Saiful

@himajoshi7419 4 жыл бұрын

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

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@imrepasztor3375 4 жыл бұрын

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

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@ahaha731 4 жыл бұрын

I love you!!!

@ExcelForFreelancers 4 жыл бұрын

Thank you so much Ji :)

@alyanibaloch4089 4 жыл бұрын

Amazing... Superb..

@ExcelForFreelancers 4 жыл бұрын

Thank you so much. I have an updated version of Sync and sharing that is even better, you can check it out here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@Duramaaxxx 4 жыл бұрын

Is it possible to use the recordset method to transfer data to a target closed workbook? Or can you only retrieve data?

@ExcelForFreelancers 4 жыл бұрын

Hi Tim its best for information to be 'pulled' into an open workbook as opposed to pushing into a closed workbook which is not possible as far as I am aware. Please see this updated Share & Sync training for an even easier method: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@Popflyer123 5 жыл бұрын

Thank you! Question about syncing from DB: can you please show me example of selecting a range from the DB rather than the whole sheet? Thanks again

@ExcelForFreelancers 5 жыл бұрын

Hi thanks very much for your comment. I do not currently have any samples for a specific range to sync since I have primarily been focused on syncing the entire database. There are instances in other training where I have used a filtered range to sycn, in my Advanced Filter videos. I hope this helps.

@khawlaalqarni2676 2 жыл бұрын

what about if my database file in sharedpoint such as one drive of company? it's work? and how? please I want you answering me

@ExcelForFreelancers 2 жыл бұрын

Hi and thanks as along as each user has access to their individual folder it will work fine. I have an updated sharing and sync video here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@bernardvillacote6893 5 жыл бұрын

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

@ExcelForFreelancers 5 жыл бұрын

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

@customepainter1 5 жыл бұрын

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

@ExcelForFreelancers 5 жыл бұрын

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: kzfaq.info/get/bejne/bLqeq7OnseDDZGg.html Part 2: kzfaq.info/get/bejne/nKthaLpol9zSdXk.html

@HisNameIsGreatest 5 жыл бұрын

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

@SuperFilip2012 4 жыл бұрын

Fantastic

@ExcelForFreelancers 4 жыл бұрын

Thanks so much Filip, I have even a new one with better techniques on sharing and syncing here: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@thryzsakriezlsantos2711 4 жыл бұрын

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.

@ExcelForFreelancers 4 жыл бұрын

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: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@sadiqali4186 5 жыл бұрын

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

@ExcelForFreelancers 5 жыл бұрын

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.

@anacrob 5 жыл бұрын

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

@ExcelForFreelancers 5 жыл бұрын

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

@anacrob 5 жыл бұрын

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

@ExcelForFreelancers 5 жыл бұрын

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?

@anacrob 5 жыл бұрын

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!

@anacrob 5 жыл бұрын

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)

@annelouiseelmquist6361 3 жыл бұрын

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?

@ExcelForFreelancers 3 жыл бұрын

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

@annelouiseelmquist6361 3 жыл бұрын

@@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!

@ExcelForFreelancers 3 жыл бұрын

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/

@AbrahamCm 6 жыл бұрын

What if you use Microsoft Access instead of an extra Excel file to consolidate all the data? Would it be better? One of the issues I have is that is taking too much time when the xls is killed, copy and saveas again. is there any other way to make it faster?

@ExcelForFreelancers 6 жыл бұрын

HI Abraham. I had not tried Access but it might be a viable solution when you set up a database connection to access. With that said, try to have just one xls file per database. Meaning if you have an application that uses Customers, Invoices and Items, use 3 separate xls files to store each one. Also it should take no more than just a second to delete and save the xls file unless its a huge amount of data. 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

@ronzraf 3 жыл бұрын

Wow this is really amazing! By the way can I use my LAN storage rather than a cloud drive to share my database?

@ExcelForFreelancers 3 жыл бұрын

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

@ronaldrafal3982 3 жыл бұрын

@@ExcelForFreelancers Thank you very much!

@merlinthiemann433 2 жыл бұрын

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!!

@ExcelForFreelancers 2 жыл бұрын

HIU Merlin yes this should work just fine. You can use my updated training here as a guide: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html Thank you for your Likes, Shares & Comments. It really helps.

@merlinthiemann433 2 жыл бұрын

@@ExcelForFreelancers thank you :)

@muralibabu7258 5 жыл бұрын

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 ?

@ExcelForFreelancers 5 жыл бұрын

Yes they will, but use my newest sharing workbook training video here, this way the updates are automatic on every change: kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html

@pravinsonshukla 6 жыл бұрын

Nice tutorial, should help in a current project. However the links to download the workbooks are not working.

@ExcelForFreelancers 6 жыл бұрын

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

@rangariraimuvengwi4005 Жыл бұрын

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

@ExcelForFreelancers Жыл бұрын

Hi and thanks, I do have a simpler way and I share it with you in this video kzfaq.info/get/bejne/l7GGaNaWv9PNcqc.html I hope this helps and thanks so much.

@rangariraimuvengwi4005 Жыл бұрын

@@ExcelForFreelancers Thank you, i loved this one.. Is it possible to use MS Access as the database & Excel as the front end

@ExcelForFreelancers Жыл бұрын

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.

@imansalafian5512 4 жыл бұрын

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?

@ExcelForFreelancers 4 жыл бұрын

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/