Excel to Access question

[Stuart]

Using ADO. Office 2000.
I've opened a workbook and I've created an Access database
named as the workbook name (say "Test1.mdb").

I now loop through the sheets in this workbook and copy a
'Template' table from an existing database into Test1.mdb,
giving it the worksheet name.

So first time through the loop:

For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect
'Import the template table and name it as the sheet name
DoCmd.TransferDatabase acImport, "Microsoft Access", _
strTemplatePath, acTable, "Master Excel Template", _
ws.Name, True, False
** which works to this point **.
What I would now like to do, is to import the Excel data from this
sheet to the table named ws.Name.......somethng like:

StartRw = 2 'use 2 to skip field names (already in the template)
EndRw = .Range("N65536").End(xlUp).Row
' all records in a table
r = StartRw ' the first record in the worksheet
Do While r < EndRw + 1
With rs 'dimmed as ADODB.Recordset
.AddNew ' create a new record
' add values to each field in the record
.Fields("Item") = Range("A" & r).Value
.Fields("Description") = Range("B" & r).Value
etc

Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End With
Next 'ws

How can I achieve a reference to the new table, please, so as
to replace or reference the variable 'rs'?

Regards.

 

[GJones]

Hi Stuart;

You could execute a string query as the record set

MySql = "Select * FROM AccessTable"

Set MyTable= Server.CreateObject("ADODB.Recordset")
MyTable.Open
MySql,MyConnection,adOpenForwardOnly,adLockOptimistic


Where there is already a connection.

Then you can go

While Not MyTable.Eof
activecell = MyTable(0)
Activecell.Offset(0,1) = MyTable(1)

.....and so on.

MyTable.MoveNext
Wend


Thanks,

Greg

 

[onedaywhen]

Using ADO. Office 2000.
I've opened a workbook and I've created an Access database
named as the workbook name (say "Test1.mdb").

I now loop through the sheets in this workbook and copy a
'Template' table from an existing database into Test1.mdb,
giving it the worksheet name.

So first time through the loop... <snip>

This approach may be overly complicated. You can create the table and
data at the same time with one query using Jet's SELECT..INTO syntax
e.g. while connected to the .mdb database, execute this SQL:

SELECT *
INTO MyNewTable
FROM
[Excel 8.0;database=C:\MyPath\MyClosedWorkbook.xls;].[MyWorksheet$]

--