Try to import the individual 6 columns into another sheet. It appears that
the extra columns are not being deleted from the sheet prior to the import.
By importing into another sheet, this should not be an issue.
Regards,
Mark Bartnik
>From: "Randy Raymond" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: [EMAIL PROTECTED]
>Subject: [Access VBA Central] Importing Spreadsheets via VBA
>Date: Tue, 28 Jun 2005 15:08:12 -0000
>
>I am writing Access VBA code to import data that I have stored on a
>series of Excel spreadsheets into a table. I am using the
>DoCmd.acTransferSpreadsheet function to import worksheets from a
>spreadsheet file to a temporary table which I then use to populate
>the table in my database via DAO recordsets. The method seemed to
>work fine for a while, but when I used it on another spreadsheet file
>(identical as far as I know to the one that worked fine) it suddenly
>started giving me a runtime error (3709 The search key was not found
>in any record) and halting. When I went to debug, execution had
>halted at the acTransferSpreadsheet function, which in my case was
>written with the following parameters:
>
>DoCmd.TransferSpreadsheet acImport,
>acSpreadsheetTypeExcel9, "tblSam", strPathName, -1, strSequenceName
>& "!"
>
>Where strPathName is a string variable for the file name of the
>spreadsheet and strSequenceName is a string variable holding the
>worksheet name.
>
>To investigate the problem I tried importing a worksheet from the
>spreadsheet file by hand, using the File > Get External Data and
>using the import wizard. It failed. The main problem it had was
>that it seemed to want to import ALL the columns from the worksheet,
>whether or not they contained data. The wizard would show a hundred
>or so empty columns, and only the first six actually had any data. I
>tried selecting all the empty columns in the worksheet, then hitting
>the delete button, just in case there was a stray character in one of
>the columns that I couldn't see, but this didn't help.
>
>Does anyone have any insight into this problem?
>
>
>
>
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessVBACentral/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
