--- In [email protected], "owen_group_profile"
<[EMAIL PROTECTED]> wrote:
> --- 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
*********************************************************************
I solved it myself. Instead of just using the worksheet name in the
range parameter of the DoCmd.TransferSpreadSheet function, I used the
worksheet name plust the range, and it worked perfectly.
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/
