Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-02-2016, 04:22 PM   #1
jom1918
Newly Registered User
 
Join Date: Apr 2011
Posts: 30
Thanks: 6
Thanked 0 Times in 0 Posts
jom1918 is on a distinguished road
Get specific cell data from multiple workbooks for import into MS Access

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!
Attached Files
File Type: xls Invoice_template_header_only.xls (95.0 KB, 62 views)

jom1918 is offline   Reply With Quote
Old 10-02-2016, 11:09 PM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Get specific cell data from multiple workbooks for import into MS Access

Open the form in the attached database, and click the button and then have a look at the table.
Attached Files
File Type: zip Invoice_template_header_only.zip (94.1 KB, 41 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-03-2016, 06:56 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,566
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Get specific cell data from multiple workbooks for import into MS Access

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-03-2016, 02:22 PM   #4
jom1918
Newly Registered User
 
Join Date: Apr 2011
Posts: 30
Thanks: 6
Thanked 0 Times in 0 Posts
jom1918 is on a distinguished road
Re: Get specific cell data from multiple workbooks for import into MS Access

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.
jom1918 is offline   Reply With Quote
Old 10-03-2016, 04:14 PM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Get specific cell data from multiple workbooks for import into MS Access

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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Tags
excel , import , invoices , ms access

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Specific Cells from Multiple Excel Workbooks to Access Database nelson1121 Modules & VBA 6 01-16-2014 10:59 AM
VB Script to import Excel workbooks with multiple tabs in MS Access 2010 captdkl02 Modules & VBA 2 12-05-2012 03:53 AM
Import Process For Multiple Excel Workbooks to Single Access Table SazzleTWG Modules & VBA 3 10-18-2012 06:06 AM
import specific cell from excel to access mane_uk Modules & VBA 12 07-01-2011 02:31 AM
Import multiple workbooks into 1 access table piedpiper8 Modules & VBA 4 11-13-2008 07:48 AM




All times are GMT -8. The time now is 05:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World