Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reading an Excel file into a recordset (VB6)

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

    I have a spreadsheet which I need to import into a table.
    I've had a look round to find the best approach for doing this. These are my limitations:

    The file has a top row which is a title in A1 which spreads across all columns below it.
    The file has a 1st row (after the title row) with column headers in it.
    If the date field is null the row gets ignored.
    If the date field is null but the dfe number doesn't appear in the establishments table, the row goes into the tagging database,
    else the row goes into the reportslookup database.

    I looking into reading it in line by line and I looked into opening the file as a recordset, most of which seem to use .net which I don't have and know nothing about:
    http://msdn.microsoft.com/library/default....IntoDataset.asp

    RE: References to the System and System.Data namespaces.
    No idea what this means!

    Either way i'm not sure how opening it as a recordset would deal with the 1st line.

    Can anybody suggest the best approach to go for, it might save me a bit of time looking into one only to find its inappropriate!
    Any help would be much appreciated.

  2. #2
    Lounger
    Join Date
    Jul 2002
    Location
    Wellington, New Zealand
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading an Excel file into a recordset (VB6)

    Phil

    I use ado to connect, It has an undocumented feature when connecting to excel in that it always assumes the first row returned contains field names. The formatting is ignored so the recordset would think that column a had a name and column b was called null etc

    I would think you would solve your problem by
    opening the recordset
    moving the curser to the first row of actual data.
    looping till end of file with a do...loop until
    refering to your columns by their indexes instead of their names

    does this help. attached is some code with a quick connect to an excel file

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading an Excel file into a recordset (VB6)

    Thanks for your reply, looking into it now - i've never programmed in Word before and I didnt realise it was that easy and flexible! Thanks for opening my eyes to that as well. I'll post back with how I get on with your code.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading an Excel file into a recordset (VB6)

    Wow - that worked like a treat, thanks for such a great example Doj.

    I can't believe it is so close to the code to open an Access or SQL recordset!

    Thanks for all your help again Doj.

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Wellington, New Zealand
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading an Excel file into a recordset (VB6)

    Nice to be able to put something back

    This is where I learnt how to DO my job

Posting Permissions

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