Conditional import of Excel sheets

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hi, maybe someone can help me with the following: on a weekly basis I
need to import 3 Excel files into an existing empty table (I run a
delete query on this table before the import). The Excel files are
stored in the same folder every week. Each file has a field showing
[Current Year/Week No]. The three files show the current week for
this
year, previous year and the year before the previous year. How can
you
instruct MS Access (2003) to look up this field and to import only
the
approrpiate files (i.e. the files that refer to the reporting week)? I
do have
a calender function from which the user can select the date (and
deduct
the 2 previous dates). I should be able to incorporate that into the
code
(with a little help...).
Any ideas? Let me know if you need more info, thanks a million, Paul
 
Are there more than 3 files in the folder? Is your problem finding the
correct files among hundreds, or just reading the year/week value? Or both?
 
Are there more than 3 files in the folder? Is your problem finding the
correct files among hundreds, or just reading the year/week value? Or both?
Click to expand...

Hi Lance, thanks for your reply. The folder will be filled with a
total of 8 files every week. They are all appended into separate
tables (apart from these 3). I intend to clean up this folder once
every while, so we're talking about maybe 50 files max.
 
OK try this out, you may need to add some references for ADO and microsoft
scripting. Also, it always treats the excel spreadsheet as having the first
line as field names, regardless of what you put in. It seems to add 1 to
your cell reference line. Sorry, I didn't have the time to figure that out..
but would love to know if you do.

I ran it through a group of 10 excel worksheets in a folder and looked for
the word "ODD" in cell A2 ( entered in code as A1, see above for error ) and
it worked well enough.





Sub check_excel_files()
Dim fsoSysObj As Scripting.FileSystemObject
Dim fdrFolder As Scripting.Folder
Dim filFile As Scripting.File
Dim strPath As String

strPath = "C:\Projects\LIBRARY"
Set fsoSysObj = New Scripting.FileSystemObject
Set fdrFolder = fsoSysObj.GetFolder(strPath)

For Each filFile In fdrFolder.Files
If filFile.Type = "Microsoft Excel Worksheet" Then

'*****************************************************
'* INSERT CODE TO CHECK EXCEL VALUE HERE
'*****************************************************
If x_value_from_excel(filFile.Path, "Sheet1", "A", 1, 0) = "ODD" Then
MsgBox filFile.Path & " is an odd numbered file."
End If
'******************************************************

End If
Next filFile




Set fsoSysObj = Nothing
Set fdrFolder = Nothing

End Sub

Function x_value_from_excel(inFile As String, inWorkSheet As String, inCol
As String, inRow As Integer, inFirstRowHasNames As Integer)

On Error GoTo handler
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

strquery = "SELECT * FROM [" & inWorkSheet & "$" & inCol & inRow & ":" &
inCol & inRow + 1 & "]"

With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" &
inFile & "; ReadOnly=False;FirstRowHasNames=" & inFirstRowHasNames & ";"
.Open
End With

Call rs.Open(strquery, cn)
x_value_from_excel = rs(0)

cn.Close
Exit Function
handler:
MsgBox Err.Description
x_value_from_excel = "ERROR"
End Function
 
I have to say, Lance, this goes somewhat over my head...to put it
mildly. I do appreciate your time on this, though, but somehow I feel
this is getting too complicated (for me anyway). I have been thinking
this over (thanks to you and another expert) and I figured that the
files I do not need all have in common that they were downloaded on a
different date than the files I do need, which will always be
downloaded the same day. Could I use the date criteria somehow as a
condition to import or not? If possible, that seems easier to me.
Thanks again for your time. Paul
 
That's something I thought about putting in the script I posted before. And
it's certainly doable, but you'll still have to loop through files and
extract the datecreated value from it. But that would allow you to skip
reading the excel file.

But let's step back from that for a second, maybe there's an easier solution.

How are these files generated? Is it an automatic machine generated file
with a machine generated name? If so is there some pattern to the naming
convention being used?

If it's a human generated, is it possible to just have the file saved as the
same name each time?
 
The files are downloaded from an external source and the naming logic
includes their internal request number, which makes it impossible to
foresee. The files are manually uploaded and then saved to the
directory, but to install a naming convention there woudl be error
prone, because I'm not the only one that runs the reports (in fact, I
designed it so that other could run it as well) and I am the only one
with a little Access knowledge.
 
If you go with the "static file names" it wouldn't be difficult to check if
the files have current data before processing them.. for what that's worth.

The only other options I see is to make a form which prompts users to choose
the files they upload.. and then make running that form part of the file
creation process. A lot less automated though.
 
So maybe I can let the user choose himself and thèn do a date
validation. That would enforce awareness, prevent mistakes and
overall, it will not stall tohe process too much. Thanks, Lance, for
your ideas and time. Much appreciated, Paul
 
Paul:
i don't understand your RE: to my ? about an Epson pinter color II model
P880A: it was giving to me and the first time printed OK, but after that
prints # and crazy characters as long as there is paper in the printer.tia
 
Back
Top