Reading an Excel Worksheet in Access

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I need to be able to read an excel worksheet in Access.
The excel file is not condusive to a straight import
(formats are not consistent, etc.). I tried to convert
the excel file into a csv file through VB, but the file
format still reads in like excel.

Here is my current code:

Sub import()

Dim XL_file As Object
Dim filename$
Dim copyname$
Dim A$

Close

filename = "S:\Trust Product Lines\Commercial Trust\daily
funds\manual trades\daily manual trade totals1.xls"
copyname = "S:\Trust Product Lines\Commercial Trust\daily
funds\manual trades\daily manual trade totals1.csv"

Set XL_file = GetObject(filename)
XL_file.SaveAs filename:=copyname

Open copyname For Input As #1

Do Until EOF(1)
Line Input #1, A$
Loop

Close
End Sub



Any help would be greatly appreciated.
 
Have you tried Linking to the Excel worksheet and then
using a make table or Append query

Jim
 
Hi Tim,

The best approach will depend on just what's in the Excel worksheet and
whether this is a one-off task or a regular one.

If the Excel table is basically straightforward apart from inconsistent
data types, repeating fields and other "spreadsheet database" stuff, try
importing it to a temporary Access table and then using one or more
append queries to put it into your actual tables. By using calculated
fields in the query you can adjust the data to suit Access's field types
(e.g. with type conversion functions such as CStr() and CLng()) and even
regularise it to some extent (e.g. by using IIf() or Switch() to
substitute one value for another).

If the Excel sheet isn't a regular table but has multiple headings or
totals or (worst of all) merged cells, things get much nastier and the
most powerful general approach is to use VBA Automation to extract the
data cell by cell and append it to your Access table. See the following
for more:

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

Post back here with more information if this doesn't get you started.
 
Hi,

If the Excel sheet isn't a regular table but has multiple headings or
totals or (worst of all) merged cells, things get much nastier and the
most powerful general approach is to use VBA Automation to extract the
data cell by cell and append it to your Access table. See the following
Click to expand...


I've had to deal with importing data from a highly irregular (i.e.
free-form) Excel sheets before. As you mentioned, multiple headings, group
total and subtotals were all over the place, and the meat of the data I
needed to import to Access was scattered all over.

Now, VBA Automation is certainly an option, but I opted to create a range
somewhere in an unpopulated area of the spreadsheet and named it, say,
EXPORTRANGE. I then reproduced only the meat of the data I needed to export
to Access in this range (using the simple +[cell address] formulas). This
way the meat of the data can be structured consistently within the range in
a table format. Access would then link only to this EXPORTRANGE. Of course
control totals should be built to verify that the export range picks up the
meat of the data in its entirety.

The benefits of doing it this way:

- No VBA programming needed, thus eliminating bugs/errors resulting from
unexpected/unhandled conditions in the spreadsheet.
- Changes in the spreadsheet would not affect the import process as long as
the structure of the export range is not changed.
- Debugging is easier.

Immanuel Sibero
 
Back
Top