Paste records in OLE Unbound Excel Object in Access Form

40 pts.
Tags:
Microsoft Access
Microsoft Access forms
Microsoft Excel
OLE
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). I'm using Access 2007. PLEASE help!

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Meandyou
    Do I understand correctly that you are populating a database by using CUT & PASTE from a REPORT? Can't you query the existing data to develop your results? Perhaps I missed something in your post, but I see too many people who take data from an existing source, copy it to another table and then work it from there (much like an old school 'work file').
    5,220 pointsBadges:
    report
  • Brianaaskew
    No, I am populating a database from records published on a secure website. I want to copy them from the website and paste them into an Access table. Problem is the website has some kind of funny formatiing which is making it so that when I go to the webiste, select the records, click copy then go to Access and try to paste, all the records are pasting in one field on the table. When I copy the records and paste them in Excel they appear in multiple fields (as they should be), then when I copy that from Excel and paste to my Access table the paste correctly. It's harb to explain but the way the records appear on the website makes it impossible for me to paste them into Excel them import them to Access, it's just not feasible with the way the data is set up. I basically want to use the OLE Unbound object to "clean" the records I copy from the website so that Access will recognize them as multiple fields and not try to paste them all into one field.
    40 pointsBadges:
    report
  • Brianaaskew
    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.
    40 pointsBadges:
    report
  • Darryn
    If it is pasting as a string into Access, just write a string parsing routine that breaks up the string based on the delimiter (I'm guessing tab or comma delimmited if excel is recognising it). You can then create a new recordset, pass in the values from the string parsing function, and upload the data to the tables. No need to involve excel. If you want some further direction on this just shout. Cheers
    765 pointsBadges:
    report
  • SbElectric
    Just another thought ... Have you experimented with Import option under Access 2007? It is located under Extrenal Data tab.
    2,540 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following