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

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers

 
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

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

Quote:

Originally Posted by 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

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 (tblCustomerInfo). 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:

Assumptions
1. All your Excel Spreadsheets reside in the C:\Customers Directory as in C:\Customers\Customer 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].

Expand|Select|Wrap|Line Numbers

 
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 (tblCustomerInfo):

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by 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

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:

Expand|Select|Wrap|Line Numbers

 
10 seconds ==> 2.42 minutes

03 seconds ==> 2.40 minutes

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

Expand|Select|Wrap|Line Numbers

Dim appExcel As Excel.Application

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

S7

ADezii

Quote:

Originally Posted by sierra7

Nice one ADenzii !

What have you got loaded in References to allow you to

Expand|Select|Wrap|Line Numbers

Dim appExcel As Excel.Application

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

S7

Thanks sierra7, the Reference is to:

Expand|Select|Wrap|Line Numbers

Microsoft Excel XX.X Object Library

sierra7

Quote:

Originally Posted by ADezii

Thanks sierra7, the Reference is to:

Expand|Select|Wrap|Line Numbers

Microsoft Excel XX.X Object Library

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

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

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

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

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

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

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

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

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

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about