VBA for Access, import Excell files into Access tables

[George]

Hi there,
I use this code
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"TableName", "C:\File.xls"
to import 1 Excel file into Access table named TableName.

Now how do I import many files?
Full path to files is in field named fullPath, table "Table3" :

fullPath
C:\Marchello\FUNCS.XLS
C:\Marchello\PROTTPLN.XLS
C:\Marchello\PROTTPLV.XLS
C:\Marchello\SOLVSAMP.XLS

Also I need to name each of table using short names of appropriate
Excel files.
Could you please show me solution?

 

[Ken Snell \(MVP\)]

see reply in other newsgroup where you posted this question.

 

[George]

I'm about to use current group instead of
microsoft.public.access.externaldata because of its bigger quantity of
members.

So my code is:

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

Now I need to parse each Access table named r*.

The structure of r* tables is (I'm interested only in "F2" field) :

create

change

delete

At least one of blocks must not be empty, for example:

table "r071203_xlsx"

create
6026001


change
7399003


delete


So my need is to parse all "r*" tables, find 7-digit value in each of
blocks and to write new table:

field1: name of "r*" table
field2: 7-digit value
field3: create, change or delete

Please, be so kind, tell me how do I perform this?
Thank you in advance.

 

[Joean Turner]

I WOULD LIKE TO STREADSHEET ????

 

[George]

Dear microsoft.public.access subscribers,

Previous example was frankly incomprehensible, so it's my try to
improve task description.

My task is to parse Access tables named r* (and do not touch other
tables).

Please find example of table here (printscreen) :
http://marchello.ccx-grads.org/example.jpg

I have to parse "blocks" in table and prepare a list _when_ values was
created, changed or deleted.

I say "block" when I mean part of table, for example, after word
"create", it can be empty (no value was created this time) or can
consist of 1 or more values.

7-digit value is management accounting code.

Each name of Access table includes date: r071203_xlsx is the 3th of
December, 2007.

I have to parse about thousand tables. Please, be so kind, suggest me
way of solution.

 

[Arvin Meyer [MVP]]

You'll find all the r* tables with:

If Left([Table Name],1) = "r" Then
'Do something
End If

To parse the date out of the r* tables, try:

Dim x
If Left([Table Name],1) = "r" Then

x = CDate(Mid([Table Name], 4,2) & "/" & Mid([Table Name] 6,2) & "/" &
Mid ([Table Name], 2,2))

End If

r071203