Excel "Form" Data into Access

[Guest]

I am looking for a way to export data from an Excel spreadsheet into an
Access 2003 database. The Excel spreadsheet is not formatted as a table
(column headings for fields, one row per record), but rather as an "input
form".

For example, the 'Project name' "input" field covers the merged cell range
E3:Y3, 'Project Description' input covers the merged cell range A5:AA7, 'Key
Process Impacted' input covers the merged cell range G9:AA9, etc.

I understand the concept of named ranges in Excel, but am not sure how to
properly use that information for a clean, single import into Access.
Because my "actual" data fields are scattered throughout the spreadsheet at
no fixed or consistent data ranges, will named ranges even help? I do not
want to do 'x' amount of import operations to capture each single piece of
data because they are not arranged in a tabular format in Excel.

Must the Excel spreadsheet be formatted as a table before attempting any
export to Access, or can the Excel "form" not be changed and the data fields
(only) exported to Access in one import operation?

 

[John Nurick]

Hi Mike,

It all depends on whether the various items of data are in consistent
locations in the worksheet.

To start with, you seem to be saying they are:

For example, the 'Project name' "input" field covers the merged cell range
E3:Y3, 'Project Description' input covers the merged cell range A5:AA7, 'Key
Process Impacted' input covers the merged cell range G9:AA9, etc.

That's fine: having got yourself an object variable pointing to the
worksheet you can get the project name with something like
oSheet.Range("E3").Value
and so on (the value in a range of merged cells is effectively stored in
the top left cell).

Or you can apply a range name to E3 and use something like this on a
variable pointing to the workbook:
oBook.Names("ProjectName").RefersToRange.Value

Or if the fields on the "form" are labelled - e.g. if the cell
immediately to the left of the project name "input field" always
contains "Project Name:" - you can write code that searches for the
labels and grabs the adjacent values.

In other words the data can be scattered throughout the workbook - but
it must be "fixed and consistent" in terms of absolute locations, or
named ranges, or relationship to "label" cells. Without that degree of
consistency, reliable automatic importing is somewhere between very
difficult and quite impossible.

Can you give a bit more information?

 

[Guest]

Hi John. Thank you for taking some time to help me through this issue. It
is very much appreciated!

90% of the Excel form fields do have a corresponding label to identify them,
while the remaining 10% are individual cells without labels, laid out in a
"column list" format. Therefore, I don't feel using the field labels is the
ideal way to proceed.

I feel more secure in using cell reference or named range values to point to
the actual data fields. I have the ability and permissions to edit the Excel
form, so I can ensure the data field cell references/named ranges are
consistent prior to the export.

The creator of the Excel form has also agreed to some modification of the
spreadsheet to reflect a tabular layout if necessary. From my understanding,
it would be easier to export the data from Excel into Access if the
spreadsheet form was in a tabular layout. Is this a true statement?

 

[John Nurick]

Hi Mike,

1) If you can control the Excel "form" then named ranges are definitely
the way to go. The advantage over absolute cell references is that you
can later modify the form without breaking your import/export code.

2) Tabular layout: this depends on how the data in the sheet relates to
the structure of the database. If everything entered in the Excel "form"
ends up as one or more records in a single table in the database, then
it's definitely worth trying to get them into tabular form in Excel
(i.e. a rectangular block of cells laid out just like an Access table in
datasheet view). Access can easily import data from such a "table" using
TransferSpreadsheet or an append query, or you can use something like
this in Excel VBA:

Function TestExport(DBPath As String) As Long
Dim oConn As Object 'ADODB.Connection
Dim SQLCmd As String
Dim RecordsAffected As Long

'Construct SQL statement. ExportRange is the named range
'covering the Excel "table". Field names need to match
'actual Access field names and Excel column headers.
SQLCmd = "INSERT INTO AddrCentTest " _
& "(ID, FirstName, LastName, City, Postcode) " & vbCrLf _
& "SELECT ID, FirstName, LastName, City, Postcode " & vbCrLf _
& "FROM [Excel 8.0;HDR=Yes;database=" _
& ActiveWorkbook.FullName _
& ";].[ExportRange];"

Set oConn = CreateObject("ADODB.Connection")
With oConn
.ConnectionString = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & DBPath & ";"
.Open
.Execute SQLCmd, RecordsAffected
.Close
End With
TestExport = RecordsAffected
End Function

I get the impression that it wouldn't be easy to re-arrange your Excel
"form" into a tabular layout without sacrificing usability. If the data
being entered into Excel does belong in a single table in the database,
you can probably get the best of both worlds by setting up the Excel
"table" on another worksheet, with formulas that get the data from the
appropriate cells or ranges in the "form".

3) Use Excel's data validation in your input cells to control what users
can enter and minimise the chance of import problems caused by
inconsistent data.

4) To append a single record whose fields contain values from various
single cells that you have defined as named ranges, you can use VBA code
like the above, but with an SQL INSERT INTO statement built like this to
use the VALUES keyword and a list of literal values:

With ActiveWorkbook
SQLCmd = "INSERT INTO AddrCentTest " _
& "(ProjectName, Description) VALUES " & vbCrLf _
& "'" & .Names("ProjectName").RefersToRange.Value & "', " _
& "'" & .Names("Description").RefersToRange.Value & "';"
End With

 

[Guest]

John, unfortunately you are correct about sacrificing usability in regards to
reformatting the Excel worksheet form.

Your suggestion to create an Excel table with formulas to get the form data,
however, sounds good. I will go that route and use named ranges on the Excel
form with data validation. The end users will certainly appreciate the
integrity of the Excel form staying intact, and I will appreciate the easier
export functionality!

Thank you very, very much John!

Hi Mike,

1) If you can control the Excel "form" then named ranges are definitely
the way to go. The advantage over absolute cell references is that you
can later modify the form without breaking your import/export code.

2) Tabular layout: this depends on how the data in the sheet relates to
the structure of the database. If everything entered in the Excel "form"
ends up as one or more records in a single table in the database, then
it's definitely worth trying to get them into tabular form in Excel
(i.e. a rectangular block of cells laid out just like an Access table in
datasheet view). Access can easily import data from such a "table" using
TransferSpreadsheet or an append query, or you can use something like
this in Excel VBA:

Function TestExport(DBPath As String) As Long
Dim oConn As Object 'ADODB.Connection
Dim SQLCmd As String
Dim RecordsAffected As Long

'Construct SQL statement. ExportRange is the named range
'covering the Excel "table". Field names need to match
'actual Access field names and Excel column headers.
SQLCmd = "INSERT INTO AddrCentTest " _
& "(ID, FirstName, LastName, City, Postcode) " & vbCrLf _
& "SELECT ID, FirstName, LastName, City, Postcode " & vbCrLf _
& "FROM [Excel 8.0;HDR=Yes;database=" _
& ActiveWorkbook.FullName _
& ";].[ExportRange];"

Set oConn = CreateObject("ADODB.Connection")
With oConn
.ConnectionString = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & DBPath & ";"
.Open
.Execute SQLCmd, RecordsAffected
.Close
End With
TestExport = RecordsAffected
End Function

I get the impression that it wouldn't be easy to re-arrange your Excel
"form" into a tabular layout without sacrificing usability. If the data
being entered into Excel does belong in a single table in the database,
you can probably get the best of both worlds by setting up the Excel
"table" on another worksheet, with formulas that get the data from the
appropriate cells or ranges in the "form".

3) Use Excel's data validation in your input cells to control what users
can enter and minimise the chance of import problems caused by
inconsistent data.

4) To append a single record whose fields contain values from various
single cells that you have defined as named ranges, you can use VBA code
like the above, but with an SQL INSERT INTO statement built like this to
use the VALUES keyword and a list of literal values:

With ActiveWorkbook
SQLCmd = "INSERT INTO AddrCentTest " _
& "(ProjectName, Description) VALUES " & vbCrLf _
& "'" & .Names("ProjectName").RefersToRange.Value & "', " _
& "'" & .Names("Description").RefersToRange.Value & "';"
End With

Hi John. Thank you for taking some time to help me through this issue. It
is very much appreciated!

90% of the Excel form fields do have a corresponding label to identify them,
while the remaining 10% are individual cells without labels, laid out in a
"column list" format. Therefore, I don't feel using the field labels is the
ideal way to proceed.

I feel more secure in using cell reference or named range values to point to
the actual data fields. I have the ability and permissions to edit the Excel
form, so I can ensure the data field cell references/named ranges are
consistent prior to the export.

The creator of the Excel form has also agreed to some modification of the
spreadsheet to reflect a tabular layout if necessary. From my understanding,
it would be easier to export the data from Excel into Access if the
spreadsheet form was in a tabular layout. Is this a true statement?

 

[onedaywhen]

I will go that route and use named ranges on the Excel
form with datavalidation.

Be warned that data typing in Excel is quite weak and this extends to
its Data Validation functionality e.g. it doesn't bite when you
*paste* in data of an illegal type

Jamie.

--

 

[Guest]

I see that to be true. *sigh*

Be warned that data typing in Excel is quite weak and this extends to
its Data Validation functionality e.g. it doesn't bite when you
*paste* in data of an illegal type

Jamie.