A combination spreadsheet as database, userform, onsheet userform and VBA to do stuff.
Please help if possible.
I have a Tab "Search Page"Sheet3 that I can either use to open a VB search form or use an on sheet form directly to search on a per circuit or customer basis from the tables, or just to manage the paste and analyse process with buttons. The column values I need to be dynamic, that if I need an additional column added, it would not require a complete re-write. If possible, well documented notes on what the VBA does.
The search form is designed that it would make use of the data tables in Sheet1(CircuitTable) and Sheet2(CustTable) to populate TextBox's. Not yet figured this out.
Ideally I need a dual function in this workbook which would be the buttons method, and the userForm or on sheet form method, which I am having some difficulty with all of it. I have been able to get some of it to work.
The data must be in the Windows clipboard, not Excel. If possible compatible with either.
What is currently working. (Or was)
I copy data from a production system, and paste this into Sheet6 using VBA and xlPasteAll, the sheet is named "Temp" (It's supposed to be hidden), using a macro linked to a button.
I get the message popup that paste is successful and the data is in fact in the sheet.
I also get a message popup if I click Paste Data when the clipboard is empty "Nothing to Paste".
After this I click another button named "Analyze1" or "Analyze2, depending on the data that was pasted, this copies specific columns from the table to a new sheet, where I have VLOOKUP setup in column G and H on customer names to populate the rest of the columns from the table "CustTable". Also another message popup if no data available to analyze. I would prefer the vlookup not to reside in the cells as this can be destroyed by accident. Another method would be better. However if there is no data in the Customer column, a blank would be preferrable, and if there is no match, then "No data" or "No Match". Column G could have no Match, but H may and G will have a default of "No SCP" or blank if no data in the lookup.
Another obstacle is that the source data comes from two different apps, where the column header names are different for the same information. Eg. "Customer" and "Customer Name". I'm strugling with renaming the columns before copying to the new sheet.
There are others too like "Circuit/Eq ID" and Circuit/Equip ID." I am still figuring this lot out. If there is a way to rename the headers after pasting them, or just look for the specific columns and copy the data to the new sheet to analyze using pre-labled columns. Another problem I found is that the columns on the source is user arranged. So they are not always in the same order, however the column names cannot be changed by them. The destination sheet "Analyze" I would prefer defined columns. Also what if my data source is from another spreadsheet?
I am thinking Set somerange as Range(A1:ML) then use an If then statement on the range. Also I need to cater for errors, On Error Resume Next or On Error Goto ErrorHandler.
The sheetform is not currently doing anything as I have not yet started to figure out the coding for searching.(Also do I need to have a separate userform if I am using an on sheet form? Which would be better?). What I would like it to do is populate the information Search onClick. Or popup with a message if not found. This is a lot to ask, but if anyone can help?
I am not allowed to use an Access database. I could install a local copy of mySQL, but that has it's limitations too.
The likelyhood that the customer name or circuit number is not in the table is very possible, which I came across yesterday.
Unfortunately I have to use an excel table as my database as I cannot get SQL database storage at this time. I plan to do this at a future date as a standalone app, with connection to SQL so the database can be maintained seperately from the spreadsheet and app.
The most important part is that this needs to be backwards compatible to Windows 7, as well as Office 2013 and that would include x32 and x64 versions of Windowns and Office.
We are currently migrating to Win10 x64 and Office 365 but there are many people still on Win7 x32 and Office x32.
Any assistance would be appreciated.
I have included what I have been able to piece together. I know it's rather messy, but it's what I can do. I need to make sure this doesn't break.
I've had a couple re-writes for compatibility issues, but there's much that I have not gotten to and it seems complex to say the least. Some things that should work don't and I don't know why either.
I have placed two samples of data from the system into tabs Source1 and Source2. Rows 1 and 2 are my ramblings and are not copied to clipboard from the Apps.
Thanks to anyone willing to assist or take on this challenge. I am figuring it out slowly, but not fast enough.
My full customer list is over 300 000 lines, the cct is about 600 000 lines.
Thanks again. Spreadsheet attached. "Sample data.xlsm"
Last edited by HandSM; 12-04-2019 at 04:50 AM.
Reason: Trying to attach the spreadsheet
Re: A combination spreadsheet as database, userform, onsheet userform and VBA to do stuff.
I will polish my crystal ball.
The impossible takes me a little time.
Miracles take even longer.
Sample files would be a good starting point.
See the 'yellow banners' at the top of the page.
Re: A combination spreadsheet as database, userform, onsheet userform and VBA to do stuff.
Thank you for the reply. All the sample data is in a tab Sample1 and Sample2. On the Sample2 sheet I sould have included some of the other CircuitNo where they consist of Numbers and letters eg. ABC1234, so the temp sheet and lookup sheets are formated as text. The only sheets that will exist in the final workbook is an About sheet, thanking all contributions I can find and what the workbook function is. The Cct and Cust tables/sheets(Hidden), and the Search Page and Analyze sheets.
I have already done some of the VBA, so it is parcially done, with what I have been able to do.
If you download the Sample data.xlsm file, it will all become clear.
What is included in the workbook, besides the sample data itself, is a Main sheet "Search Page" with buttons and a non-functioning onsheet form as well as a partially working UserForm, the customer data sheet"CustHoldGrp", a individual circuit sheet"CctTypeCustHoldGrp" and the output sheet called "Analyze"
Everything is in the workbook. I use DAX and Queries to get the two customer tables to what they are
The problem is on the UserForm, populating the form with the search button/s; The column names when pasted from source 1 or 2; copying the columns to "Analyze" in a specific order and lastly I think is the vlookup that will not break.
You see, the customer tables are refreshed daily, so I will schedule a refresh, or refresh on open from other spreadsheets on a shared folder. Those lists are large and local copies are faster.
I have read that Dictionary is a faster search/lookup method, but can't figure it out, also I have read that INDEX and MATCH is also a faster method. As of this morning the Cct table has grown to almost a million lines of data. Unfortunately the source data, I cannot be selective with which columns I want to copy to Windows clipboard, so there is a lot of unneeded columns pasted to temp, but they are not so many rows. I clear the "Temp" sheet after copying the columns I want. I think the largest set I worked with this week has been 586 rows. But the potential to grow is there, depends also on the filters I use on the apps I copy from.
Is this clear, or do I need to extract parts of the VBA and post those, for individual help on each part? I have tried a couple things and am also still trying to figure it out. But, as always, you guys know way more than I do and can do this so much faster.
Also, thanks for all the snippets that I have been able to find and use. I cannot acknowledge individuals as I have lost track of who provided what.
I'm getting there slowly. I have a couple modules that have VBA in that does not do what is needed, have not removed it yet, but will clean it up later.
At some point I will create a form for reading data from the customer table(the original source) and edit and save or delete the record.
I think this will be in January, but for now, I have this workbook problem.
The Workbook I base this sample on is 18Mb at present, I have not done the Cct update as yet, so it will be quite large once I do so.
Any help would be appreciated.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By masro in forum Excel Programming / VBA / Macros
Last Post: 04-15-2015, 02:42 PM
By bananajelly in forum Excel Programming / VBA / Macros
Last Post: 05-14-2014, 03:47 PM
By H_Kennedy in forum Excel Charting & Pivots
Last Post: 01-04-2014, 07:11 AM
By H_Kennedy in forum Excel Programming / VBA / Macros
Last Post: 01-03-2014, 12:28 PM
By jfoerch in forum Excel Programming / VBA / Macros
Last Post: 04-25-2013, 02:38 PM
By nirvanarapeme in forum Excel Programming / VBA / Macros
Last Post: 08-04-2012, 05:18 PM
By welchs101 in forum Excel Programming / VBA / Macros
Last Post: 04-05-2010, 04:11 PM
Tags for this Thread
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1