Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002

    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.

  2. #2
    Join Date
    Apr 2004
    outside the rim
    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.

    Good luck

  3. #3
    Join Date
    Nov 2002

    Smile TY tcace

    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?

  4. #4
    Join Date
    Apr 2004
    outside the rim
    For the Open/Save As Dialogs, see

    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
            Case "String"
                'it's text
            Case "Date"
                'it's a date
            Case "Error"
                'it's not valid
            Case "Empty" Or "Null"
                'it was something else
            Case Else
                'it was something else
        End Select
        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)

    Good luck and have fun!

  5. #5
    Join Date
    Nov 2002

    Question ty again

    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?

  6. #6
    Join Date
    Apr 2004
    outside the rim
    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")
    End If
    ' 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
        Exit Sub
        MsgBox Err.Number & vbCrLf & Err.Description
        Resume Excel_Exit
    Lookup GetObject and CreateObject in the help file.

    Have fun

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts