TransferSpreadsheet to Desktop problem

[LEXCERM]

Hi there,

I have a bunch of code which transfers data to an Excel file via queries.

When I select, say, 'C' drive (any drive/folder) the code executes without problems and I can run this over-and-over again.

However, if I try and save to desktop (redirection), it may work the first time but then errors thereafter: 3274 - external table is not in the expected format

It seems to fail around here and not on any particular line:-

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_Export1", strFilename
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_Export2", strFilename
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_Export3", strFilename

If I close the DB then open it again it seems to reset itself, works the first time and then errors next time.

I also use this code to capture trailing '\' in the filename:-

If Right(strFilename, 1) = "\" Then
strFilename = strFilename & myExportfilename
Else
strFilename = strFilename & "\" & myExportfilename
End If

Thanks in advance and regards.

 

[Ranman256]

what file path are you using? (C:\Users\userName\Desktop\)
what file NAME are you using? (xl12 = .xlsx)

 

[LEXCERM]

Hi Ranman256 and thanks for replying.

The filename that's produced is something like:-
\\.....local\\...\FolderRedirection01\[username]\Desktop.

I've used both the following, ending .xlsx:-
acSpreadsheetTypeExcel12Xml
acSpreadsheetTypeExcel12

All permutations work when "not" selecting the Desktop, i.e. H:\, or C:\ etc.

I've also tried using something like:-
CreateObject ("WScript.Shell").specialfolders("Desktop").

Really odd, but I'm sure there's an explanation somewhere.

Kind regards.