PDA

View Full Version : Can I use VBA with this problem?



oscar45
09-24-2015, 08:53 AM
I am working on a VBA project for my group we can have a way of easily reading data in one place.

Right now, my team of 5 are doing online searches and are able to export search results into excel worksheets. I want to create some kind of system that will allow for users to manually upload their worksheets into one place. I need to separate the data results based on Location (which has it's own field name/column in every export). I want the data to automatically be separated into different Excel worksheets based on City (like New York City, DC, Miami, Los Angeles, ex.).

My initial though is to have a folder with 3 documents in it. The first document will be the Search Results Excel Sheets. Team members would manually copy and paste their search results into this workbook. They will create a new worksheet for each search result (and label the worksheets SearchResult1, Searchresult2, etc). Then I want a way to automatically upload each worksheet into an Access tables. This Access document will be the second document in the folder. In Access, I want to create various queries based on the City. The last document in this folder will be an Excel Workbook that has a worksheet for each City. The entire data row will transfer from the Access Queries to the appropriate worksheet within this workbook.The users will be able to click on this third document and be able to search for data based on the City.

Is there a better way of going about this? I want to make this as automated and user friendly as possible but my VBA skills are limited. I really want a way for a user to manually upload their search results (an exported Excel worksheet) and somehow have it sorted into another Excel Workbook based on the City name.

Does anyone have any suggestions?

SamT
09-24-2015, 11:57 AM
Please. More clarity and more detail.




users to manually upload their worksheets into one place.Logically impossible. A WorkSheet is not a file. A WorkBook is a file. You can have them save a search result WorkSheet to a common Workbook.




I want to create various queries based on the City. This is different from Collating search results by city. Simple Collating allows searching for data by city. Queries is a method of Data Mining. ie. Results = All cities with Population > 100000 And ((Sales < $20000) OR (SalesPersonnel < 5))




I really want a way for a user to manually upload their search results (an exported Excel worksheet) and somehow have it sorted into another Excel Workbook based on the City name.I recommend a workbook with two worksheets: A VBA Data Sheet with a list of all cities and a Search Results Sheet. The workbook should also have a UserForm with controls to upload the Web Search result to Access and to Transfer City data From Access to the Search Results Sheet as selected by a CityPicker ListBox that uses the List of Cities on the VBA Data sheet. Access is better at handling inputs/outputs from/to Multiple Users than Excel. Alternately, the Form can Create a City Worksheet to hold the Data from Access allowing multiple City sheets in the same WorkBook as needed.