Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How can I turn an Excel file into an Access file? (Office 2000)

    Hi gang! I haven't been around the forum for 6 months (travelling) and have not gotten my hands dirty with Access for a couple of years, so please bear with me. I searched through previous posts but was unsuccessful in finding a solution to this (what I think should be simple) problem.

    I have a somewhat complex (user authorizations) spreadsheet that I want to import into Access. The first few columns contain menu paths and transaction codes while the first few rows contain work areas and user IDs. If a user can access a certain transaction, there is an X in the intersecting cell. (This is an SAP authorisation list in case anyone has ever worked with one.)

    I've been able to create two tables, one for the menu paths and transaction codes and another for the user IDs. So far so good. I've even managed to create a third table that pretty much captures the spreadsheet as a flat field database. Now, how the heck do I move the useful data from the flat field table into a query of the transaction codes and user IDs? I'm sure this is going to be an "Ah, ha!" moment, but I really need that whack on the side of the head.

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How can I turn an Excel file into an Access file? (Office 2000)

    You'll have to name your ranges in Excel and then import not from the worksheet but from the range.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How can I turn an Excel file into an Access file? (Office 2000)

    Thanks, Charlotte. Hmm, I guess I'm going to have to rephrase my question to make it a bit more clear.

    I am able to import the spreadsheet without any problems (in fact, Excel picked up the first rows and columns for the field names automatically). So, now I have a table that looks pretty much like my Excel file, a plain ol' flat field database. Now I want to create a relational database. How can I rip it apart and put it back together again without losing my data?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How can I turn an Excel file into an Access file? (Office 2000)

    You might try the Table Analyzer (Tools-->Analyze). It will help you convert a flat table. We can't be of much help here without more specific information on the data and what you want to do with it.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How's this sound?

    With all the difficult questions posted on this forum, I guess my question may be simple to point where it looks tough <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>. One more try ....

    My spreadsheet consists of numbers in the left-hand column and names across the top. Where some rows and columns intersect, there are Xs to indicate that this name goes with this number. So, that's a flat-field database. How do I turn it into a relational database?

    I suspect that I need one table for names, one table for numbers and one table to indicate where the names and numbers meet. Does this sound right? What steps do I need to perform to make this happen?

    Once I have taken it apart, I can start adding additional pieces of information to the tables. For example, I want to have a location for each name so I can group people together who work in the same area. I also want to add menu paths to the numbers so I can group those together as well. Later I'll create another table so I can include training information (courses) and link that to the names and to the numbers. Whew! How's that? Does it make sense now? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How's this sound?

    I usually do this manually with the help of queries.. I'm sure an update query could do it, but I can't provide you specifics on that...

    Create your three new tables.
    Create queries to pull only the data you want from your 'flat file' table
    Copy the data and paste to the appropriate table.

    Then you can edit the relationships (check the help file) to tie it all together.

    I hope that helps!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: How's this sound?

    Hi,
    It sounds to me like you're effectively trying to split back out what looks like the results of a crosstab query into its original tables (I know they never existed, but you are trying to create the equivalent process in reverse). I suspect you will have to use code to do this and it may well be easier to do this with your original Excel spreadsheet and then import the results into your new tables.
    If you can post a sample spreadsheet, someone here can probably come up with a suitable routine - my initial reaction is that you'd want to use two arrays and populate the second one from the records with an X in them. You might also want to add a post in the Excel forum with a link to this post.
    I hope that makes sense?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: How's this sound?

    Based on what I think you mean, the following code will take data from sheet1 in a workbook (with titles in the first row and first column) and create a list in sheet2 with an entry for each combination of column and row with an X in:
    <pre>Sub BreakOut()
    Dim arrData
    Dim lngRow As Long, lngColumn As Long, lngRowCount As Long, lngColumnCount As Long
    Dim lngNewRow As Long
    Application.ScreenUpdating = False
    lngNewRow = 1
    arrData = Sheets(1).UsedRange
    lngRowCount = UBound(arrData, 1)
    lngColumnCount = UBound(arrData, 2)
    For lngRow = 2 To lngRowCount
    For lngColumn = 2 To lngColumnCount
    If arrData(lngRow, lngColumn) = "X" Then
    With Sheets(2)
    .Cells(lngNewRow, 1) = arrData(lngRow, 1)
    .Cells(lngNewRow, 2) = arrData(1, lngColumn)
    End With
    lngNewRow = lngNewRow + 1
    End If
    Next lngColumn
    Next lngRow
    Application.ScreenUpdating = True
    End Sub
    </pre>

    You may need to alter it for your specific needs. You can then import this and use the Table Analyser on it, or replace the Column and Row names with their ID values from your existing two tables, and import it as your third, linking table.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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