(Access 97) Transfer Spreadsheet function in macro fails

[Lisa]

I have a macro that runs queries and then outputs them to
a network drive using the transfer spreadsheet action. The
macro fails and gives me an error "Failure creating file"

I can right click on the query and export it to the drive
without any problems. So my access to the drive is not a
problem, and general exporting of queries seems to be ok.

Does anyone have any idea what might be wrong? I have re-
installed Access 97 three times and installed a patch that
was supposed to fix a few glitches.

TIA,
-Lisa

 

[Steve Schapel]

Lisa,

Please give details of the macro you are using.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

There are a total of seven actions in the macro.
The first three actions are OpenQuery. They run queries to
make table 1 a linked table, delete data from table 1, and
add data to table 1 in that order.

The remaining actions are the TransferSpreadsheet functons.

Each TransferSpreadsheet runs a query and then exports the
results to a network drive.

It runs for my other team members, I seem to be the
problem child of the group.

Thanks,
-Lisa

 

[Ken Snell]

Steve is taking a well earned vacation. I'm not sure if I can fill his shoes
in helping you, but I'll try.

When you say that it works for your other team members, are they running the
same copy of the database as you? In other words, have you tried their copy
of the database to see if the macro works then for you?

Right-clicking and doing an export is a different action than the
TransferSpreadsheet action. The former is the same as a OutputTo action,
which is a bit different in the "version" of EXCEL file that is created.

Can you post the exact TransferSpreadsheet action and the arguments that
you're using?

 

[Lisa]

Hi Ken,

Everyone is running the same database.

Here are the Action Arguments:
Transfer Type: Export
Spreadsheet Type: Microsoft Excel 97
Table Name: qryAccountsW/MV>2500
File Name: (path to file on shared server)
Has Field Names: Yes
Range:

Thanks!
-Lisa

 

[Ken Snell]

Nothing seems out of line in your code.

Could you post the exact error message (and error number, if any) that
you're getting? I'm leaning towards some type of directory access problem,
but not sure why your code would fail when your manual effort works.

 

[Lisa]

The error message I get is "Failure Creating File"

That's it...no codes or numbers.

-Lisa

 

[Ken Snell]

OK - post the exact path to the filename that you're using; may be a syntax
error.

In the meantime, let's do some testing to see if we can narrow down why this
error occurs.

1) change the macro's argument for the filename to this:
C:\Lisa.xls
does the file get created ok?

2) change the macro's spreadsheet type to a different EXCEL version. Does
the file get created ok?