|
Use UserForm to Access Spreadsheet on Corporate Intranet?
In the office this past week I created a tool that was posted on the
corporate Intranet and users downloaded it to view certain reports and make forecasts to other reports. The file was 6.8MB and Tools Share Workbook Allow changes was checked. Over the course of the first day that the tool was posted to the Intranet, we noticed some strange behavior. Some people who open the file received a message saying that the file was Read Only but this setting was never enabled. Also, some people were unable to save the changes that they made to their forecasts (even when the file was not opened as Read Onlywhich was never enabled anyway). Some people had no problems, whatsoever, accessing and utilizing the file. All in all, probably 30+ people accessed the file throughout the day. Due to this strange, and seemingly random behavior, I can only imagine that we exceeded some kind of memory limitation. Has anyone encountered this type of behavior before? If so, what did you do to overcome these issues? I am thinking that there must be a better way to allow users to log into a single file, view the contents, and make a few changes. Can this be done with UserForms? Can I set up some UserForms, open them, allow users to input information into the form, and then send this data to a specific range on a specific sheet? I have some extensively experience programming Excel UserForms, but I have never tried to use a UserForm to log into a remote excel file. I have some experience using specific references in UserForms such as this Private Sub ComboBox2_Change() UserForm1.ComboBox2.RowSource = ThisWorkbook.Worksheets("Welcome").Range("A12:A20" ).Address(External:=True) End Sub Private Sub ComboBox3_Change() UserForm1.ComboBox3.RowSource = ThisWorkbook.Worksheets("Welcome").Range("B12:B20" ).Address(External:=True) End Sub Anyway, Im not even sure that this type of project is feasible or practical. Does anyone have any experience doing such a thing? What may be a viable alternative? Some people have MS Access, but most people do not, and will not have it installed, so I am trying to do this project in Excel. Id appreciate any and all input. Regards, Ryan--- -- RyGuy |
Use UserForm to Access Spreadsheet on Corporate Intranet?
I am only guessing but sounds like you are being prompted to open the file
Read only when another user already has it open? Whatever, if you are sharing a file to read / write data with multiple users I would consider having the users workbooks read / write data to a central database You could use Access which is normally available with MS Office or even use another workbook as the database although this will have limitations. If you want to go this route there are plenty of experienced people in this forum that should be able to give your further guidance if needed. Another alternative would be to consider a professional tool such as TM1. TM1 is a multidimensional online analytical processing (OLAP) database that extracts data from ERP systems such as SAP, and other relevant applications and is a building block for data warehousing activities, TM1 cross-correlates all the data into a massive "cube" that allows users to analyse trends using compound queries, e.g. revenues by type/region/profit margin etc. I am not 100% sure, but I think Congos (an IBM company) now own this product. Hope of some help -- jb "ryguy7272" wrote: In the office this past week I created a tool that was posted on the corporate Intranet and users downloaded it to view certain reports and make forecasts to other reports. The file was 6.8MB and Tools Share Workbook Allow changes was checked. Over the course of the first day that the tool was posted to the Intranet, we noticed some strange behavior. Some people who open the file received a message saying that the file was Read Only but this setting was never enabled. Also, some people were unable to save the changes that they made to their forecasts (even when the file was not opened as Read Onlywhich was never enabled anyway). Some people had no problems, whatsoever, accessing and utilizing the file. All in all, probably 30+ people accessed the file throughout the day. Due to this strange, and seemingly random behavior, I can only imagine that we exceeded some kind of memory limitation. Has anyone encountered this type of behavior before? If so, what did you do to overcome these issues? I am thinking that there must be a better way to allow users to log into a single file, view the contents, and make a few changes. Can this be done with UserForms? Can I set up some UserForms, open them, allow users to input information into the form, and then send this data to a specific range on a specific sheet? I have some extensively experience programming Excel UserForms, but I have never tried to use a UserForm to log into a remote excel file. I have some experience using specific references in UserForms such as this Private Sub ComboBox2_Change() UserForm1.ComboBox2.RowSource = ThisWorkbook.Worksheets("Welcome").Range("A12:A20" ).Address(External:=True) End Sub Private Sub ComboBox3_Change() UserForm1.ComboBox3.RowSource = ThisWorkbook.Worksheets("Welcome").Range("B12:B20" ).Address(External:=True) End Sub Anyway, Im not even sure that this type of project is feasible or practical. Does anyone have any experience doing such a thing? What may be a viable alternative? Some people have MS Access, but most people do not, and will not have it installed, so I am trying to do this project in Excel. Id appreciate any and all input. Regards, Ryan--- -- RyGuy |
Use UserForm to Access Spreadsheet on Corporate Intranet?
Thanks for the information john!! I have used Cognos before; it is amazing!
A team of developers is working on a long-term solution for us. In the meantime, we were trying to come up with a viable short-term solution using Excel, and maybe Access too. I have Access, and I have done some intermediate to advanced work in Access before, but many users don't have Access, so we were trying to stay away from that for the interface part. Can an Excel UserFrom be shown, without downloading the entire Excel file from the server, for the purpose of cutting down on memory and bandwidth usage? Basically, can a UserFrom popup and ask for inputs for the relevant information? I would like the user to then click a command button to capture all data in several TextBoxes, for instance, and transmit this data to a specific sheet in the Excel file which is stored on the server? I'd really appreciate any other ideas? Regards, Ryan--- -- RyGuy "john" wrote: I am only guessing but sounds like you are being prompted to open the file Read only when another user already has it open? Whatever, if you are sharing a file to read / write data with multiple users I would consider having the users workbooks read / write data to a central database You could use Access which is normally available with MS Office or even use another workbook as the database although this will have limitations. If you want to go this route there are plenty of experienced people in this forum that should be able to give your further guidance if needed. Another alternative would be to consider a professional tool such as TM1. TM1 is a multidimensional online analytical processing (OLAP) database that extracts data from ERP systems such as SAP, and other relevant applications and is a building block for data warehousing activities, TM1 cross-correlates all the data into a massive "cube" that allows users to analyse trends using compound queries, e.g. revenues by type/region/profit margin etc. I am not 100% sure, but I think Congos (an IBM company) now own this product. Hope of some help -- jb "ryguy7272" wrote: In the office this past week I created a tool that was posted on the corporate Intranet and users downloaded it to view certain reports and make forecasts to other reports. The file was 6.8MB and Tools Share Workbook Allow changes was checked. Over the course of the first day that the tool was posted to the Intranet, we noticed some strange behavior. Some people who open the file received a message saying that the file was Read Only but this setting was never enabled. Also, some people were unable to save the changes that they made to their forecasts (even when the file was not opened as Read Onlywhich was never enabled anyway). Some people had no problems, whatsoever, accessing and utilizing the file. All in all, probably 30+ people accessed the file throughout the day. Due to this strange, and seemingly random behavior, I can only imagine that we exceeded some kind of memory limitation. Has anyone encountered this type of behavior before? If so, what did you do to overcome these issues? I am thinking that there must be a better way to allow users to log into a single file, view the contents, and make a few changes. Can this be done with UserForms? Can I set up some UserForms, open them, allow users to input information into the form, and then send this data to a specific range on a specific sheet? I have some extensively experience programming Excel UserForms, but I have never tried to use a UserForm to log into a remote excel file. I have some experience using specific references in UserForms such as this Private Sub ComboBox2_Change() UserForm1.ComboBox2.RowSource = ThisWorkbook.Worksheets("Welcome").Range("A12:A20" ).Address(External:=True) End Sub Private Sub ComboBox3_Change() UserForm1.ComboBox3.RowSource = ThisWorkbook.Worksheets("Welcome").Range("B12:B20" ).Address(External:=True) End Sub Anyway, Im not even sure that this type of project is feasible or practical. Does anyone have any experience doing such a thing? What may be a viable alternative? Some people have MS Access, but most people do not, and will not have it installed, so I am trying to do this project in Excel. Id appreciate any and all input. Regards, Ryan--- -- RyGuy |
Use UserForm to Access Spreadsheet on Corporate Intranet?
You can achieve a great deal with Excel especially when connecting to other
platforms to support your application. To give an idea, I developed for my daughters work place a timesheet application as an Excel Addin. 250 users submit timesheets each week and although my first preference was to use Access as the database, they too only wanted to use Excel. The Addin is installed in each users machine & this has number of userforms for data entry. Depending on activity, users can read / write to database. Apart from need to manage the physical size of the data stored in the database sheet, so far (going for about two years now) they have not encounter any major problems. On the downside, this was a major development which would have been better served using a database like Access but Excel can be pushed to limits to do some quite surprising tasks. In the end, it really depends how much effort you want to put in developing your application. Personally for a Budget / Forecast application of significant size, I would look at something like TM1, it will do all you want and much more! Hope useful. -- jb "ryguy7272" wrote: Thanks for the information john!! I have used Cognos before; it is amazing! A team of developers is working on a long-term solution for us. In the meantime, we were trying to come up with a viable short-term solution using Excel, and maybe Access too. I have Access, and I have done some intermediate to advanced work in Access before, but many users don't have Access, so we were trying to stay away from that for the interface part. Can an Excel UserFrom be shown, without downloading the entire Excel file from the server, for the purpose of cutting down on memory and bandwidth usage? Basically, can a UserFrom popup and ask for inputs for the relevant information? I would like the user to then click a command button to capture all data in several TextBoxes, for instance, and transmit this data to a specific sheet in the Excel file which is stored on the server? I'd really appreciate any other ideas? Regards, Ryan--- -- RyGuy "john" wrote: I am only guessing but sounds like you are being prompted to open the file Read only when another user already has it open? Whatever, if you are sharing a file to read / write data with multiple users I would consider having the users workbooks read / write data to a central database You could use Access which is normally available with MS Office or even use another workbook as the database although this will have limitations. If you want to go this route there are plenty of experienced people in this forum that should be able to give your further guidance if needed. Another alternative would be to consider a professional tool such as TM1. TM1 is a multidimensional online analytical processing (OLAP) database that extracts data from ERP systems such as SAP, and other relevant applications and is a building block for data warehousing activities, TM1 cross-correlates all the data into a massive "cube" that allows users to analyse trends using compound queries, e.g. revenues by type/region/profit margin etc. I am not 100% sure, but I think Congos (an IBM company) now own this product. Hope of some help -- jb "ryguy7272" wrote: In the office this past week I created a tool that was posted on the corporate Intranet and users downloaded it to view certain reports and make forecasts to other reports. The file was 6.8MB and Tools Share Workbook Allow changes was checked. Over the course of the first day that the tool was posted to the Intranet, we noticed some strange behavior. Some people who open the file received a message saying that the file was Read Only but this setting was never enabled. Also, some people were unable to save the changes that they made to their forecasts (even when the file was not opened as Read Onlywhich was never enabled anyway). Some people had no problems, whatsoever, accessing and utilizing the file. All in all, probably 30+ people accessed the file throughout the day. Due to this strange, and seemingly random behavior, I can only imagine that we exceeded some kind of memory limitation. Has anyone encountered this type of behavior before? If so, what did you do to overcome these issues? I am thinking that there must be a better way to allow users to log into a single file, view the contents, and make a few changes. Can this be done with UserForms? Can I set up some UserForms, open them, allow users to input information into the form, and then send this data to a specific range on a specific sheet? I have some extensively experience programming Excel UserForms, but I have never tried to use a UserForm to log into a remote excel file. I have some experience using specific references in UserForms such as this Private Sub ComboBox2_Change() UserForm1.ComboBox2.RowSource = ThisWorkbook.Worksheets("Welcome").Range("A12:A20" ).Address(External:=True) End Sub Private Sub ComboBox3_Change() UserForm1.ComboBox3.RowSource = ThisWorkbook.Worksheets("Welcome").Range("B12:B20" ).Address(External:=True) End Sub Anyway, Im not even sure that this type of project is feasible or practical. Does anyone have any experience doing such a thing? What may be a viable alternative? Some people have MS Access, but most people do not, and will not have it installed, so I am trying to do this project in Excel. Id appreciate any and all input. Regards, Ryan--- -- RyGuy |
Use UserForm to Access Spreadsheet on Corporate Intranet?
|
Use UserForm to Access Spreadsheet on Corporate Intranet?
Yes, those two links are very helpful. I have used both resources, multiple
times, in the past. I just seem to have a mental block about how to do this thing. I'm going to sleep on it; hopefully something will come to mind. Thanks again for the information john!! Regards, Ryan--- -- RyGuy "john" wrote: Sorry but dont have anything in short form at moment that I can send directly as a working example, the application I mention is property of my daughters company & unable to distribute any part of it There are some very good sites that have good working examples of copying data to other workbooks and creation of userforms etc: http://www.rondebruin.nl/tips.htm http://www.contextures.com/excelfiles.html Suggest take a look at these (and any others you can find via say google) & should point you in right direction. As I said earlier, you can post any code problems to this forum & most here will be happy to give further guidance if need. If I do get a moment, i will see if I can cobble something together. Hope helpful -- jb "ryguy7272" wrote: Excellent!! Indeed, excel is a powerful too. Do you have a mini version of that tool? Can you send me one Userform and one Sheet to capture and store the data? I think I can figure out the rest. Im stumped as to how to get the UserForm away from the Sheet. In other words, I dont know how to do this without giving users access to the whole Workbook. If you could send me one Form and one Sheet, with a blurb or two if there is some really technical stuff, Id really, really, really appreciate it. (without the xx part) Regards, Ryan--- -- RyGuy "john" wrote: You can achieve a great deal with Excel especially when connecting to other platforms to support your application. To give an idea, I developed for my daughters work place a timesheet application as an Excel Addin. 250 users submit timesheets each week and although my first preference was to use Access as the database, they too only wanted to use Excel. The Addin is installed in each users machine & this has number of userforms for data entry. Depending on activity, users can read / write to database. Apart from need to manage the physical size of the data stored in the database sheet, so far (going for about two years now) they have not encounter any major problems. On the downside, this was a major development which would have been better served using a database like Access but Excel can be pushed to limits to do some quite surprising tasks. In the end, it really depends how much effort you want to put in developing your application. Personally for a Budget / Forecast application of significant size, I would look at something like TM1, it will do all you want and much more! Hope useful. -- jb "ryguy7272" wrote: Thanks for the information john!! I have used Cognos before; it is amazing! A team of developers is working on a long-term solution for us. In the meantime, we were trying to come up with a viable short-term solution using Excel, and maybe Access too. I have Access, and I have done some intermediate to advanced work in Access before, but many users don't have Access, so we were trying to stay away from that for the interface part. Can an Excel UserFrom be shown, without downloading the entire Excel file from the server, for the purpose of cutting down on memory and bandwidth usage? Basically, can a UserFrom popup and ask for inputs for the relevant information? I would like the user to then click a command button to capture all data in several TextBoxes, for instance, and transmit this data to a specific sheet in the Excel file which is stored on the server? I'd really appreciate any other ideas? Regards, Ryan--- -- RyGuy "john" wrote: I am only guessing but sounds like you are being prompted to open the file Read only when another user already has it open? Whatever, if you are sharing a file to read / write data with multiple users I would consider having the users workbooks read / write data to a central database You could use Access which is normally available with MS Office or even use another workbook as the database although this will have limitations. If you want to go this route there are plenty of experienced people in this forum that should be able to give your further guidance if needed. Another alternative would be to consider a professional tool such as TM1. TM1 is a multidimensional online analytical processing (OLAP) database that extracts data from ERP systems such as SAP, and other relevant applications and is a building block for data warehousing activities, TM1 cross-correlates all the data into a massive "cube" that allows users to analyse trends using compound queries, e.g. revenues by type/region/profit margin etc. I am not 100% sure, but I think Congos (an IBM company) now own this product. Hope of some help -- jb "ryguy7272" wrote: In the office this past week I created a tool that was posted on the corporate Intranet and users downloaded it to view certain reports and make forecasts to other reports. The file was 6.8MB and Tools Share Workbook Allow changes was checked. Over the course of the first day that the tool was posted to the Intranet, we noticed some strange behavior. Some people who open the file received a message saying that the file was Read Only but this setting was never enabled. Also, some people were unable to save the changes that they made to their forecasts (even when the file was not opened as Read Onlywhich was never enabled anyway). Some people had no problems, whatsoever, accessing and utilizing the file. All in all, probably 30+ people accessed the file throughout the day. Due to this strange, and seemingly random behavior, I can only imagine that we exceeded some kind of memory limitation. Has anyone encountered this type of behavior before? If so, what did you do to overcome these issues? I am thinking that there must be a better way to allow users to log into a single file, view the contents, and make a few changes. Can this be done with UserForms? Can I set up some UserForms, open them, allow users to input information into the form, and then send this data to a specific range on a specific sheet? I have some extensively experience programming Excel UserForms, but I have never tried to use a UserForm to log into a remote excel file. I have some experience using specific references in UserForms such as this Private Sub ComboBox2_Change() UserForm1.ComboBox2.RowSource = ThisWorkbook.Worksheets("Welcome").Range("A12:A20" ).Address(External:=True) End Sub Private Sub ComboBox3_Change() UserForm1.ComboBox3.RowSource = ThisWorkbook.Worksheets("Welcome").Range("B12:B20" ).Address(External:=True) End Sub Anyway, Im not even sure that this type of project is feasible or practical. Does anyone have any experience doing such a thing? What may be a viable alternative? Some people have MS Access, but most people do not, and will not have it installed, so I am trying to do this project in Excel. Id appreciate any and all input. Regards, Ryan--- -- RyGuy |
| All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com