Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Spreadsheet Import (2003)

    I've finally sorted out all the spreadsheets, and got them into the right format <post#=692,908>post 692,908</post#>. Can anyone help me with code to import all the spreadsheets into the database. The filenames all start with Checks followed by the date e.g Checks 251107.

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

    Re: Spreadsheet Import (2003)

    See for example <post:=658,115>post 658,115</post:> and <post:=164,791>post 164,791</post:>. You can adapt the code for your situation.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet Import (2003)

    Thanks Hans. Used the first link. Still a rogue column in one of the spreadsheet, as the code stopped with the message Field F30 doesn't exist in the destination field. I will either have to search through them all, or I could add F30 to the table, and see what it brings up. Thanks again Hans.

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet Import (2003)

    I've found the supposable rogue spreadsheet. I can't find any data or column headings that aren't in the other sheets. If i however, import the table in the normal way, there is a Field30 that shows up. As I say, not evident in the spreadsheet.

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

    Re: Spreadsheet Import (2003)

    Is it the last field in the imported table? If so, there is probably something in the first blank-seeming column in the Excel sheet that causes the import mechanism to see the column as a field, perhaps a space. Because this column has no column header, it is given a dummy field name F30.
    If you select a few columns to the right of what appears to be the end of the used columns in the spreadsheet and delete them, then save the workbook, you may get a better result while importing.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet Import (2003)

    Yes it was the last field. I've gone through every spreadsheet and deleted 4 colums at the end, and everythings imported ok. Thanks Hans.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet Import (2003)

    Rob

    One quick way of checking whether there is 'rogue' data in a worksheet is to click CTRL-END. If the selected cell is outside the area to be imported, you will need to delete some rows and/or columns. You must save the workbook after the deletion, and then try CTRL-END again. When CTRL-END selects the bottom right corner of the data to be imported, then you can proceed with the import.

    Nick

Posting Permissions

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