Unanswered: Excell Spreadsheet as a linked table in Access 2002
I hate to say this, but the main office in Kansas uses this somewhat wonky inventory tracking and accounting software called MAS90 which they then export to Excell to massage the data around with. Then they basically get a flat file which is, oddly enough, almost, but not quite, in third normal form. they have in the past used another worksheet in the same Excell instance to massage the data then copied and pasted it onto the end of the main workbook. Then, this main workbook was used as a linked table for an access database(not made by me) to run reports on. Then, we updated all the computers to windows xp professional and microsoft office xp 2002 and they started to have problem with data types. No matter what is done to try to fool the linked table in Access, fields that have items that are mostly numbers but maybe a few string items in them come across as number fields with the #NUM error for the records with string items that even comes across on reports. Does anyone know of a way to fix this? I know that I could just make an Access form for them to copy and paste the new info into but I'm worried about copy errors and making them do a new step, seeing especially how some of the users are not very tech savy. Thank you in advance for your replies and time.
There's a lot of options here, many that involve some serious work.
I would probably do the importing through code - that way I can screen the data before it hits the table.
If you reference Excel in Access, you can Dim an object as Excel.Application and then "use" Excel through code in Access. I would:
1. Use the Access file open dialogs (don't use the Common Dialogs - they are unreliable) so that the user can select the Excel file.
2. Open a recordset to a table in Access
3. Dim Excel; and use the Excel object variable to:
a. Open the file
b. Loop through the data in Excel using the Excel.Application.Cells(y,x) structure and copy each value to a "variant" data type.
4. Screen the data and write valid data to the recordset.
5. Set all my object variables to Nothing
6. Continue on my merry way in Access
Believe it or not, this isn't as hard as it sounds!
I suppose another option more in line with how it is setup now is to make the fields in your linked table deifned as Text. Then use a query with the Val function to convert the data to numbers (the strings will Val to 0). Similarly, if your setup does not allow you to predefine the data types, then bring the data in "as is" and use a query or two to clean it up before it hits the reports.
Thanks for the idea, just a few questions though. I assume you're talking about using ADODB to open the Excell spreadsheet, and I can figure out how to tell it which column to stop sucking data from, but how do you figure the last row--or will a row with no data just trigger EOF and ending the grabbing of data. I haven't worked with grabbing data through code from another source outside the database yet, but I assume that I could read on the net and in the help files and figure it out. My guess is, since they continually just drop the massaged data onto the end of the Excell spreadsheet, that I should just delete the data in the current table and replace it every month. I could do it on database open I suppose, but I wonder if this would take a considerable amount of time after a while. Also, forgive me for being ignorant, but what are Access file open dialogs as opposed to the common dialogs? Is the common what pops up when you go on the menu bar and select File...Open?
As for the linking, that's a incorrect assumption! I read your original post to mean that there were issues linking to the Excel spreadsheet as a datasource. If you can figure out how to setup the ADODB connection, it will probably be a lot less coding to accomplish your task.
What I am proposing is, through code, to open Excel, open the file, move through the spreadsheet and grab the data in the cells - transfering it to an internal Access table as you go.
Here's a sneak preview:
Dim objExcel As Excel.Application
Dim iR As Integer ' Rows
Dim iC As Integer ' Columns
Dim iV As Variant
Dim blDone As Boolean
Set objExcel = GetObject(, "Excel.Application") ' this assumes Excel is already open
objExcel.Workbooks.Open FileName:="D:\My Documents\MyFile.xls"
For iC = 1 to 5 ' loop through the first 5 columns
blDone = False
iR = 1
iV = objExcel.Application.Cells(iR, iC).Value
Select Case TypeName(iV)
Case "Integer" Or "Long" Or "Single" Or "Double"
'it's a number
'it's a date
'it's not valid
Case "Empty" Or "Null"
'it was something else
'it was something else
iR = iR + 1 ' loop through the rows
Loop Until blDone
objExcel.ActiveWorkbook.Close False ' the False means "Don't Save"
Set objExcel = Nothing ' don't forget to do this!
I'm making this up as I go (meaning it's untested) so you will probably have to fiddle with the syntax a bit, and you'll notice I didn't include anything to be done with the data (I'd use a recordset that rides along with the loop)
Thanks, I we didn't have any trouble linking the silly workbook as a table, its just that some of the columns automatically were made number field type which didn't necessarily have all numbers in them--causing those fields to have #NUM in them. But, I'd rather not do linked table stuff with Excell if I can help it anyways. In your code, by saying you're assuming that Excell is already open, do you mean that the user pushing the button to get the data from the workbook has to open Excell first or do you mean opening Excell programatically?
Most of the time, you can modify the Dim statement to be Dim objApp As New WhatEver.Application but in the case of Excel, due to the way they have the personal.xls setup, multiple instances of Excel is not a good idea.
So, for Excel, I generally use
Dim objExcel As Excel.Application
Dim blOpen As Boolean
blOpen = True
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application") ' grab it if it's open
If Err Then ' it's not open
blOpen = False
On Error Goto Excel_Err
Set objExcel = CreateObject("Excel.Application")
' do your stuff here
If Not blOpen Then objExcel.Quit
' A glitch in some versions of Office will leave an Excel process running
' if it was created through code and then the object was set to nothing.
' The glitch can be avoided by "Quitting" Excel before clearing the object
' To test, run the code so that an Excel application is Created. Then,
' after the code is not executing and the object cleared, check in the
' Processes Tab of the task manager to see if Excel is still there
' Note: it will not appear in the Applications Tab
Set objExcel = Nothing
MsgBox Err.Number & vbCrLf & Err.Description
Lookup GetObject and CreateObject in the help file.