excel to pervasive
i dont know if anyone is familiar with what im talking about, im actually engineering, but since no one else seems to be able to figure this one out im stuck with it.
basically i have a scenario where we have a parts list in excel (60,000) and they are in a 3 column (pn, description1, description2) format. we could copy paste right to the database using access, but that would be real time and potentially mistakes would be huge mistakes, that and it puts me in a security position (i could commit corporate fraud, etc) basically i need to be able to enter these parts and modify their descriptions in accel and then ad them into the system 1 at a time.
to make matters worse the system is set up such that once i am done editing 1000 or so descriptions i have to play the alt tab, alt c, alt v, game copying and pasting one cell at a time to its appropriate field in the pervasive program macola itemmaster (that what we call it, though i dont know if others use it at all)
i wanted to try to use c++, but it doesnt seem to be working for me, and excel macros wont switch between the two programs, so i was told to look into visual basic.
having been dissapointed before i thought i would search for people that knew more than i did before i invested some serious time of teaching myself to use visual basic.
basically i need to know if and how to do one of two things. if somehow i could tell the program to actually do the key command string that i enter to move one entire pn,desc1,desc2 set to "itemmaster" and then loop that program until a designated time, that would work.
or if i could tel the computer to copy and paste 1 cell at a time from excel to the field in pervasive, macola, itemmaster, then that would also work.
a find and replace idea wont work though for our system (unfortunately)
so like i said i need a way to get the computer to copy "A1", switch to another open window, and paste that cell into a location, switch back to excel, copy "b1", switch to the other open window,tab over once, paste cell, switch back to excel, copy "c1", switch to the other open window, tab over, paste, enter, switch back to excel, and start all over again with the next row.
any help would be great. furthermore i might even be interested in paying someone if they could actually do it for me.
let me know what you guys think as far as possible solutions
Just use the data import feature in Access. enter the data in your Excel spreadsheet. Then go in to Access and select File - Get External Data from the menu and follow the dialog boxes [see attached screen shot] that are displayed to connect to your Excel file and import the data. You could even write/record yourself a little macro if this will be a repetitive function.
i dont think that will work, import is a command that is similar in funtion to me just highlighting cells and copy, pasting them in.
this would require import to recognize that one cell goes in a certain field. in other words what im copying into does not have a row colum format, it has 3 random locations on a window. its not copying one database to another. thats the issue.
i need it to recognize a specific location that the single cell needs to be imported into, not just another cell "a1"
thanks for the input, any other ideas?
and i think i typod, its progression explorer that i am copying into
also i dont want to do access because the companies live data is there and there is potential for huge errors and such, i dont want to be in that position.