Please read this message if you use docmd.TransferSpreadsheet

[Weiqin Xie]

I wrote a DoCmd.TransferSpreadsheet ... code to transfer
an Access table to Excel spreadsheet in my C:\Temp\
directory, it works as expected.

However, when I try to do the same thing to transfer the
table a directory with following path:
DoCmd.TrannsferSpreadsheet,acExport, _
acSpreadsheetTypeExcel97, "CompleteOrderRecords", _
"C:\Temp\Ways Consulting and Company\Old & New\
CompleteOrders.XLS"
It generates Run-time error '3027'...

When I remove the subdirecory "Old & New", it works again.

My co-workers and I also try to using multiple quatation
marks and other methods, it's all the same.

The conlcusion: the code simply can't handle multiple
spaces in consecutive subdirectory. Single space is OK.

 

[John Nurick]

Hi Weiqin,

I don't the the path is the problem (certainly not the arrangement of
spaces) unless you are using a very old version of Access. I've just
executed this command successfully:

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "Query1",
"C:\Documents and Settings\Administrator\My Documents\Holland 2003\F W
S\Name with more spaces and long\long file name with more
spaces.xls", True

But I can provoke
Run-time error '3027':
Cannot update. Database or object is read-only.
by using a second " at the end of the filename, i.e.

...\long file name with more spaces.xls""

 

[Van T. Dinh]

It is more likely to be the problem with the ampersand "&".

Try changing the "&" to "and".

 

[Weiqin xie]

Hi, John:

Thanks for your post. I forgot to make it clear that the
Access we are using is Access 97. I will go home to try on
Access 2000, 2002 and 2003.

Weiqin

-----Original Message-----
Hi Weiqin,

I don't the the path is the problem (certainly not the arrangement of
spaces) unless you are using a very old version of Access. I've just
executed this command successfully:

DoCmd.TransferSpreadsheet

acExport,acSpreadsheetTypeExcel9, "Query1",

"C:\Documents and Settings\Administrator\My Documents\Holland 2003\F W
S\Name with more spaces and long\long file name with more
spaces.xls", True

But I can provoke
Run-time error '3027':
Cannot update. Database or object is read-only.
by using a second " at the end of the filename, i.e.

...\long file name with more spaces.xls""

I wrote a DoCmd.TransferSpreadsheet ... code to transfer
an Access table to Excel spreadsheet in my C:\Temp\
directory, it works as expected.

However, when I try to do the same thing to transfer the
table a directory with following path:
DoCmd.TrannsferSpreadsheet,acExport, _
acSpreadsheetTypeExcel97, "CompleteOrderRecords", _
"C:\Temp\Ways Consulting and Company\Old & New\
CompleteOrders.XLS"
It generates Run-time error '3027'...

When I remove the subdirecory "Old & New", it works again.

My co-workers and I also try to using multiple quatation
marks and other methods, it's all the same.

The conlcusion: the code simply can't handle multiple
spaces in consecutive subdirectory. Single space is OK.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Weiqin Xie]

Hi, Van:

Thanks for your post. I tried to make a new subdirectory
which replaced "&" with "and". However, it still didn't
work. I forgot to indicate the Access we are using is
Access 97 (!). John Nurick already showed us he can
transfer table to a subdirectory with many spaces. I will
try the same thing on my home computers which were
installed with new versions of Access.

Weiqin

 

[Van T. Dinh]

I mean you change the "&" to "and" in BOTH Windows Explorer and your code!