TransferSpreadsheet path size limit?

J

Jay

Hi,
I'm using a Access 97 to export a query with the transferspreadsheet command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryCBGandPendingReclaims", Destination & FileName

I think the limit to the destination path (Destination & Filename) is 64
characters? Can anyone confirm that this 64 character limit is only in Access
97? and if there's a way around it?

I know Access2000 exceeds this limit , but this database needs to be run on
A97.

Thanks,
Jay
 
J

John Nurick

I don't recall a 64-character limit on Excel paths in Access 97 (there
was for dBASE paths). You can work round by using the equivalent short
path, most easily obtainable with this little bit of code posted
recently by Pieter Wijnen:

Private Declare Function APIGetShortPath Lib "kernel32" _
Alias "GetShortPathNameA" _
(ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long) As Long

Public Function GetShortPath(ByVal LongName As String) As String
Dim ShortName As String * 256

APIGetShortPath LongName & VBA.vbNullChar, _
ShortName, VBA.Len(ShortName)
GetShortPath = VBA.Left(ShortName, _
VBA.InStr(ShortName, VBA.vbNullChar) - 1)
End Function
 
D

Douglas J. Steele

I didn't recall it either, but I did some testing and sure enough, when the
path was greater than 64 characters, it didn't work. Unfortunately, using
the Short Path won't always bring it down below 64 characters: it might also
be necessary to ensure that the target location isn't buried too deeply in
subfolders.

If the user has no control over where the spreadsheet needs to be located,
it's always possible to write to a "short" location, then move the resultant
workbook to the proper location once done.
 
J

Jay

Thanks John and Doug.

I did some further testing with the user's PC, somehow that line of code ran
fine (with file path greather than 64 characters).

Now the difference in our Office setup is that my PC has A97 and A2000, and
the user has A97 only. Doug, you must have multiple versions of Access on
your PC also, I would think.

Could it be that some of the Access DLL files are mixed up in the system?

-Jay
 
D

Douglas J. Steele

I've only got Access 97 onn the machine on which I tested (although all the
other Office products on it are Office 2003).
 
J

Jay

The user's PC has Access 97 and other Office 2000 products.
My machine has A97, A2000, and other Office 2000 products also.

I still don't get why it wouldn't work on my PC. Doug, if you can find
something out about this bug, please let me know on this forum. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top