Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Import from Excel (97 running on NT4.0)

    I know this forum has many conversations relating to importing from excel, but I have a twist that I can't seem to figure out. I routinely import spreadsheets containing rate schedules used to price out contract changes. The spreadsheets are about 816 lines each there are 5 of them imported everytime the rates change. I have no problem importing these because they are set up the same way the tables are set up in the database. My twist is that in addition to these 5 spread sheets, there are two additional ones which are set up quite differently than the others. Unfortunately the structure of my tables requires these two additional spreadsheets to also be imported and updated to the same table as the other 5. Is there a way I can loop through the excel spreadsheet to extract only the information I need without totally changing the structure (the spreadsheets come from the contractor and are required to price the changes). The contractor is unwilling to modify the spread sheet to reformat the output. I know this is quite brief and does not describe how the spreadsheets are set up, but I was hoping I did not have to get into that detail. Any help is appreciated.
    Kevin

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import from Excel (97 running on NT4.0)

    You could import the two excel files in a temporary table and with queries or code extract the data you need and append to the master table.
    Another possibility is to link the excel sheets as a table and extract the data to append also with queries or code.
    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from Excel (97 running on NT4.0)

    To elaborate on Francois' answer a bit:
    It depends on the structure of the additional spreadsheets.

    If the information is in table form (althought different from your standard tables), you can import the information into Access and then use an append query or VBA to move it into the standard tables.

    If the information is not in table form, I would suggest writing an Excel macro to put it into table form. Then you can proceed as above.

    If you prefer, you can incorporate the Excel code (macro) into your Access application:

    Set a reference to the Microsoft Excel 8.0 Object Library (Tools/References... in any module)
    Create code like

    Dim appXL as Excel.Application
    Dim blnStartXL As Boolean
    Set appXL = GetObject(, "Excel.Application")
    If appXL Is Nothing Then
    blnStartXL = True
    Set appXL = CreateObject("Excel.Application")
    End If
    With appXL
    ' code using Excel goes here
    ...
    ...
    End With
    If blnStartXL = True Then
    appXL.Quit
    End If
    SetAppXL = Nothing

Posting Permissions

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