Complex Data Import

[small brother]

Dear All

I need your help to make this import successuful.

I have arround 150 excel files that need to be imported into access.

The files are named "Report dd/mm/yyyy.xls"
The columns are Code-Name-Balance-Employee
First i will need to add a column date where i put the date included in the
file name.
Second i will need to remove the subtotals (all the files contain a subtotal
by employee).
Third i will need to import them to Access in one table.

Thanks for your help

 

[Klatuu]

You have a lot of coding to do.

You can use the Dir function in a loop to get all the files in the directory.

To remove the subtotals, you will have to use automation to open the
spreadsheet from Access and remove the subtotals. This statement does it,
but you need to code in your own range:

xlSheet.Range("A:S").RemoveSubtotal

To add the date from the file name as a column you can create an update
query that will add the date column.

 

[Dale Fye]

I disagree with Dave, this may not be a whole lot of code.

First, I would create a temporary table to use to do the initial import of
your data. This table should contain the 4 fields you describe in your
original post, except that you can see I have changed [NAME] to [NameField].
Name is an Access reserved word, and should not be used as a field name.

What does your Subtotal line look like in the spreadsheet? Give us a sample
of a couple of records and the SubTotals row. Hopefully, the subtotal rows
will have a value or missing value that is in all of the other rows.

This is untested air code, and I have not filled in all the blanks, but it
should give you an idea of how I would approach this.

Public Function ImportExcelFiles

Dim strFileName as String
Dim strFileDate as String
Dim strSQL as string

'Delete records from your temp table
currentdb.execute "DELETE * FROM tbl_Temp"

strFileName = DIR(currentproiect.path & "\*.xls")
Do while len(strFileName) > 0

'Check to make sure the Excel filename is structured
'the way you describe below.
If instr(strFileName, "Report") <> 1 then
'do nothing with this file
debug.print "File " & strFileName & " was not imported " _
& "(missing 'Report' at beginning of file
name)!"
Else
'Define the date portion of the filename
strFileDate = Trim(mid(strFileName, 7))
strFileDate = Left(strFileName, len(strFileName) - 4)
if not isdate(strFileDate) then
'do nothing, wrong file type
debug.print "File " & strFileName _
& " was not imported (bad date
format)!"
else
'Do your processing here
'Import records into the temp table (fill in the
parameters as appropriate
Docmd.Transferspreadsheet ....

'Delete the subtotal rows from the temp table (I'd
write and
'save a Delete query and just execute that query
Currentdb.Querydefs("qry_DeleteSubtotals").Execute

'Insert the records into your main table
strSQL = "INSERT INTO tbl_Main (FileDate, Code, " _
&
"NameField, Balance, " _
&
"Employee) " _
& "SELECT #" & cdate(strFileDate) & "#, "
_
& "Code,
NameField, Balance, Employee " _
& "FROM tbl_Temp"
currentdb.execute strsql

'Delete records from temp table
currentdb.execute "DELETE * FROM tbl_Temp"
End if
End if

'You might want to insert some code here to either delete the
processed files, or move them to a backup folder where they could be reused
if necessary.

'Get the next filename provided by the DIR function
strFileName = DIR()

Loop

End Sub

HTH
Dale

 

[Klatuu]

Explain to me how you would identify the subtotals row? There is no
assurance that it would look any different than any other row.
Other than that, I like your idea.
--
Dave Hargis, Microsoft Access MVP

I disagree with Dave, this may not be a whole lot of code.

First, I would create a temporary table to use to do the initial import of
your data. This table should contain the 4 fields you describe in your
original post, except that you can see I have changed [NAME] to [NameField].
Name is an Access reserved word, and should not be used as a field name.

What does your Subtotal line look like in the spreadsheet? Give us a sample
of a couple of records and the SubTotals row. Hopefully, the subtotal rows
will have a value or missing value that is in all of the other rows.

This is untested air code, and I have not filled in all the blanks, but it
should give you an idea of how I would approach this.

Public Function ImportExcelFiles

Dim strFileName as String
Dim strFileDate as String
Dim strSQL as string

'Delete records from your temp table
currentdb.execute "DELETE * FROM tbl_Temp"

strFileName = DIR(currentproiect.path & "\*.xls")
Do while len(strFileName) > 0

'Check to make sure the Excel filename is structured
'the way you describe below.
If instr(strFileName, "Report") <> 1 then
'do nothing with this file
debug.print "File " & strFileName & " was not imported " _
& "(missing 'Report' at beginning of file
name)!"
Else
'Define the date portion of the filename
strFileDate = Trim(mid(strFileName, 7))
strFileDate = Left(strFileName, len(strFileName) - 4)
if not isdate(strFileDate) then
'do nothing, wrong file type
debug.print "File " & strFileName _
& " was not imported (bad date
format)!"
else
'Do your processing here
'Import records into the temp table (fill in the
parameters as appropriate
Docmd.Transferspreadsheet ....

'Delete the subtotal rows from the temp table (I'd
write and
'save a Delete query and just execute that query
Currentdb.Querydefs("qry_DeleteSubtotals").Execute

'Insert the records into your main table
strSQL = "INSERT INTO tbl_Main (FileDate, Code, " _
&
"NameField, Balance, " _
&
"Employee) " _
& "SELECT #" & cdate(strFileDate) & "#, "
_
& "Code,
NameField, Balance, Employee " _
& "FROM tbl_Temp"
currentdb.execute strsql

'Delete records from temp table
currentdb.execute "DELETE * FROM tbl_Temp"
End if
End if

'You might want to insert some code here to either delete the
processed files, or move them to a backup folder where they could be reused
if necessary.

'Get the next filename provided by the DIR function
strFileName = DIR()

Loop

End Sub

HTH
Dale

Dear All

I need your help to make this import successuful.

I have arround 150 excel files that need to be imported into access.

The files are named "Report dd/mm/yyyy.xls"
The columns are Code-Name-Balance-Employee
First i will need to add a column date where i put the date included in
the
file name.
Second i will need to remove the subtotals (all the files contain a
subtotal
by employee).
Third i will need to import them to Access in one table.

Thanks for your help

 

[Dale Fye]

That was why I asked what his sub-totals row looks like.

In most of the Excel spreadsheets that I have worked with, they either:

1. label the row ("Total", "SubTotal")
2. leave the cell in column A blank, while the cells above and below all
have data
3. have an empty row immediately following it (in which case we would have
to delete two records for each employee)

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Explain to me how you would identify the subtotals row? There is no
assurance that it would look any different than any other row.
Other than that, I like your idea.
--
Dave Hargis, Microsoft Access MVP

I disagree with Dave, this may not be a whole lot of code.

First, I would create a temporary table to use to do the initial import of
your data. This table should contain the 4 fields you describe in your
original post, except that you can see I have changed [NAME] to [NameField].
Name is an Access reserved word, and should not be used as a field name.

What does your Subtotal line look like in the spreadsheet? Give us a sample
of a couple of records and the SubTotals row. Hopefully, the subtotal rows
will have a value or missing value that is in all of the other rows.

This is untested air code, and I have not filled in all the blanks, but it
should give you an idea of how I would approach this.

Public Function ImportExcelFiles

Dim strFileName as String
Dim strFileDate as String
Dim strSQL as string

'Delete records from your temp table
currentdb.execute "DELETE * FROM tbl_Temp"

strFileName = DIR(currentproiect.path & "\*.xls")
Do while len(strFileName) > 0

'Check to make sure the Excel filename is structured
'the way you describe below.
If instr(strFileName, "Report") <> 1 then
'do nothing with this file
debug.print "File " & strFileName & " was not imported " _
& "(missing 'Report' at beginning of file
name)!"
Else
'Define the date portion of the filename
strFileDate = Trim(mid(strFileName, 7))
strFileDate = Left(strFileName, len(strFileName) - 4)
if not isdate(strFileDate) then
'do nothing, wrong file type
debug.print "File " & strFileName _
& " was not imported (bad date
format)!"
else
'Do your processing here
'Import records into the temp table (fill in the
parameters as appropriate
Docmd.Transferspreadsheet ....

'Delete the subtotal rows from the temp table (I'd
write and
'save a Delete query and just execute that query
Currentdb.Querydefs("qry_DeleteSubtotals").Execute

'Insert the records into your main table
strSQL = "INSERT INTO tbl_Main (FileDate, Code, " _
&
"NameField, Balance, " _
&
"Employee) " _
& "SELECT #" & cdate(strFileDate) & "#, "
_
& "Code,
NameField, Balance, Employee " _
& "FROM tbl_Temp"
currentdb.execute strsql

'Delete records from temp table
currentdb.execute "DELETE * FROM tbl_Temp"
End if
End if

'You might want to insert some code here to either delete the
processed files, or move them to a backup folder where they could be reused
if necessary.

'Get the next filename provided by the DIR function
strFileName = DIR()

Loop

End Sub

HTH
Dale

Dear All

I need your help to make this import successuful.

I have arround 150 excel files that need to be imported into access.

The files are named "Report dd/mm/yyyy.xls"
The columns are Code-Name-Balance-Employee
First i will need to add a column date where i put the date included in
the
file name.
Second i will need to remove the subtotals (all the files contain a
subtotal
by employee).
Third i will need to import them to Access in one table.

Thanks for your help

 

[Klatuu]

Yes, I think that would work. That is what I was thinking.

It should may have Sub Total in multiple rows and Total in another, and some
blank lines, so a Delete Query might work for this:

DELETE * FROM SomeTable WHERE Instr([SomeField], "Total") > 0;

To remove the blank lines, if there is a column that will always have a
value, it could be include in the query:

DELETE * FROM SomeTable WHERE Instr([SomeField], "Total") > 0 OR [GoodField]
IS NULL;

--
Dave Hargis, Microsoft Access MVP

That was why I asked what his sub-totals row looks like.

In most of the Excel spreadsheets that I have worked with, they either:

1. label the row ("Total", "SubTotal")
2. leave the cell in column A blank, while the cells above and below all
have data
3. have an empty row immediately following it (in which case we would have
to delete two records for each employee)

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Explain to me how you would identify the subtotals row? There is no
assurance that it would look any different than any other row.
Other than that, I like your idea.
--
Dave Hargis, Microsoft Access MVP

I disagree with Dave, this may not be a whole lot of code.

First, I would create a temporary table to use to do the initial import of
your data. This table should contain the 4 fields you describe in your
original post, except that you can see I have changed [NAME] to [NameField].
Name is an Access reserved word, and should not be used as a field name.

What does your Subtotal line look like in the spreadsheet? Give us a sample
of a couple of records and the SubTotals row. Hopefully, the subtotal rows
will have a value or missing value that is in all of the other rows.

This is untested air code, and I have not filled in all the blanks, but it
should give you an idea of how I would approach this.

Public Function ImportExcelFiles

Dim strFileName as String
Dim strFileDate as String
Dim strSQL as string

'Delete records from your temp table
currentdb.execute "DELETE * FROM tbl_Temp"

strFileName = DIR(currentproiect.path & "\*.xls")
Do while len(strFileName) > 0

'Check to make sure the Excel filename is structured
'the way you describe below.
If instr(strFileName, "Report") <> 1 then
'do nothing with this file
debug.print "File " & strFileName & " was not imported " _
& "(missing 'Report' at beginning of file
name)!"
Else
'Define the date portion of the filename
strFileDate = Trim(mid(strFileName, 7))
strFileDate = Left(strFileName, len(strFileName) - 4)
if not isdate(strFileDate) then
'do nothing, wrong file type
debug.print "File " & strFileName _
& " was not imported (bad date
format)!"
else
'Do your processing here
'Import records into the temp table (fill in the
parameters as appropriate
Docmd.Transferspreadsheet ....

'Delete the subtotal rows from the temp table (I'd
write and
'save a Delete query and just execute that query
Currentdb.Querydefs("qry_DeleteSubtotals").Execute

'Insert the records into your main table
strSQL = "INSERT INTO tbl_Main (FileDate, Code, " _
&
"NameField, Balance, " _
&
"Employee) " _
& "SELECT #" & cdate(strFileDate) & "#, "
_
& "Code,
NameField, Balance, Employee " _
& "FROM tbl_Temp"
currentdb.execute strsql

'Delete records from temp table
currentdb.execute "DELETE * FROM tbl_Temp"
End if
End if

'You might want to insert some code here to either delete the
processed files, or move them to a backup folder where they could be reused
if necessary.

'Get the next filename provided by the DIR function
strFileName = DIR()

Loop

End Sub

HTH
Dale

Dear All

I need your help to make this import successuful.

I have arround 150 excel files that need to be imported into access.

The files are named "Report dd/mm/yyyy.xls"
The columns are Code-Name-Balance-Employee
First i will need to add a column date where i put the date included in
the
file name.
Second i will need to remove the subtotals (all the files contain a
subtotal
by employee).
Third i will need to import them to Access in one table.

Thanks for your help

 

[small brother]

Thx Dale for your help
i have found the solution to this problem
it's similar to what you propose
here is the code:

Private Sub Command1_Click()
Dim objXL As Excel.Application
'Dim wbXL As Excel.Workbook
'Dim sheet As Excel.Worksheet
Dim strPath As String
Dim strFileName As String
'Dim rCount As Long, r As Long
Dim j As Long
Dim k As Long
Dim aa As String
Dim Rng As Range

Set objXL = CreateObject("Excel.Application")
strPath = "C:\IntCalc\Jan07\"
strFileName = Dir(strPath & "*.xls")


Do While Len(strFileName) <> 0
Excel.Workbooks.Open Filename:=strPath & strFileName
Workbooks(strFileName).Worksheets("Sheet1").Activate
ActiveSheet.AutoFilterMode = False
Worksheets("Sheet1").Range("A:M").RemoveSubtotal
Delete_Columns



For j = (Range("A65536").End(xlUp).Row) To 1 Step -1
If IsNumeric(Worksheets("Sheet1").Cells(j, 1).Value) = False Or
IsEmpty(Worksheets("Sheet1").Cells(j, 1)) = True Then
Worksheets("Sheet1").Rows(j).EntireRow.Delete
End If
Next j




L = (Range("A65536").End(xlUp).Row)
aa = "E" & L
Range("e1").Select
ActiveCell.FormulaR1C1 = Mid(strFileName, 13, 2) & "/" &
Mid(strFileName, 10, 2) & "/2007"
Range(aa).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown


DoCmd.TransferSpreadsheet acImport, , "Sheetimport", strPath &
strFileName, False

Workbooks(strFileName).Close SaveChanges:=True
strFileName = Dir()
Loop
objXL.Workbooks.Close
'strFileName = Dir
Excel.Workbooks.Application.Quit
Set objXL = Nothing

End Sub





Sub Delete_Columns()

Excel.Application.ScreenUpdating = False
Excel.Application.Calculation = xlCalculationManual

On Error GoTo Exits:
Worksheets("Sheet1").Columns(14).EntireColumn.Delete
Worksheets("Sheet1").Columns(13).EntireColumn.Delete
Worksheets("Sheet1").Columns(12).EntireColumn.Delete
Worksheets("Sheet1").Columns(11).EntireColumn.Delete
Worksheets("Sheet1").Columns(10).EntireColumn.Delete
Worksheets("Sheet1").Columns(8).EntireColumn.Delete
Worksheets("Sheet1").Columns(7).EntireColumn.Delete
Worksheets("Sheet1").Columns(6).EntireColumn.Delete
Worksheets("Sheet1").Columns(5).EntireColumn.Delete
Worksheets("Sheet1").Columns(3).EntireColumn.Delete

Exits:
Excel.Application.ScreenUpdating = True
Excel.Application.Calculation = xlCalculationAutomatic

End Sub

Yes, I think that would work. That is what I was thinking.

It should may have Sub Total in multiple rows and Total in another, and some
blank lines, so a Delete Query might work for this:

DELETE * FROM SomeTable WHERE Instr([SomeField], "Total") > 0;

To remove the blank lines, if there is a column that will always have a
value, it could be include in the query:

DELETE * FROM SomeTable WHERE Instr([SomeField], "Total") > 0 OR [GoodField]
IS NULL;

--
Dave Hargis, Microsoft Access MVP

That was why I asked what his sub-totals row looks like.

In most of the Excel spreadsheets that I have worked with, they either:

1. label the row ("Total", "SubTotal")
2. leave the cell in column A blank, while the cells above and below all
have data
3. have an empty row immediately following it (in which case we would have
to delete two records for each employee)

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Explain to me how you would identify the subtotals row? There is no
assurance that it would look any different than any other row.
Other than that, I like your idea.
--
Dave Hargis, Microsoft Access MVP


:

I disagree with Dave, this may not be a whole lot of code.

First, I would create a temporary table to use to do the initial import of
your data. This table should contain the 4 fields you describe in your
original post, except that you can see I have changed [NAME] to [NameField].
Name is an Access reserved word, and should not be used as a field name.

What does your Subtotal line look like in the spreadsheet? Give us a sample
of a couple of records and the SubTotals row. Hopefully, the subtotal rows
will have a value or missing value that is in all of the other rows.

This is untested air code, and I have not filled in all the blanks, but it
should give you an idea of how I would approach this.

Public Function ImportExcelFiles

Dim strFileName as String
Dim strFileDate as String
Dim strSQL as string

'Delete records from your temp table
currentdb.execute "DELETE * FROM tbl_Temp"

strFileName = DIR(currentproiect.path & "\*.xls")
Do while len(strFileName) > 0

'Check to make sure the Excel filename is structured
'the way you describe below.
If instr(strFileName, "Report") <> 1 then
'do nothing with this file
debug.print "File " & strFileName & " was not imported " _
& "(missing 'Report' at beginning of file
name)!"
Else
'Define the date portion of the filename
strFileDate = Trim(mid(strFileName, 7))
strFileDate = Left(strFileName, len(strFileName) - 4)
if not isdate(strFileDate) then
'do nothing, wrong file type
debug.print "File " & strFileName _
& " was not imported (bad date
format)!"
else
'Do your processing here
'Import records into the temp table (fill in the
parameters as appropriate
Docmd.Transferspreadsheet ....

'Delete the subtotal rows from the temp table (I'd
write and
'save a Delete query and just execute that query
Currentdb.Querydefs("qry_DeleteSubtotals").Execute

'Insert the records into your main table
strSQL = "INSERT INTO tbl_Main (FileDate, Code, " _
&
"NameField, Balance, " _
&
"Employee) " _
& "SELECT #" & cdate(strFileDate) & "#, "
_
& "Code,
NameField, Balance, Employee " _
& "FROM tbl_Temp"
currentdb.execute strsql

'Delete records from temp table
currentdb.execute "DELETE * FROM tbl_Temp"
End if
End if

'You might want to insert some code here to either delete the
processed files, or move them to a backup folder where they could be reused
if necessary.

'Get the next filename provided by the DIR function
strFileName = DIR()

Loop

End Sub

HTH
Dale

Dear All

I need your help to make this import successuful.

I have arround 150 excel files that need to be imported into access.

The files are named "Report dd/mm/yyyy.xls"
The columns are Code-Name-Balance-Employee
First i will need to add a column date where i put the date included in
the
file name.
Second i will need to remove the subtotals (all the files contain a
subtotal
by employee).
Third i will need to import them to Access in one table.

Thanks for your help

 

[snowiii]

Small brother:

I am trying to do something similar but a little less complex...I just want
to import multiple workbooks data (all have same format) into a table I have
built...I tried parsing some of your code but have had no luck (just a
beginner)....Can you or anyone else help me with code to do this?

Thx Dale for your help
i have found the solution to this problem
it's similar to what you propose
here is the code:

Private Sub Command1_Click()
Dim objXL As Excel.Application
'Dim wbXL As Excel.Workbook
'Dim sheet As Excel.Worksheet
Dim strPath As String
Dim strFileName As String
'Dim rCount As Long, r As Long
Dim j As Long
Dim k As Long
Dim aa As String
Dim Rng As Range

Set objXL = CreateObject("Excel.Application")
strPath = "C:\IntCalc\Jan07\"
strFileName = Dir(strPath & "*.xls")


Do While Len(strFileName) <> 0
Excel.Workbooks.Open Filename:=strPath & strFileName
Workbooks(strFileName).Worksheets("Sheet1").Activate
ActiveSheet.AutoFilterMode = False
Worksheets("Sheet1").Range("A:M").RemoveSubtotal
Delete_Columns



For j = (Range("A65536").End(xlUp).Row) To 1 Step -1
If IsNumeric(Worksheets("Sheet1").Cells(j, 1).Value) = False Or
IsEmpty(Worksheets("Sheet1").Cells(j, 1)) = True Then
Worksheets("Sheet1").Rows(j).EntireRow.Delete
End If
Next j




L = (Range("A65536").End(xlUp).Row)
aa = "E" & L
Range("e1").Select
ActiveCell.FormulaR1C1 = Mid(strFileName, 13, 2) & "/" &
Mid(strFileName, 10, 2) & "/2007"
Range(aa).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown


DoCmd.TransferSpreadsheet acImport, , "Sheetimport", strPath &
strFileName, False

Workbooks(strFileName).Close SaveChanges:=True
strFileName = Dir()
Loop
objXL.Workbooks.Close
'strFileName = Dir
Excel.Workbooks.Application.Quit
Set objXL = Nothing

End Sub





Sub Delete_Columns()

Excel.Application.ScreenUpdating = False
Excel.Application.Calculation = xlCalculationManual

On Error GoTo Exits:
Worksheets("Sheet1").Columns(14).EntireColumn.Delete
Worksheets("Sheet1").Columns(13).EntireColumn.Delete
Worksheets("Sheet1").Columns(12).EntireColumn.Delete
Worksheets("Sheet1").Columns(11).EntireColumn.Delete
Worksheets("Sheet1").Columns(10).EntireColumn.Delete
Worksheets("Sheet1").Columns(8).EntireColumn.Delete
Worksheets("Sheet1").Columns(7).EntireColumn.Delete
Worksheets("Sheet1").Columns(6).EntireColumn.Delete
Worksheets("Sheet1").Columns(5).EntireColumn.Delete
Worksheets("Sheet1").Columns(3).EntireColumn.Delete

Exits:
Excel.Application.ScreenUpdating = True
Excel.Application.Calculation = xlCalculationAutomatic

End Sub

Yes, I think that would work. That is what I was thinking.

It should may have Sub Total in multiple rows and Total in another, and some
blank lines, so a Delete Query might work for this:

DELETE * FROM SomeTable WHERE Instr([SomeField], "Total") > 0;

To remove the blank lines, if there is a column that will always have a
value, it could be include in the query:

DELETE * FROM SomeTable WHERE Instr([SomeField], "Total") > 0 OR [GoodField]
IS NULL;

--
Dave Hargis, Microsoft Access MVP

That was why I asked what his sub-totals row looks like.

In most of the Excel spreadsheets that I have worked with, they either:

1. label the row ("Total", "SubTotal")
2. leave the cell in column A blank, while the cells above and below all
have data
3. have an empty row immediately following it (in which case we would have
to delete two records for each employee)

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Explain to me how you would identify the subtotals row? There is no
assurance that it would look any different than any other row.
Other than that, I like your idea.
--
Dave Hargis, Microsoft Access MVP


:

I disagree with Dave, this may not be a whole lot of code.

First, I would create a temporary table to use to do the initial import of
your data. This table should contain the 4 fields you describe in your
original post, except that you can see I have changed [NAME] to [NameField].
Name is an Access reserved word, and should not be used as a field name.

What does your Subtotal line look like in the spreadsheet? Give us a sample
of a couple of records and the SubTotals row. Hopefully, the subtotal rows
will have a value or missing value that is in all of the other rows.

This is untested air code, and I have not filled in all the blanks, but it
should give you an idea of how I would approach this.

Public Function ImportExcelFiles

Dim strFileName as String
Dim strFileDate as String
Dim strSQL as string

'Delete records from your temp table
currentdb.execute "DELETE * FROM tbl_Temp"

strFileName = DIR(currentproiect.path & "\*.xls")
Do while len(strFileName) > 0

'Check to make sure the Excel filename is structured
'the way you describe below.
If instr(strFileName, "Report") <> 1 then
'do nothing with this file
debug.print "File " & strFileName & " was not imported " _
& "(missing 'Report' at beginning of file
name)!"
Else
'Define the date portion of the filename
strFileDate = Trim(mid(strFileName, 7))
strFileDate = Left(strFileName, len(strFileName) - 4)
if not isdate(strFileDate) then
'do nothing, wrong file type
debug.print "File " & strFileName _
& " was not imported (bad date
format)!"
else
'Do your processing here
'Import records into the temp table (fill in the
parameters as appropriate
Docmd.Transferspreadsheet ....

'Delete the subtotal rows from the temp table (I'd
write and
'save a Delete query and just execute that query
Currentdb.Querydefs("qry_DeleteSubtotals").Execute

'Insert the records into your main table
strSQL = "INSERT INTO tbl_Main (FileDate, Code, " _
&
"NameField, Balance, " _
&
"Employee) " _
& "SELECT #" & cdate(strFileDate) & "#, "
_
& "Code,
NameField, Balance, Employee " _
& "FROM tbl_Temp"
currentdb.execute strsql

'Delete records from temp table
currentdb.execute "DELETE * FROM tbl_Temp"
End if
End if

'You might want to insert some code here to either delete the
processed files, or move them to a backup folder where they could be reused
if necessary.

'Get the next filename provided by the DIR function
strFileName = DIR()

Loop

End Sub

HTH
Dale

Dear All

I need your help to make this import successuful.

I have arround 150 excel files that need to be imported into access.

The files are named "Report dd/mm/yyyy.xls"
The columns are Code-Name-Balance-Employee
First i will need to add a column date where i put the date included in
the
file name.
Second i will need to remove the subtotals (all the files contain a
subtotal
by employee).
Third i will need to import them to Access in one table.

Thanks for your help