I'm by far no programmer, but I have a nasty problem that
I think can only be over-come with some serious
programming.

I need to pull data from a HUGE number of Excel files -
2800 and counting.

Here are my problems:
- The files are in a defined directory structure that
will change and expand over time.
- The number of files will continue to expand over time.
- The length of the files will fluctuate over time.
- There are blank rows intermitently through out the data
I need to retrieve.
- The data I need does not start at cell A1.
- The data I need might not consistently start on the
same row.
- I need to introduce either the name of the file or the
name of the sub-directory into the data set.
- I will ultimately be sending all of this to Access.

I had access to a programmer that was able to dump the
directory structure to an array and then read some
specific cell values into a table in Access, but I've
lost contact with him.

Can anyone help?

Hi,
Are the workbooks named sequentially, eg: Hmm1, Hmm2
Hmm3 ... so I can cycle through each of them. Or do they
have odd names like thisfile, thatThing, Dooby, etc?

Are they found in the same folder or are they in different
folders?

Are the in a different directory or Drive Letter?

Is there more than one sheet in each workbook or does it
vary per workbook?

Is there a reference to use to find the Row/Column cell
value ie: if cell.value="Library" then


If you can answer any of the above questions, I might be
able to answer a few of yours. You might be able to access
the excel files directly into Access or, if not a VB6
Userform, or Excel itself.

visit
http://au.geocities.com/excelmarksway
http://au.geocities.com/windsofmark

I am not too familiar with Access but have some user
knowledge of VB6 and Excel Userforms.

Send a sample sheet with a scenario you have in mind.

regards
Mark E. Philpot



It's free until I say its not.


-----Original Message-----
I'm by far no programmer, but I have a nasty problem that
I think can only be over-come with some serious
programming.

I need to pull data from a HUGE number of Excel files -
2800 and counting.

Here are my problems:
- The files are in a defined directory structure that
will change and expand over time.
- The number of files will continue to expand over time.
- The length of the files will fluctuate over time.
- There are blank rows intermitently through out the data
I need to retrieve.
- The data I need does not start at cell A1.
- The data I need might not consistently start on the
same row.
- I need to introduce either the name of the file or the
name of the sub-directory into the data set.
- I will ultimately be sending all of this to Access.

I had access to a programmer that was able to dump the
directory structure to an array and then read some
specific cell values into a table in Access, but I've
lost contact with him.

Can anyone help?
.

"Doug Shannon" wrote ...

I'm by far no programmer, but I have a nasty problem that
I think can only be over-come with some serious
programming.

I need to pull data from a HUGE number of Excel files -
2800 and counting.

Here are my problems:
- The files are in a defined directory structure that
will change and expand over time.
- The number of files will continue to expand over time.
- The length of the files will fluctuate over time.
- There are blank rows intermitently through out the data
I need to retrieve.
- The data I need does not start at cell A1.
- The data I need might not consistently start on the
same row.
- I need to introduce either the name of the file or the
name of the sub-directory into the data set.
- I will ultimately be sending all of this to Access.

Here's something that demonstrates using a *general* SQL query to
fetch Excel data from varing locations on a worksheet:

1. Create a new blank Excel workbook.
2. Name one of the worksheets MAIN.
3. Add a standard module to the workbook and paste in the following
code:

Option Explicit

Sub Test()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Target As Excel.Range
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String
Dim lngCounter As Long

' Amend the following constants to suit
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"

Const DATA_HAS_HEADERS As Boolean = False

Const TABLE_NAME_CURRENT As String = "" & _
"MAIN"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=<HEADERS'"

' Build connection strings
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH", strPath)
strCon = Replace(strCon, _
"<FILENAME", FILENAME_XL_TEMP)
strCon = Replace(strCon, _
"<HEADERS", IIf(DATA_HAS_HEADERS, "YES", "NO"))

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "SELECT * FROM "
strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add
With wb
ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _
Copy .Worksheets(1)
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.Open
Set rs = .Execute(strSql1)
End With

Set ws = ThisWorkbook.Worksheets.Add
With ws
Set Target = .Range("A1")
End With

With rs
For lngCounter = 1 To .fields.Count
Target(1, lngCounter).Value = _
.fields(lngCounter - 1).Name
Next
End With

Target(2, 1).CopyFromRecordset rs

Con.Close

End Sub

4. Review the constants in the code (you may not need to change them).
5. Save the workbook.
6. Enter some test data into the MAIN sheet e.g. (in the Immediate
Window)

Range("C3").Value = "MyCol1"
Range("D3").Value = "MyCol2"
Range("C4").Value = "1"
Range("D5").Value = "2"
Range("E8").Value = "3"

7. Run the Test macro.

A new worksheet is created based on the query

SELECT * FROM [Main$]

Experimenting with different data and the DATA_HAS_HEADERS column will
give you an idea how the situations you described ('There are blank
rows intermitently through out the data', 'The data I need might not
consistently start on the same row' etc) may be handled. If you are
feeling brave <g you can change the SQL e.g. to use a cell address:

SELECT * FROM [Main$C3:IV5536]

Using this query with the above example data and settings still
returns five rows of three columns. A defined name ('named range') can
also be used as the table name e.g.

SELECT MyCol1 FROM [MyBookLevelName];

SELECT RefID FROM [Sheet1$MySheetLevelName];

Jamie.

--

-----Original Message-----
Hi,
Are the workbooks named sequentially, eg: Hmm1, Hmm2
Hmm3 ... so I can cycle through each of them. Or do they
have odd names like thisfile, thatThing, Dooby, etc?

The name changes with each file, it does (mostly) follow
a defined format, but there is no incremental link.

Are they found in the same folder or are they in
different folders?

Different sub-folders of a main folder.

Are they in a different directory or Drive Letter?

Yes, they are on a server and the application will be
stored on a seperate server.

Is there more than one sheet in each workbook or does it
vary per workbook?

No, there should only be one sheet in each workbook.

Is there a reference to use to find the Row/Column cell
value ie: if cell.value="Library" then

Yes, there is a reference value that acts as a column
header within the workbook, but it's not in the first row
and rarely in the first column.

If you can answer any of the above questions, I might be
able to answer a few of yours. You might be able to
access the excel files directly into Access or, if not a
VB6 Userform, or Excel itself.

visit
http://au.geocities.com/excelmarksway
http://au.geocities.com/windsofmark

I will, thanks.

I am not too familiar with Access but have some user
knowledge of VB6 and Excel Userforms.

I hadn't heard of Userforms...
How could they help me?

Send a sample sheet with a scenario you have in mind.

Sent privately.

regards
Mark E. Philpot



It's free until I say its not.