Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    16

    Unanswered: problem getting data from excel

    Hi
    I using this code to get Data from excel. it works fine but its not getting all the data from a cell. I getting all rows and columns but partly cell. Like the data in the second line (wrap text) is not importing.

    Code:
    Private Sub SeletExcelFile_Cmd_Click()
       Dim FStrg As String
       FStrg = BrowseForFile(Me.hWnd, Me.StartPath, ".xls", _
          "EXCEL Documents (*.xls)" & Chr$(0) & "*.xls" & Chr$(0), "Select The Required EXCEL Document...")
     
         If FStrg = "" Then Exit Sub
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
        "myTable", FStrg, True
    
    End Sub
    please help

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    automating the import of an excel sheet does not appear to be related to whether or not all the data in the cell imports....it might - - but it doesn't strike me as intuitively certain at the get go...

    do a manual import...same problem? then you know it is not the automation

    if you have the same problem with a manual insert then...cell exceeding field length?...property/field type conflict?....

  3. #3
    Join Date
    Nov 2009
    Posts
    16
    Yep, you are right NTC the cell exceeding field length - Thank you
    SO how can i increase the field lenght?

    Can it be done by code?

    Thank you

  4. #4
    Join Date
    Nov 2009
    Posts
    16
    But also notice that some cell have short bullet points. So only the first bullet point is imported and not the second.

  5. #5
    Join Date
    Oct 2009
    Posts
    340
    just go to the table in design view and change it there...

  6. #6
    Join Date
    Nov 2009
    Posts
    16
    Sorry, stupid question. but its limited to 255 Character.
    Is there any work around if it excceds?

    Also it can't detect the Alt+Enter for bullet points. I have lots of bullets in spreadssheet that I want to import in access DB

    Please Advice.

    Thank you

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    change the field type from text to 'memo'.....that is one of the choices....it is a more wide open field type and less restrictive than text...

    this will be your best hope...

    you have to keep in mind that import for databases is oriented toward structured data - - not free form text with font issues (like bullets..)...so try the memo field type and I think that is going to be about as much as you will be able to get....

  8. #8
    Join Date
    Nov 2009
    Posts
    16
    Thank you NTC.
    It works well but mess up the bullet points.
    If some one have suggestions please advice

    Thank you

  9. #9
    Join Date
    Oct 2009
    Posts
    340
    you can get creative and not import it.....if look & layout are highly important....make it a pdf or keep it in excel or whatever and simply make it a standalone attached document via hyperlink field or attached document feature (Access07)...

    kind of just depends on what you are trying to accomplish in the end.....

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Pootle Flump posted some code here in the code bank which may be helpful: http://www.dbforums.com/6222281-post4.html
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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