PDA

View Full Version : VBA for batch upload



BENatUSGS
06-17-2010, 11:43 AM
Hi,
I work for the United States Geological Survey and have been charged with the task of uploading old data into our national data base. The easiest way to do this (because of the quantity of data) is to format the data into an excel spreadsheet that our data base can recognize and do the rest on its own. The excel workbook is separated between two sheets, Sample and Result. The Sample sheet describes information about the data collected(agency code, sample date and time site number ect) and the Result sheet describes information about the data itself(type of data or sample, how the data was collected and processed). So, all the sample information may have multiple result data that relates to it.

My codes objective it to make a user friendly way to enter in large quantities of data and reduce user input error.

I have a couple of questions but probably cannot be answered unless you can see the code.This macro has a lot of code so I'll make it an attachment unless I find out it is ok to post that much code.:dunno

Question
1. You can see from the code that if the user was to type in “ABORT” that the macro would prompt for more data input and then allowing you an option of saving before ending the macro. Due to my lack of VBA skills :banghead: the user input goes to the next available cell with no data in it. So, depending on how much data the user inputted before typing “ABORT” (ie ½ the way through ) If the user would try to restart this macro to continue their work they would end up with data in incorrect spots. So, does anyone one have an idea for a better way to either input the data into cells or have the code recognize when abort is typed and always return to the last inputted cell?
Any ideas would help
Thanks,
Ben
By the way my 1st post so if the way I am posting or uploading my attachment is incorrect please let me know. Also, I apologize for my terrible spelling and grammar.

p45cal
06-17-2010, 03:43 PM
Set up a user form so that they can enter their data, in any order they want to. The user form would contain all the fields required, on both sheets.
When the user presses OK, checks are made on all the fields, and if all checks are OK, only then do the data all get placed on the sheets, in one go. That way they can abort/fail at any time without having any partly filled rows.
One advantage of this is that later, you can develop the code and user form to review existing data, and update/correct it.

What form does this old data have? Is it on paper?

BENatUSGS
07-02-2010, 09:26 AM
Sorry it has taken me so long to respond p45cal…
The data is in the form of a 25 year old paper back book

p45cal
07-02-2010, 09:36 AM
Aaaaggghhh! Then I would use OCR in the first instance to get the data in some form onto an excel sheet, then post process the scans. Abbyy Finereader did a good job for me 'bout 10 years ago.