Because the data in your EXCEL file is so unstructured, you're going to need
to read each cell in the file and write it to a recordset so that you then
can write the recordset's data (just the data you want) into your ACCESS
table.
This article shows how you can set up the generic code to do this:
Write Data From an EXCEL Worksheet into a Recordset using Automation
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#WriteFileRst
You'll need to give careful consideration to the structure of the table on
which you'll base the recordset, and you'll need to 'carry down' values from
columns A and B into subsequent records that you write into the recordset so
that you'll know what each record is supposed to mean. ACCESS will not
retain the order of the rows from EXCEL after you write the data into a
table.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Ken,
I really work with this file at home.
As an example please find printscreen here:
http://marchello.ccx-grads.org/example.jpg
I can't post into usenet from my workplace.
The structure of real files is the same.
"Create", "change", "delete" words are not in English, also I have
"F3" column with description of 7-digit values (it is not important
for our task).
Please find current version of working solution below (two modules).
Now I have to merge these two modules into one;
temporary table for importing Excel files must be the one;
I have to add the name of each spreadsheet into table named
tblOutput;
drop all tables except tblOutput after work.
I can't know all names of tables with import errors (description of 7-
digit values is rather to long to import), otherwise I would use sql
with DROP.
Please, be so kind, suggest me the way of solution.
Thanks ahead.
Your description of the data in the EXCEL files is not clear. Can you be
more specific about the "blocks" of data -- show real data and the cells
in
which the data are found.
http://marchello.ccx-grads.org/solution.txt
Sub test()
Dim rstCurr As DAO.Recordset
Dim dbsCurr As Database
Dim MyPath As String
Dim MyFile As String
Dim ShortFile As String
MyPath = "C:\Marchello\"
Set dbsCurr = Access.CurrentDb
Set rstCurr = dbsCurr.OpenRecordset("Table1", dbOpenDynaset)
MyFile = Dir(MyPath)
Do While MyFile <> ""
rstCurr.AddNew
'rstCurr.Fields("1").Value = Time$
'rstCurr.Fields("2").Value = Date$
'rstCurr.Fields("3").Value = MyPath
'rstCurr.Fields("4").Value = MyFile
'rstCurr.Update
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, MyFile,
MyPath & MyFile
MyFile = Dir
Loop
End Sub
Sub test()
Dim db As DAO.Database, tdf As DAO.TableDef
Dim strAction As String
Dim rstIn As DAO.Recordset, rstOut As DAO.Recordset
' Point to this database
Set db = CurrentDb
' Open the output recordset
Set rstOut = db.OpenRecordset("tblOutput", _
dbOpenDynaset, dbAppendOnly)
' Loop through all tabledefs
For Each tdf In db.TableDefs
' Look for a table name starting with "r"
'If tdf.Name Like "r*" Then
If Left(tdf.Name, 1) = "r" Then
' Found one - open it
Set rstIn = db.OpenRecordset("SELECT F2 " & _
"FROM [" & tdf.Name & "] " & _
"WHERE Len(F2 & """") > 0")
' Process all the records
Do Until rstIn.EOF
' See if keyword
If (rstIn!F2 = "Create") Or (rstIn!F2 = "Change") _
Or (rstIn!F2 = "Delete") Then
' Just save the action
strAction = rstIn!F2
Else
' Make sure we have a good action
If Len(strAction) > 0 Then
' Write an output record
rstOut.AddNew
rstOut!Field1 = tdf.Name
rstOut!Field2 = rstIn!F2
rstOut!Field3 = strAction
rstOut.Update
End If
End If
' Get the next record
rstIn.MoveNext
Loop
' Close the input
rstIn.Close
End If
' Get the next table
Next tdf
' Clean up
rstOut.Close
Set rstIn = Nothing
Set rstOut = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub
