Has someone written a macro to run in Excel that will append a fixed range of
data into an existing Access table. The data I need to exdport resides in
one sheet of a Excel file in list form with column headings. The files are
created by a piece of lab equipment each time a test is completed. Each
saved file shares a common structure but is given a unique file name. There
can be dozens of files created each day which makes use of the
TransferSpreadsheet function in Access impractical. The ideal macro would be
embedded in the Excel template file used to capture the test data.

This is infinitly do-able but it is not a great project for the first timer.
You need to be familiar with ADODB recordsets, SQL queries and to do this
right the whole thing should be stored as an XLA addin file. If you are
reasonably familiar with these concepts and only need a little help with the
implimentation then by all means reply back and I can feed you some code that
should help expedite this project...

HTH

"halfshoe" wrote:

Has someone written a macro to run in Excel that will append a fixed range of
data into an existing Access table. The data I need to exdport resides in
one sheet of a Excel file in list form with column headings. The files are
created by a piece of lab equipment each time a test is completed. Each
saved file shares a common structure but is given a unique file name. There
can be dozens of files created each day which makes use of the
TransferSpreadsheet function in Access impractical. The ideal macro would be
embedded in the Excel template file used to capture the test data.

gocush wrote:
Has someone written a macro to run in Excel that will append a
fixed range of
data into an existing Access table.

The following is code that I got from Andy Wiggins some time ago
<snip

That's a lot of code <g (and anyhow doesn't do what the OP asked i.e.
creates a new table rather than inserting into an existing table). I
think this could be a job for 'ADO in just four lines':

Sub JustFourBygLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
con.Execute _
"INSERT INTO MyNewtable (MyCol1, MyCol2, MyCol2)" & _
" SELECT MyCol1, MyCol2, MyCol3 FROM" & _
" [Excel 8.0;HDR=YES;Database=C:\MyWorkbook;].MyRange;"
End Sub

Jamie.

--

Jamie,
Thanks for the concise update.
Rather than hardcode it as you have, I would like to make it generic by
adding variables to the code. Below is the code I have tried with comments
to describe my setup and possible changes I need to make in order for it to
work.
The execute command is yielding an error as the code exists below, so it
needs a little tweaking. Can you advise on where to make the adjustments?

Also, will this work if MyRange has only a subset of the db Fields? e. g. if
the mdb had 10 fields and MyRange showed only Field1, Field4 and Field7

Much appreciated

"Jamie Collins" wrote:


gocush wrote:
Has someone written a macro to run in Excel that will append a
fixed range of
data into an existing Access table.

The following is code that I got from Andy Wiggins some time ago
<snip

That's a lot of code <g (and anyhow doesn't do what the OP asked i.e.
creates a new table rather than inserting into an existing table). I
think this could be a job for 'ADO in just four lines':

Sub JustFourBygLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
con.Execute _
"INSERT INTO MyNewtable (MyCol1, MyCol2, MyCol2)" & _
" SELECT MyCol1, MyCol2, MyCol3 FROM" & _
" [Excel 8.0;HDR=YES;Database=C:\MyWorkbook;].MyRange;"
End Sub

Jamie.

--

sorry I forgot the code:

Option Explicit

Sub AddRecordsToDB()

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'Setup:
'Access: An MS Access file: NewTestDB.mdb in same folder as This Excel
Workbook
' The mdb has one Table: MyTable
' This table has 3 Fields: Staff_Num, Salary, LastName
'Excel: On Sheet1:
' Range("A1:A100") is named MyRange
' Range("A1") is named MyCol1 value: Staff_Num
' Range("B1") is named MyCol2 value: Salary
' Range("C1") is named MyCol3 value: LastName
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''

Dim con As Object
Dim sPath As String
Dim sDbFile As String
Dim sMyTable As String
Dim MyCol1, MyCol2, MyCol3
Dim sThisWbk As String
Dim sBkName As String
Dim Rng1 As Range


sBkName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
sPath = ThisWorkbook.Path
sThisWbk = sPath & "\" & sBkName

sDbFile = sPath & "\NewTestDB.mdb"
sMyTable = "MyDbTable"

'Get Field names
MyCol1 = Range("MyCol1").Value 'Field names only-no data ????
MyCol2 = Range("MyCol2").Value
MyCol3 = Range("MyCol3").Value
''''Perhaps the above ranges should include data ????????

'Locate MyRange in Excel (A1:C100)
'This has both data and Field name headers
'which match the .mdb table fields
''''''''''''''''''''''
'This perhaps should exclude headers ?????
'and start with row 2 ????????
''''''''''''''''''
Set Rng1 = Range("MyRange")

Set con = CreateObject("ADODB.Connection")

con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDbFile & ""

con.Execute _
"INSERT INTO " & sMyTable & " (" & MyCol1 & "," & MyCol2 & "," & MyCol3
& "," & MyCol4 & "," & MyCol5 & "," & MyCol6 & ")" & _
" SELECT " & MyCol1 & "," & MyCol2 & "," & MyCol3 & "," & MyCol4 & "," &
MyCol5 & "," & MyCol6 & " FROM" & _
" [Excel 8.0;HDR=YES;Database=" & ThisWbk & ";]." & Rng1 & ";"

''''''' Perhaps the above Excel 8.0 is not correct for my Excel 2000
???????
End Sub



"Jamie Collins" wrote:


gocush wrote:
Has someone written a macro to run in Excel that will append a
fixed range of
data into an existing Access table.

The following is code that I got from Andy Wiggins some time ago
<snip

That's a lot of code <g (and anyhow doesn't do what the OP asked i.e.
creates a new table rather than inserting into an existing table). I
think this could be a job for 'ADO in just four lines':

Sub JustFourBygLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
con.Execute _
"INSERT INTO MyNewtable (MyCol1, MyCol2, MyCol2)" & _
" SELECT MyCol1, MyCol2, MyCol3 FROM" & _
" [Excel 8.0;HDR=YES;Database=C:\MyWorkbook;].MyRange;"
End Sub

Jamie.

--