Searching Multiple Excel Files & Printing Report in MS Access-Is this possible?

[Stephenoja]

Hello Guys,

I have a challenge here and would really appreciate some help. I have customer bills that are all done in excel with a template and stored in directories by month order. At the end of the month or periodically, I need to query and print a report giving customer name, account number, date of service and cost for a particular service/item for all customers who received such a service/item (at times up to 300 customer bills in excel format will be present in the directory). Is it possible to write a script either in access or excel that will search all the files in the directory and prepare such a report? Searching each individual file and having to type or cut and paste the details each time the report is required is quite tedious. This information is stored in the same cell in each bill as a template is used. Does anyone have any idea how to work around this?

Stephen

[sierra7]

Hi Stephan

Nobody seems to have answered this so I am replying to say there is somebody out here!

I understand from your description that you have a separate worksheet for each customer bill. This is a nightmare scenario (which is probably why nobody has replied!), so my first suggestion is why don't you write a new app in Access and start over!?!

Nevertheless, just listing the steps I would take to build such an app;-
You need a database with at least one table to recieve the data.
You will need a form which needs a field for the location of the latest folder, assuming you can enter this at the end of a month and then click to run a procedure to import the data. You could add a Common Dialog box to help browse and find this folder.
You need to write a procedure which will loop through the files in the folder. As a start I would just display each file name in a message box to ensure they were being read. You can use the Dir() function recursively (loop untill Dir = "", read Help on Dir Function) and it will find the next file, next file etc..
Once I was sure I was finding each file I would try opening it. The following 'snippets' could probably be used in your program

Dim xcel As Object
    Set xcel = CreateObject("Excel.Application")
 
'Open spreadsheet, 
    xcel.workbooks.Open "NextFileName"
    xcel.Visible = False
 
'Select Sheet    
    xcel.Sheets("Sheet1").Select
 
' Read data from cells into fields of recorset
    !CustName = xcel.Range("A2").Value
    !AccNo = xcel.Range("C3").Value
    !ODate = xcel.Range("D4").Value
    'etc

NextFileName would be the full path + filename including .wks to the current spreadsheet.
You would need to setup a recordset outside the loop and open it so you can write data to the fields you require. (I have not shown setting up the recordset but !CustName is obiously the field for the Customer Name in the recordset you are using)
"A2","C3", "D4" are just my arbitary names for the cells in the spreadsheet template that hold the data you require to extract.

I have not attempted to write the loops, within loops, within loops that are needed for this exercise but having given it a bit of thought it is quite 'do-able' (which is more than I thought at the begining)

I hope this helps a little !

S7

[Stephenoja]

Thanks Sierra. Its quite a tall order but I'm trying it out. Having loads of difficulties but I think I am getting thorough. Yes it would have really been better in access but these are files that were already created in microsof excel for the past six years so this confusion is all i have to work with.

[ADezii]

The following code, although not Optimized, will do exactly what you are requesting, except for the simple part of running a Query against the final Table results (tblCustomerInf o). It actually works quite well on several Excel Spreadsheets but I have no idea how it will perform on 300+. Rather than going into prolonged explanations, I'll simply post the code along with some assumptions, and should you have any questions, please feel free to ask. These assumptions are critical, follow them exactly:

1. Assumptions
   1. All your Excel Spreadsheets reside in the C:\Customers Directory as in C:\Customers\Cu stomer John Doe.xls.
   2. In each Spreadsheet, the data resides in a consistent location, in this case Customer Name is in Cell "A1", Account Number is in Cell "B1", and Order_Date is in "C1".
   3. tblCustomerInfo (create it) with Fields [Name], [Account#], and [Order_Date].

Dim strPath As String, appExcel As Excel.Application, strFolderPath As String
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
 
DoCmd.SetWarnings False
  strSQL = "Delete * From tblCustomerInfo;"
  DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblCustomerInfo", dbOpenDynaset)
 
strFolderPath = "C:\Customers\"
strPath = "C:\Customers\*.xls"     'Set the path.
 
strPath = Dir(strPath, vbNormal)   'Retrieve the first entry.
Set appExcel = CreateObject("Excel.Application")
 
Do While strPath <> ""    'Initiate the loop
  appExcel.Workbooks.Open strFolderPath & strPath
  appExcel.Visible = False
  appExcel.Sheets("Sheet1").Select
    With MyRS
      .AddNew
        !Name = appExcel.Range("A1").Value
        ![Account#] = appExcel.Range("B1").Value
        ![Order_Date] = appExcel.Range("C1").Value
      .Update
    End With
strPath = Dir           'Next entry
Loop
 
appExcel.Quit
Set appExcel = Nothing
 
MyRS.Close
Set MyRS = Nothing
 
MsgBox "This loooooong process has completed!"
 

SAMPLE OUTPUT (tblCustomerInf o):

ID    Name            Account#       Order_Date
10    Customer 1    123467         1/23/2008
11    Customer 2    91023556Y55     4/23/2008
12    Customer 3    HGT5543FR     11/29/2008
 

[ADezii]

For my own curiosity, and to further assist you, I ran my code on 3 separate trials against 50 Excel Spreadsheets, the results are as follows:

145.10 seconds ==> 2.42 minutes
144.03 seconds ==> 2.40 minutes
151.03 seconds ==> 2.52 seconds
 
Average Process Time for 3 Trials : 2.45 minutes

The code ran without a hitch. On 300+ Spreadsheets, I would start the process go out to lunch, and when you returned it should be finished. If you are going to use this approach, let me know since there are a few things that I would like to point out, and the code itself can probably be improved.

[sierra7]

Nice one ADenzii !

What have you got loaded in References to allow you to

 Dim appExcel As Excel.Application

I have to create OBJECT then set the Object to Excel.Applicati on as two lines.

S7

[ADezii]

Thanks sierra7, the Reference is to:

Microsoft Excel XX.X Object Library

[sierra7]

Duh!!
Thanks! I had a reference to Office loaded which obviously didn't work.
S7