Unanswered: Paste records into Excel Unbound Object
Ok so I have built a database that is used to house and manipulate records that I get from a monthly report that is posted online. Basically I work for the Army and we have over 50 units. Each unit has anywhere from 10 to 1000 soldiers in it. I initially had tried to build a database with a table for each unit but that wasn't working out fro queries and just wasn't design friendly. What I've done now is built one table to house all the soldiers and a query which puts in what unit the soldier is from when you paste the names in a form. Unfortunatly I cannot do a mass download of each report. The only way to pull it is to go to each units report and copy the records. It's an Army program and has all kinds of security stopping me from doing anythin other then copying the records. I need to be able to then paste them into my Access database so I can create reports and other stuff like that. The way the website table is formatted causes it to be pasted in one field when I try to Paste or pasteappend directly into Access. The only way that I have found to paste it in Access is to paste it in Excel first then copy and paste it in Access. I can not do the whole import from Excel thing cause I need to make this database as automated and user friendly as possible so telling the other users they have to open an Excel sheet paste it in there then copy it go back to Access and paste it again in their won't work. I don't know how else to explain it but basically I'm trying to stay away from using a separate Excel document if at all possible. Here's what I've done: I have created a form called "Paste" with an OLE unbound Excel worksheet on it called "PasteRecords". I have another form called "Main" which I want to be the main user interface. Basically what I want to do is have the user go to the website copy the records, then go to the "Main" form and click a command button called "PASTEALLCMD" which will open the "Paste" form delete whatever is already in the OLE unbound Excel worksheet ("pasterecords"), paste whatever is on the clipboard, select it all, copy it, and then close the "paste" form. I have changed the properties of the OLE Unbound object so it is enabled, unlocked, and Auto Activate's upon Get Focus. Basically when you open the form "Paste" it is ready to have the records pasted but I can't figure out a code to do this automatically without the user having to do anything. I know it sounds wierd but I have tried everything else and the only way I can find to get these records pasted in the Access table is to first have them go through Excel (I'm guessing pasting them in Excel is reformating the date and allowing Access to read them as individual cells rather then one long string). PLEASE help!
Ok let me clearify.
I work for the Army in the records department. We keep files on everyone posted here. There is a website that we pull an accountability report from every month (it changes daily) so that we can do an audit of our records and make sure we have a file on everyone. We have clerks that are assigned to individual units and they are responsible for those units. We have about 50 units total. Each unit has anywhere from 1 to 15 "UIC's" (smaller units) and each UIC has anywhere from 1 to 2000 soldiers listed.
For example, we have a unit called 192nd. 192nd has it's own filling cabinet. 192nd has 10 UIC's. The filling cabinet has all the soldier's for 192nd (each assigned to one of the ten UIC's that fall under 192nd) filed together in alphabetical order. The accountability report from the website lists the soldier's by UIC not by Unit (so I have to pull 10 reports). The accountability report is a table on a website that has fields like name, rank, ssn. There is no field for unit name or uic (it is a text box in a corner on the website). The information in the accountability report has to be placed in a table put in alphabetical order and then printed in one list so that the clerk can take that list to the filling cabinet and check off each name to make sure the record is there.
So, if I were to copy a table from an accountability report and paste it into Excel and then go to the next UIC and copy and paste that into Excel there would be no way of telling what UIC or Unit the soldier is from. What we were doing in the past is we had one Excel workbook and an Excel sheet for each Unit. I would go the sheet for that unit then go to the webiste and pull each UIC accountability report, paste it in it's Unit's sheet and do a sort so they all appeared together in alphabetical order. This did not allow us to really do what we need to do as far as creating reports and stuff like that. I started building an Access database based off the Excel sheet and was basically making a linked table for each sheet in the workbook. This left me with over 50 linked tables and meant I had to work with both Excel and Access. With the reports I want to create and the queries I want to pull this made the database EXTREMELY large and made for very poor design. I realized I needed one table with all the soldier's names (and other info) together and I needed to add a field with the UIC so that they could be grouped and printed in reports. The problem was putting the UIC in each record without having to go in and enter it in manually (remember some UIC's have over 2000 soldiers). I created two tables SoldierRecords and TempSoldierRecords. I created a Form called Main with a subform called Mainsubform linked to TempSoldiersRecord. The user opens the form selects the UIC they are pulling the accountability report for from a dropdown box, they then go to the website and open the report for that UIC, copy the record, goes back to the form and hits a button called paste. This button pastes the records to the subform (from TempSoldiersRecords) and runs a command to read the selection made from the UIC dropdown and then makes the value for the UIC field in the subform the same as the selection from the dropdown. Then the user hits another button called save and these records in the tempsoldierrecords subform are appended to the SoldierRecords table and the subform is cleared for the next UIC to be entered. This works fine and is exactly what I want it to do. The problem is that this website has some kind of funny formatting which is causing all the fields from the table on the website to be pasted into one field in Access as one long string. When I paste these records (straight from the website) into Excel they are fine and appear in multiple fields, when I then copy the records from Excel and paste them in Access they are fine and appear in multiple fields. Basically Excel is somehow "cleaning" the formating from the table I copied from the webiste and allowing Access to see it as multiple fields rather then one long string. I can't have to paste in Excel and copy then paste again in Access for each UIC (there are hundreds) I created another form called "Paste" that has a OLE unbound object Excel worksheet. I want the user to go to the "Main" form and be able to click a button that will "clean" (using the OLE Unbound Excel Worksheet) whatever they copied from the website and then paste in the subform on the Main form. I know it sounds crazy but I've been workign on this for two months now and I can't think of any other feasible way to do it with all the factors considered.