--- In [EMAIL PROTECTED], "Randy Raymond"
<[EMAIL PROTECTED]> wrote:
> 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?
---------------------------------------------------------
I hate importing workbooks straight into Access, it can bring up any
number of horrible little errors which are a pain to work around.
The last time I needed to do something similar (I didn't need the data
from all the columns) I iterated over each XL cell copying the value
to a variable, then at the end of each row I added that detail to a
new record in an Access table.
A better way (as someone else has suggested) is to export each
worksheet as a .csv (or tab delimited), and then import that into Access.
Owen
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/
