Limited Excel Data to Bigger Access Table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tjdaly - 25 Feb 2006 03:09 GMT

I am trying to figure out how to bring in data from an estimating excel
spreadsheet. I would like to bring in roughly 10 items in tabular format
from excel into a project dbase that holds all project info. There is one
large table that holds the majority of the uique project information for each
projet. What is th ebest way and most automated way to bring in the excel
data from the spreadsheet into the dBase project table. The spreadsheet is
different fro every project, but the data is the same.
I need to figure out a way to automate this function so a sales assistant
can perform the function easily and correctly.
Any help would be greatly appreciated.

Signature

TJ Daly

Reply to this Message

John Nurick - 25 Feb 2006 07:33 GMT

It sounds as if you have a small rectangular range somewhere in each
workbook and want to import or link to the data it contains.

If the workbooks are all based on the same template, and the users are
moderately disciplined, the simplest thing is to define a named range in
the template that coincides with your data range (and probably including
its column headers), using Insert|Name|Define.

You can then use DoCmd.TransferSpreadsheet in the usual way, passing the
name of the range as the Range argument.

>I am trying to figure out how to bring in data from an estimating excel
>spreadsheet. I would like to bring in roughly 10 items in tabular format
[quoted text clipped - 6 lines]
>can perform the function easily and correctly.
>Any help would be greatly appreciated.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

tjdaly - 25 Feb 2006 11:50 GMT

You have exposed my extreme newbieness!!
This sounds like it would work if I knew what I was doing.
I am assuming i use the TransferSpreadsheet in the Macro tab and then go
from there. It keeps asking for a file name? How do I get it to ask for a
path when that macro is run? Since the path is difft every time?
You lost me on Insert[Name]Define?
Can I do this if the range i am bringing in only has 5-10 fields and the
table has 25?
Ignorance is not always bliss!

Signature

TJ Daly

> It sounds as if you have a small rectangular range somewhere in each
> workbook and want to import or link to the data it contains.
[quoted text clipped - 22 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 25 Feb 2006 18:36 GMT

I think you'd better explain in more detail, or we'll start
misunderstanding each other.

Is your Excel range laid out like a table, e.g.

    ID        FirstName    LastName    Postcode
1        Peter        Jones        SW1E 4AA
    2        John        Lewis        W1A 6EH
    3        Richard        Caplan        W1F    3RR
    ...

or some other way?

Does it have column headers (as above)? If so, are the headers identical
to the names of the corresponding fields in your Access table?

You say the Access table has more fields in it than the Excel range.
When you import the data from Excel, do you want to leave these other
fields empty, or do you need to put values into them? If so, what
values?

Or do you need to import values from the Excel range into existing
records in the Access table?

Any way round, you'll need to use VBA to do this, rather than a macro,
so it'll be worth reading up on that. One place to start looking is on
Jeff Conrad's Access Resource page at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html.

To let the user select a file name, the most reliable method is to use
the code at http://www.mvps.org/access/api/api0001.htm.

>You have exposed my extreme newbieness!!
>This sounds like it would work if I knew what I was doing.
[quoted text clipped - 5 lines]
>table has 25?
>Ignorance is not always bliss!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

tjdaly - 25 Feb 2006 18:50 GMT

Thanks for your patience! I feel like I am close.

> Is your Excel range laid out like a table, e.g.

Yes

> Does it have column headers (as above)? If so, are the headers identical
> to the names of the corresponding fields in your Access table?

Yes

> You say the Access table has more fields in it than the Excel range.
> When you import the data from Excel, do you want to leave these other
> fields empty

Yes

I'll read the links today.
Thanks again for your help!!!!

Signature

TJ Daly

> I think you'd better explain in more detail, or we'll start
> misunderstanding each other.
[quoted text clipped - 42 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 25 Feb 2006 21:35 GMT

Perhaps the simplest approach would be along these lines.

1) use the ahtCommonFileOpenSave() function from
http://www.mvps.org/access/api/api0001.htm to get the name and location
of the Excel file from the user.

2) pass this to DoCmd.TransferSpreadsheet to create a (temporary) linked
table connected to the Excel range. (I suggested using a named range.
IMHO this is the best approach if these spreadsheets are being created
from some kind of a template. Alternatively, if the range you're
interested in is guaranteed always to be in the exact same absolute
location (e.g. Sheet1!E20:I25), you can pass that instead: though it
will fail if the user has added or deleted rows or columns. Finally,
it's possible to write code that examines the contents of the workbook
to find the range you want - but that's advanced VBA.)

3) run an append query that moves the data from the linked table to the
table where you want it. To do this, you'll need to start by creating
the linked table manually (File|Get External Data|Link). Give the linked
table a name that indicates its purpose (e.g. tblTempExcelLink). Then
create an append query in the usual way, and name it something like
qryAppendExcelData.

This is "air code" and will need modification to suit your actual
circumstances.

Dim strFilter As String
Dim strInputFileName as String
Dim dbD as DAO.Database
Dim tbdT As DAO.TableDef
Const TEMP_TABLE = "tblTempExcelLink"
Const QUERY_NAME = "qryAppendExcelData"
Const IMPORT_RANGE = "RangeToImport" ' or "Sheet1!E20:I2"
' or whatever

'Get the filename
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select the workbook", _
Flags:=ahtOFN_HIDEREADONLY)

'Delete the temporary linked table if left over from last time
For Each tbdT In dbD.TableDefs
If tbdT.Name = TEMP_TABLE Then
DoCmd.DeleteObject acTable, TEMP_TABLE
Exit For 'No point in continuing through remaining tabledefs!
End If
Next

'Create the temporary linked table
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
TEMP_NAME, strInputFileName, True, IMPORT_RANGE

'Execute the append query
dbD.Execute QUERY_NAME, dbFailOnError

>Thanks for your patience! I feel like I am close.
>
[quoted text clipped - 10 lines]
>I'll read the links today.
>Thanks again for your help!!!!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message