Tom,
I thought you were trying to import the data from Excel into Access.
Your
code does not reflect that. Here is an SQL statement that appends data
into
an Access table directly from Excel.
Sub ImportPipeline()
Dim strSQL As String
strSQL = "INSERT INTO tblPipeline80 (Curr_Year, Curr_Month,
Program_Manager, ITM, " _
& "Opportunity, Resource, Jan, Feb, Mar, Apr, May, Jun, " _
& "Jul, Aug, Sep, Oct, Nov, Dec, Source_Sheet ) " _
& "SELECT '2005', '04', [Prog Mgr], ITM, Opportunity, Resource,
Jan,
Feb, Mar, Apr, May, " _
& "Jun, Jul, Aug, Sep, Oct, Nov, Dec, 'Pipeline 2005 April'" _
& "FROM [Excel 8.0;Hdr=Yes;DATABASE=" _
& "C:\Documents and Settings\hargida\My Documents\Access\" _
& "Pipeline 2005 April.xls;].Pipeline;"
CurrentDb.Execute strSQL
End Sub
tblPipeline80 in the Access Table
Notice that I am putting data into the Access table that is not in the
Excel
sheet. The version I have sent is my test version. Those items in
quotes
will be replaced with variables.
C:\Documents and Settings\hargida\My Documents\Access\Pipeline
2005April.xls;].Pipeline
The above is the spreadsheet I am importing from and .Pipeline is a
Named
Range in the spreadhsheet.
:
I've simplified this (I think) to just do the import before I do
anything
else with the database, and here's what I've got in my Excel macro
Sub AppendDatabase()
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.OpenDatabase Filename:="Database.mdb"
CommandText:=Array("tblStaticData"), CommandType:=xlCmdTable
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.CurrentRegion.Select
Selection.Name = "Database"
ActiveWorkbook.Close Savechanges:=True
Range("A1").Select
End Sub
But the bit I can't resolve at the moment is the last bit. The macro
opens
the 'tblStaticData' table in the 'Database.mdb' file and imports the
data
to
it, but then prompts me for the name of the new workbook I need to
save
it
as. All I want to do is append the data to the existing
'tblStaticData'
table, but I'm getting myself tied up in knots.
It's late - I'll try again tomorrow.
Thanks again,
Peter
Okay, let me know how it works out. One hint. I alway start with a
select
query to see if I am getting the data I want like I want it before I
make
it
into an append query
:
Thanks for that Klatuu, I'll give that a go and see how I get on.
It
seems
fairly straight forward.
Thanks again,
Pete
I don't know what you are refering to be the second table. Is it
the
temporary I suggested? I would not import it permanently. It
just
makes
your database grow when it does not have to. You can have more
fields
in
the
receiving table, but in this instance I don't know that it is
necessary.
Here is how I would suggest:
Create a table in your database that has the same layout as the
Excel
sheet
you want to import. Be careful about the formatting in the Excel
sheet.
If
a cell in Excel is formatted as General and no data was entered
into
it,
it
will be imported as Null. This will cause an error during
import.
The
easiest solution is to make sure that the columns in Excel you
are
going
to
import as numeric are formatted as some kind of number. That
way,
if
do
data
is in a cell, Access will see it as 0.
Create an Append query that uses the temporary table to update
the
Main
table. In this query, you can do the data manipulation you need.
for
example, lets say you have one colum in the temporary table that
is
DateAndTime. Since I don't know how the date and time look
coming
in
from
Excel, I can't be too exact here, but assume it is in the Access
Date
datatype. Then, in the Date column and Update To row of the
append
query:
ADate: formatdatetime([TempTable]![DateTime], vbShortdate)
And for the Time column:
ATime: formatdatetime([TempTable]![DateTime], vbShortTime)
You may need to select different options for the formatdatetime
function
depending on your needs.
Now to do the import:
1. Delete the data in the temporary table
2. Import the Excel file into the temporary table
3. Run the append query
:
Would it also work if I imported the data permanently into
Access?
The
only
problem I'm trying to get around in this scenario is making sure
the
second
table has the same number of records, by using an autonumbered
ID
field.
I
think your idea of a temporary import may be better, though.
Can you be a bit more specific about how I can do this, please?
Do
I
need
the same fields in both tables, or can I have more in the table
receiving
the data?
Thanks,
Peter
I think your best solution would be to import the spreadsheet
to a
temporary
table, then create an append query that will maniputlate the
data
the
way
you
want it into the Access table you want to put the data into.
:
Hi,
I'm using an excel spreadsheet which is automatically updated
daily
from
a
text file, and this table is linked into the Access database,
which
means
I
can't amend the data in the table.
I'm using a query to manipulate the data (for example
separating
the
time
from the date into different fields for search reasons) but
I'd
like
to
add
fields (such as Yes/No checkboxes and free text fields) where
additional
data can be added that relate to a specific record. My
initial
thoughts
were to use an additional table with the extra fields in,
then
use
a
query
to make a new table, but I don't know how to link the tables
to
ensure
what
I enter in a field of one record stays with that record and
doesn't
corrupt
others.
This probably sounds completely off the wall, and there's
bound
to
be
an
easier solution, but if anyone can give me some guidance or a
better
solution to the problem I'd appreciate it.
Thank you,
Peter
