Get specific cell data from multiple workbooks for import into MS Access (1 Viewer)

jom1918

Registered User.
Local time
Today, 12:04
Joined
Apr 26, 2011
Messages
30
Hi everyone,

We have multiple invoices in an excel format that has been setup like a document with an invoice header and then invoice line items and totals. We have hundreds of these that have to be imported to an existing access database. At the moment people are manually entering the details. Is there a way I could either get the relevant data from each invoice file and load into a new "import friendly" excel worksheet so I can easily import each file OR is there some vba code or macro I could use to collect the relevant data in an appropriate format? I have attached a spreadsheet showing how I would like to structure the invoice header and a sample of how the files come to us at the moment. The fields are always in the same specific cell locations on every invoice file.

Any help would be really appreciated!
 

Attachments

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 21:04
Joined
Feb 28, 2001
Messages
17,339
JHB is giving examples. I will supplement his suggestion by giving some theoretical and pragmatic viewpoints.

If you are going to drive this from Access, the idea of opening some worksheet; converting its format to ANOTHER worksheet; and then importing that to a table ... just seems wasteful. Here is the question you should answer (for yourself, since you are the one who has to do this). Do you have the knowledge and ability to define what constitutes valid input to the first part of that process?

That is, if you were going to convert formats programmatically, do you already know what you expect to see in each cell/row/column/whatever for the folks are doing manually? Do you have visual clues, markers, etc. that you could theoretically test?

If the answer to that question is "NO" then your whole process falls apart anyway.

Now, as to that format you have created for your attachment, that is pretty easy to manipulate (I think). In overview, you do the following:

  • Open an Excel Application Object
  • Using the FileSystem Object, look for/identify the file.
  • There IS such a thing as opening a FilePicker dialog box to help find the file.
  • Using the Excel app object, open the file that you have chosen as the workbook for the item to be imported.
  • Identify the desired worksheet (within the workbook) and make it the ActiveWorksheet
  • Direct-access the cells, which are found either as a collection of rows (each of which is itself a collection of cells) or a collection of columns (each of which is itself a collection of cells). I.e. access by row first or by column first.
  • You CAN do something like compare keywords vs. the content of a cell to see if you found a "marker"
  • If you can persuade your folks to start using that format, you can easily just run your analyzer code on each spreadsheet you get.

Now, here is my thought. If you CAN decipher the spreadsheet in the first place, use Recordset operations to immediately import it. Don't bother to change formats first and THEN do a second operation to do the actual import via TransferSpreadsheet or whatever else you would use. Why touch it twice when you can touch it once and be done?

One more thought: It is possible using the file system object methods to rename that file to another folder to signify that you have processed it, or you could just provide your system with copies of the workbook files and delete them when you are done with them.

If you really DID want to do some reformatting i.e. to output a record in the "preferred" format, then you could use the File System Object to COPY the file to some desired name, open the Excel App, open the workbook, etc. to fill in the cells that need to be filled in.

A couple of final thoughts... have you ever heard of that old book, "Everything I Needed to Know I Learned in Kindergarten" ? Rules such as "If you open it, close it." "If you take it out, put it away." Always be sure to close workbooks and close application objects when you are done with them.
 

jom1918

Registered User.
Local time
Today, 12:04
Joined
Apr 26, 2011
Messages
30
Thanks to you both for the sample and the suggestions. They have been very helpful. I CAN decipher the spreadsheet in the first place, so I am going to try the Recordset operations to immediately import it. I haven't used TransferSpreadsheet before but I will figure it out.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:04
Joined
Feb 19, 2013
Messages
12,252
a couple of years ago I was tasked by a client to import some 10000 spreadsheets generated over a 5 year period.

During that time the format changed - rows were added or deleted so the first 'item' was not on the same row

additional columns were added. Some values were 'annotated'. in some, numbers had been entered as decimals whilst in others they were integers. All depended on who had created the invoice.

In all, I identified some 150 'variants'.

My point is, check that your earliest invoice has exactly the same structure and layout as your last one and do spot checks in between.

If you are confident the layout etc is consistent, you can create some code in vba to loop through the files, importing them one by one.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom