Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Angry Unanswered: Importing from Excel, part two.

    Well, I managed to import Excel files in my Access database, thx to Justin. But the only way to make it work was to make the first row carry the column names. Now, this doesn t seem a problem, but for me it is, as I want users to fill out Excel files ( a template, actually), where the headers should be in the first COLUMN, as it's much more appealing and practical. Is there any way to "tell" Access (VB) that the matrix - the cells in Excell - are transposed?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yes, but not as easily as you might like. What method are you using to import your excel file? For me, I would open the workbook as an object in vba, then use the values in the first column to create a new table, then create a dataset of the excel sheet sans the column header row and push it into the new table.

    Also, I don't understand how putting headers in the first column is more appealing or practical... do you have a crosstab like conversion you are performing somewhere?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2005
    Posts
    5
    Yeah, that's one option, to access it through ADO. The problem is that my customer wants to handle these report-like sheets in Excel, and they have the following structure:
    Name: _________________
    Last Name: _____________
    Zip: ____________________
    etc.
    a very long list, and so far I've managed to make the import work only if the sheet is formatted like:
    Name| Last | Zip|......
    Ian | Dury| 1234| .....

    which is ugly and very user-unfriendly. The method I used is the following
    Code:
    DoCmd.TransferSpreadsheet 
    transferType:=acImport, 
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="Table2",
     FileName:=.FoundFiles(intFileCount), 
    HasFieldNames:=True, Range:="Sheet1!E1:G3"

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    For a very long list... it would seem that the way you are doing is the most logical...

    unless I"m missing something.

    You have a lot of attributes? Is this "very long list" meaning "a lot of columns"? In that case, you really might want to consider using an ado/dao/etc dataset. It will grant you the best flexibility.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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