VBA for Access, import Excell files into Access tables

  • Thread starter Thread starter George
  • Start date Start date
G

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?
 
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.
 
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.
 
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
 
Back
Top