Using access to get data from excel objects

[Goldfish1]

Hi, I am fairly new to using VBA in Access and Excel. I have an Excel form that I am using to collect data as the front end and an Access database to house the data as the back end. In my Access database I want to create a module that will open the excel files and retrieve data from Active X combo boxes, option buttons and checkboxes that are on the Excel form. I have been searching for the VBA code to get data from these objects but have not been able to find any examples. If anyone knows how to reference these objects and get the values from the excel form, I would appreciate it! Thank you very much!

 

[DavidAtWork]

Hi Goldfish, you might want to rethink your approach
"I want to create a module that will open the excel files and retrieve data from Active X combo boxes, option buttons .... "
When you open these Excel files, unless you are running some funky start up code from the Workbook open event, these forms won't be loaded and the objects won't have any values.
If you explain a bit more how this is currently working or how you envisiage this working.

David

 

[Goldfish1]

Hi, Thank you so much for responding so quickly. In the excel file, I have a button to load the combo boxes with data. I did notice that every time I open the excel file, I would have to reload the combo boxes because there would be nothing in them. But if you select a value form the drop down and save the file, the value is still there when I open it up again. Same for the option buttons and check boxes, the selected controls seem to be marked the same when I open the file again. If the value is there when I open the file again, is there a way to get the value? Thank you so much again for your help!
Judy

 

[DavidAtWork]

Judy, I suspect you may need try a different approach such as opening the Excel file, create a connection to your database and then write the values from your form back to the database using some SQL.
It is possible to run Excel and open a file from Access, but I've never tried referencing form objects from within Access

David

 

[Goldfish1]

Hi David,
I think you are right, that would be easier to go from the Excel form and connect to Access, thanks for the suggestion! I have discovered another problem though. The excel button to populate the box connects to Access to get data from a table and populates hidden sheets. The macro for the button then populates the combo box from the hidden sheet. For the business name combo box I have 2 columns, business name and address. I have another module that recognizes when someone selects an item from this combo box, it gets the value then searches the hidden sheet for the business name and gets the row so that it can retrieve the address in the next cell of the hidden sheet which will then go back to sheet 1 and populate a different cell with the address. I wanted to automate that for the user and to make sure they write the address correctly for import back to the database (in the import, it needs to check if the business with that address exists so that it won't populate the business table if there already). It worked fine except there could be a business with multiple addresses. For example if I pick the business listed second in the list, the address from the first listing will populate the address box since the code found that row first. I could only find the syntax ThisWorkbook.Sheets("Sheet1").combobox1.value which gets the bound column. Is there a way to get the 2nd column from the item they selected so that I don't have to get the address from the hidden sheet? Thank you!
Judy

 

[DavidAtWork]

Judy, when you say "someone selects an item from this combo box, it gets the value then searches..."
Is there a reason why you can't develop this whole thing in Access as it does sound like you're taking data from Access to Excel and back to Access or am I missing something?

David

 

[Goldfish1]

Hi David,
The situation is that there is already an Access database that was developed for this department. There are 3 groups within this department and there are 3 Access databases. The databases need to interact with each other. The person that created these databases made all of the tables link between each other with the 3 databases. There are maybe 5 or so people in each group. They told me that when 1 person goes in the database it is fine. But when a few go into the database the performance is terrible and they can't use it. So now they are planning to create a better system that might take a year or two to complete. They want to create a web site interface with probably SQL Server back end. I don't have the capability to create something like that so they are working on finding someone. What I am working on is an interim solution. We didn't want to spend a lot of time and effort since it will be obsolete when the new system is complete. So rather than rebuild the 3 databases we thought it better to have Word or Excel forms and import them into the database. It is starting to feel like this is becoming a lot more effort than originally thought. In order to keep the data in good shape, I need to bring some of the information to the form like business name and address and then check the data going back in. I know this is not the ideal, but seemed like the only option. Do you think there is a way to get both columns in the combobox that were selected?
Thanks!
Judy

 

[Goldfish1]

Hi David,
Let me clarify. There are 3 Access back end databases and 3 Front end database. The front end of each database has linked tables to the other 2 database.
Thanks,
Judy