Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 3rd, 2012, 04:09 PM
Authorized User
Points: 308, Level: 6
Points: 308, Level: 6 Points: 308, Level: 6 Points: 308, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using queries to import data from multiple spreadsheets

I need to import data from spreadsheets into Access on an ongoing basis. I know how to set up the queries themselves. But there are 2 things I do not know how to do.

1. I would normally tell the query where to look for its data, that is the name and location of the spreadsheet. But I will be given spreadsheets of unknown names, sometimes more than one at a time. Is there an easier way that taking the spreadsheets one by one, give each of them a pre-set name, run the queries, change the name of the spreadsheet, then rename the next spreadsheet with the pre-set name and keep cycling this way through the spreadsheets? Is there a way to tell the queries to run on all spreadsheets at a certain location, or with a certain date, or with part of the file name that is maybe the same?

Second, is there a way to tell a query to import the data from a spreadsheet only if, say, cell A1 in sheet1 equals 0? That is, this supposes that I already know how to import from all spreadsheets in a certain folder, or whatever, but that I only want to get data from those will the correct entry in a specific cell in the file?
Reply With Quote
  #2 (permalink)  
Old July 4th, 2012, 10:39 PM
Authorized User
Points: 308, Level: 6
Points: 308, Level: 6 Points: 308, Level: 6 Points: 308, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I would like to add a bit more detail to my question.

In short, I want to run append queries in Access that will get data from an ongoing string of excel spreadsheets. By ongoing, I mean that they will continue to arrive over a period of time.

In time order, here is what I foresee happening:
1. On day 1 there will be a number of spreadsheets submitted. There will be all sorts of data in various tabs on the spreadsheets, all of which will be imported into (appended into) existing Access tables. We will save these spreadsheets in a specific folder on the computer. These Excel files will all have similar but not identical names.

2. My thought is that in some cell in the spreadsheets there could be an entry of zero (or whatever) that indicates that this spreadsheet has not yet been processed.

3. A series of Access append queries will look at all the spreadsheets we received, make sure there is a zero in the predetermined cell, and then bring across the data. We need to import all data from all spreadsheets, but it requires splitting it up to put it into a variety of Access tables and fields. The imported data will never go into a new table, will always go into existing tables.

4. Then we will change those zero's into number 1's to indicate that these spreadsheets are not processed.

5. Then on day 2 more spreadsheets will arrive and we will begin the process again.

Issues:
We could change the designated cells from 0 to 1 manually in the cell that indicates what spreadsheets have been processed. But having a person do this begs for a problem sooner or later.

We might want to put unprocessed spreadsheets into one folder and move them to a different folder once processed. It seems like this might be easier than using a cell to denote this information. Plus, it would avoid the process checking a whole of of files that have the number 1 in our cell and do not need to be imported.

So, my questions are:
How do I tell the append queries to run on all spreadsheets in a folder (or the more complex task of running it on all spreadsheets in a folder that have the zero in a certain cell)? And I guess a subquestion is that I have never done this without knowing specifically was the Excel file name is. (I really want to do this with append queries, not code).

How do I use an Access query to either change that cell value (from zero to 1 in my example) or to move the files from the folder for unprocessed files into the folder for previously processed files? Again, with the issue of not knowing in advance exactly what each of the files names is. Or is there a better way to make sure all spreadsheets get processed while also ensuring that none get processed twice?

My thought was to use a series of append queries, each getting specific data into specific Access tables, and then have a macro or small piece of code that runs these queries one after another, processing all of them for one spreadsheet, then moving on to the next spreadsheet are repeating the process. And stopping when there are no more spreadsheets to process.

I especially do not want to use the new process in Access as of the 2007 version that allows you to import data (an option in the ribbon) and then memorize the transaction. In my experience, you cannot edit these memorized imports. I do not want to rely on any processes that cannot be easily edited.
Reply With Quote
  #3 (permalink)  
Old July 5th, 2012, 02:37 AM
Authorized User
Points: 380, Level: 6
Points: 380, Level: 6 Points: 380, Level: 6 Points: 380, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Hi,

There is absolutely no way of avoiding using VBA to do this!

The option of using a pre-processed and processed folder to control which spreadsheets have been processed sounds like the best idea.

I assume you only have one type of spreadsheet (data layout is same for all spreadsheets) that needs to be processed?

Once you link to one spreadsheet and set up the append queries to do what you require, then you can use VBA to loop through all the files in a particular directory and refresh the link to the next spreadsheet in the directory. Here's code to loop through a directory, http://allenbrowne.com/ser-59.html, for each file you can update the TableDef object of the linked table to point to it, using the Connect method and RefreshLink method. Then run all the append queries required.

Are you very familiar with VBA or do you need someone to code this for you?

Malc.
Reply With Quote
  #4 (permalink)  
Old July 13th, 2012, 11:51 AM
Authorized User
Points: 308, Level: 6
Points: 308, Level: 6 Points: 308, Level: 6 Points: 308, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The code offered below seems to be to create a list of files in a directory. I am assuming that this may be part of the process, to get a list of file names into an array and then loop through those names in calling up files. But I have to confess that is above my skill level.

Let my try to restate what I see as a two part problem.

I need to do two things in VBA that I do not really have a clue how to do and am hoping for specific advice.

Fist, I need to use maybe the DIR function to loop through a number of Excel files, perform actions from them one by one, then end the process when each file has been processed.

Second, when the above is completed, I need to move all files in that defined directory to a different one.

I am comfortable in a lot of areas of VBA but I am no expert in it. These two tasks are ones I have never encountered before.
Reply With Quote
  #5 (permalink)  
Old July 13th, 2012, 08:53 PM
Authorized User
Points: 380, Level: 6
Points: 380, Level: 6 Points: 380, Level: 6 Points: 380, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Hi,

Here's some code to iterate through Excel 2010 files, you would need to add your process coding where indicated. You WILL need to add error handling too in case of file handling errors and set the result to false so that the calling code can take appropriate action.

Code:
Public Function Process(sourceFolder As String, destinationFolder As String) As Boolean
    
    'pre-conditions: folders have \ character as last character
    'post-conditions: processes each Excel spreadsheet in the source folder and moves it to the destination folder
    
    Dim filename As String
    Dim sourceFile As String
    Dim result As Boolean
    
    result = True
    
    filename = Dir(sourceFolder & "*.xlsx")

    'process file if exists
    While filename <> ""
        'DO PROCESSING - code here to run append queries etc.
            
        'for this test assume processing successful, but you'll need to build in some sort of checks
        'copy file to destination folder
        sourceFile = sourceFolder & filename
        FileCopy sourceFile, destinationFolder & filename
        'delete the source file
        Kill sourceFile
        'get next file
        filename = Dir()
    Wend
    
    Process = result
    
End Function
HTH.

Malc.
Reply With Quote
  #6 (permalink)  
Old August 13th, 2012, 09:06 PM
Authorized User
Points: 308, Level: 6
Points: 308, Level: 6 Points: 308, Level: 6 Points: 308, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you. I am busy at the moment trying to debug the append queries. I linked all the appropriate Excel tabs into Access so that I could begin the append queries, knowing that even if I end up importing the tables, I can still use the queries as long as the table and field names stay the same.

I am quickly learning about issues with linked tables. I have one field not appending because of "a type conversion failure." It appears that a number field I had set up in access as Integer is coming across from Excel as Double. (the field typically will take 5 digit numbers with no decimals, but I cannot guarantee that will be true over time). I apparently cannot change the type being generated in the link from Excel and cannot change the type in Access properties because of the link.

Thinking out loud here, even if I switched to imported tables rather than linked, I still could not reasonable change the field types in these tables, because every successive set of imports would require a new round of changing field properties.

I think the only option is to see how the fields will come in from Excel by default and make the destination tables for the append queries match. And pray that something does not change the field types in the tables from excel do not change because of a minor change in the data.
Reply With Quote
  #7 (permalink)  
Old August 14th, 2012, 03:46 AM
Authorized User
Points: 380, Level: 6
Points: 380, Level: 6 Points: 380, Level: 6 Points: 380, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Hi,

You could always use the CInt() function to convert the value to an Integer within the append query!
Reply With Quote
  #8 (permalink)  
Old August 14th, 2012, 05:11 AM
Authorized User
Points: 380, Level: 6
Points: 380, Level: 6 Points: 380, Level: 6 Points: 380, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Hi,

Here's the code I promised.

Code:
Public Function Process(sourceFolder As String, destinationFolder As String) As Boolean
    
    'pre-conditions: folders have \ character as last character
    'post-conditions: processes each Excel spreadsheet in the source folder and moves it to the destination folder
    
    Dim filename As String
    Dim sourceFile As String
    Const excelTable As String = "ExcelData" 'Name given to linked table
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim result As Boolean
    
    On Error GoTo HandleError

    result = True
    
    filename = Dir(sourceFolder & "*.xlsx")

    Set dbs = myApp.CurrentDbs
    'process file if exists
    While filename <> ""
        'Create a linked table to the Excel named range
        sourceFile = sourceFolder & filename
        Set tdf = dbs.CreateTableDef(excelTable)
        tdf.Connect = "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & sourceFile
        tdf.SourceTableName = "NamedRange"
        dbs.TableDefs.Append tdf
        dbs.TableDefs.Refresh
        
        'DO PROCESSING - code here to run append queries etc.
        Set rst = dbs.TableDefs("ExcelData").OpenRecordset
        
        While Not rst.EOF
            MsgBox rst.Fields(1)
            rst.MoveNext
        Wend
        rst.Close
        Set rst = Nothing
        dbs.TableDefs.Delete (excelTable)
        Set tdf = Nothing
        'for this test assume processing successful, but you'll need to build in some sort of checks
        'copy file to destination folder
        FileCopy sourceFile, destinationFolder & filename
        'delete the source file
        Kill sourceFile
        'get next file
        filename = Dir()
    Wend

ExitProc:
    On Error Resume Next
    Process = result
    Set dbs = Nothing
    Exit Function

HandleError:
    DisplayErrorMessage "Process", Err.Number, Err.Description
    CleanUpAfterError
    Resume ExitProc
    result = False
    Resume
    
End Function
This will loop through Excel 2010 files in a folder and create a linked table to a named range "NamedRange". I'm just opening a recordset and displaying the 2nd field as a test. This works fine but I think you will need some explicit error checking if the TableDef object already exists during the start of the function. You may want to log each "import" by storing the Excel file name and date processed in an import log table.

HTH.

Malc.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple queries one form jeremy1048 Access 4 November 13th, 2007 12:45 PM
Extracting data from excel spreadsheets Neil1234567 Access VBA 2 October 16th, 2007 07:28 AM
Import spreadsheets for Sarbanes-Oxley reporting makkerc Excel VBA 6 August 29th, 2007 08:03 AM
Extracting data from Spreadsheets in a folder IainAL VB How-To 2 April 20th, 2007 02:57 AM
Import Multiple Unknown Excel Spreadsheets in .Net bcarmen Excel VBA 1 January 31st, 2005 04:46 PM



All times are GMT -4. The time now is 01:12 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.