Export to Excel Error 2302

[UWKC Admin]

I have an export to excel button in Visual basic (DoCmd.OutputTo
acOutputReport, strReport, acFormatXLS, strFileName, False), it is giving me
an error: “HAL Test (our application name) can't save the output data to the
file you've selected. 2302†This error pops up only if I try to export it to
c:\documents and settings directory. Works fine when I export it to c:\ or
any directories I create under c:\. I have full control permissions on the
system so I don't understand what is different about c:\documents and
settings. I found an article "ACC: Error Message Using OutputTo to Output a
Report to Another Format" but none of the resolution worked. Please help.

 

[Beth Melton]

I can't reproduce the error here using Access 2003 and Windows XP. Did
you try copying the path you want to use from the Address bar in an
Exploring window and pasting it in your code? Occasionally there's an
error but you just can't see it.

Otherwise what version of Access and Windows are your using? What
patches do you have installed? Can you copy/paste your code for us to
take a look at?

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

 

[UWKC Admin]

We are using XP Pro SP2 Build 2600 and Access 2003 (11.6355.6408) SP1. I
tried copying the path to the code but still got the same error. Here is the
code:

Public Sub ExportToExcel(strReport As String)
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Const strWindowTitle = "Export to Excel"
Dim strFileName As String

On Error GoTo Err_ExportToExcel

Set xlApp = CreateObject("Excel.Application")
strFileName = xlApp.GetSaveAsFilename("HAL_Export.xls", _
"Excel Files (*.xls), *.xls", , strWindowTitle)
Set xlApp = Nothing

If strFileName = "False" Then Exit Sub
If Right(Trim(UCase(strFileName)), 4) <> ".XLS" Then _
strFileName = Trim(strFileName) & "XLS"
strFileName = Replace(strFileName, " ", "_")

'Delete the file if it exists
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(strFileName) Then
If MsgBox("File exists. Delete it?", _
vbOKCancel, strWindowTitle) = vbCancel Then _
Exit Sub
fs.DeleteFile (strFileName)
End If


' Export
DoCmd.OutputTo acOutputReport, _
strReport, acFormatXLS, _
strFileName, False

 

[Beth Melton]

Okay, now that I have a sample of your code I have a better idea of
what is happening. I thought you were providing the path in the
procedure so my reference to copy/paste was in thinking your
strFileName variable was initialized with something like:

strFileName="C:\Documents and Settings\..."

But now I see that's not the case. I believe the problem doesn't lie
in the Document and Settings folder, the error will occur for any
folder that contains spaces in the path. This line of code:

strFileName = Replace(strFileName, " ", "_")

converts the spaces to underscores and that's causing the problem.
There is no folder named "Documents_and_Settings" which is why the
error occurs. I'm not sure why you are replacing the spaces, since the
path is getting passed correctly, but if you remove the line that uses
the Replace function your procedure works fine.

Also, FWIW, since you are using the standard SaveAs dialog from Excel
the .xls extension is added even if the user removes it or tries to
add their own. IOW this statement:

strFileName = Trim(strFileName) & "XLS"

will never be executed. Besides, if it were executed you'd be saving
files with "XLS" appended to the end of the filename and not treated
as the file extension since you didn't add the period (dot). i.e
".XLS" IOW, a file name of "Hal" would become "HalXLS" rather than
"HAL.XLS".

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

 

[UWKC Admin]

We added this line of code
strFileName = Replace(strFileName, " ", "_")
because users were getting an error when they put spaces in the filename.
For example, when user enter "Export for Elaine" as the filename, the error
"Export.xls could not be found." And if you choose to save it to your
desktop, an error "c:\documents.xls could not be found."
We didn't have this problem when we were using Windows 2000 and Office 2000.

 

[Beth Melton]

I don't encounter this error either. I can save files with file names
containing spaces to folders with names containing spaces in Access
2003/Windows XP.

The error you are describing is similar to a common error users
encounter when opening a file outside of the application. In this
situation the problem lies in the Registry rather than the application
and it's due to a syntax error.

I would comment out the line and if users encounter the error then try
the following:

Go to Start/Run and run:

excel /regserver

Note the space before the forward slash.

This command will recreate the file associations for Excel along with
recreating various Registry entries. If that doesn't work then let us
know and we can help you find the underlying problem since the issue
doesn't lie in your code.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

 

[UWKC Admin]

I did what you recommended, commented out strFileName = Replace(strFileName,
" ", "_") and ran excel /regserver, but still got the same error.

'c:\documents.xls' could not be found. Check the spelling of the file name,
and
verify that the location is correct.

I clicked OK and then another dialog box appears:

'and.xls' could not be found....

clicked OK and then:

'settings\elin\desktop\export.xls' could not be found....

clicked OK and then:

'12.xls' could not be found...

clicked OK and Excel opened a blank spreadsheet but when I go to my desktop,
export 12.xls is actually saved to my desktop with the correct export
information in it. So I guess it is working but it would confuss users when
they have to click OK on so many popup dialog boxes.

 

[Beth Melton]

Did you check your References?

I also noted It looks like you are using Excel just to access the Save
As dialog. A more common method is to call a standard Windows Save
dialog instead.

Here's the complete code if interested:
http://www.mvps.org/access/api/api0001.htm

Using their example you need something like:

Public Sub ExportToExcel(strReport As String)
Dim strFilter As String
Dim strInputFileName As String

'Ask for SaveFileName
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)",
"*.xls")
strSaveFileName = ahtCommonFileOpenSave( _
FileName:="Hal_Export", _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

DoCmd.OutputTo acOutputReport, _
strReport, acFormatXLS, _
strSaveFileName, False

End Sub

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/