Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with Excel (A2K)

    Hi all!

    I am trying to automate the process of importing data stored as a .dif file into my Access application.

    I'm unaware of any way to directly import the .dif, so I figure I need to convert the data to a .xls first. Then I can use TransferSpreadsheet to import the data. Using the following code I'm able to import the first 16,385 records (rows). The .dif file has 45,000+ records to be imported.

    Public Sub ImportData()
    On Error GoTo Handler

    Dim strFileName As String
    Dim objXL As New Excel.Application
    Dim objWb As Excel.Workbook

    'Ask the user to locate the .dif file to use for the import process
    strFileName = FindDifFile("") 'Function that returns the path to the .dif file
    'Open the .dif in Excel. This will force the conversion to an actual .xls file
    Set objWb = objXL.Workbooks.Open(strFileName)
    'Save the file as a .xls to complete the conversion
    strFileName = Left(strFileName, Len(strFileName) - 3) & "xls"
    objWb.SaveAs strFileName, xlExcel7
    objWb.Close True
    objXL.Quit
    Set objWb = Nothing
    Set objXL = Nothing

    ...The rest of my routine...
    End Sub

    As I said, that code works fine up to the 16,385 limit. How can I get around that limit?

    I have a feeling it has something to do with the way I'm saving the .xls file here:
    objWb.SaveAs strFileName, xlExcel7

    Do I need to use a newer Excel engine? If so, what's the syntax?

    Thanks, in advance, for any help!
    Sam

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

    Re: Working with Excel (A2K)

    Excel7 = Excel 95 had 16,384 rows in a worksheet. Excel 97 through 2003 have 65,536 rows per worksheet. Try xlWorkbookNormal instead of xlExcel7 in the SaveAs line.

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Excel (A2K)

    Perfect! Thanks so much, Hans!!

Posting Permissions

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