By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Trying to save Access Data to Excel

P: 2
Hi Folks,
This is my first post so please be patient with me. I am not a developer but trying to look for some guidance and think you might be able to help me.

One of the functions of our Access application allows the user to save some data to an Excel spreadsheet with the file name of their choice. Pressing the command button opens the Save As window and lets the user select where to save the file and what to call it. Here's the problem:

Some users can't do this and get the 438 Error message. I can't figure out what the computers that can do this process have that the others don't. Some have Windows 2k and some have Win XP (but the operating system doesn't seem to be an issue) all have Office XP SP3.

Does this ring any bells for anyone. Where can I look? What can I try. Any help will be greatly appreciated.

Here's the code if this helps at all:

Private Sub cmdExcelAudit_Click()
Dim SQL As String
Dim strFileName As String

On Error GoTo Err_cmdExcelAudit_Click

'Ask for SaveFileName
comDl.FileName = ""
comDl.ShowSave
'If comDl.Cancel Then
' Exit Sub
'End If
strFileName = comDl.FileName & ".xls"
If strFileName = ".xls" Then
Exit Sub
End If
If Right(strFileName, 8) = ".xls.xls" Then
strFileName = Left(strFileName, Len(strFileName) - 4)
End If

SQL = "qryExcelAuditTrail"

DoCmd.OutputTo acOutputQuery, SQL, acFormatXLS, strFileName, False

MsgBox strFileName & " created successfully", vbInformation


Exit_cmdExcelAudit_Click:
Exit Sub

Err_cmdExcelAudit_Click:
MsgBox "cmdExcelAudit_Click - Error Number = " & Err.Number & ", Error Description = " & Err.Description
Resume Exit_cmdExcelAudit_Click

End Sub

Thanks,
Nelson
Apr 30 '07 #1
Share this Question
Share on Google+
3 Replies


Dököll
Expert 100+
P: 2,364
Hi Folks,
This is my first post so please be patient with me. I am not a developer but trying to look for some guidance and think you might be able to help me.

One of the functions of our Access application allows the user to save some data to an Excel spreadsheet with the file name of their choice. Pressing the command button opens the Save As window and lets the user select where to save the file and what to call it. Here's the problem:

Some users can't do this and get the 438 Error message. I can't figure out what the computers that can do this process have that the others don't. Some have Windows 2k and some have Win XP (but the operating system doesn't seem to be an issue) all have Office XP SP3.

Does this ring any bells for anyone. Where can I look? What can I try. Any help will be greatly appreciated.

Here's the code if this helps at all:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdExcelAudit_Click()
  3. Dim SQL         As String
  4. Dim strFileName As String
  5.  
  6.     On Error GoTo Err_cmdExcelAudit_Click
  7.  
  8.     'Ask for SaveFileName
  9.     comDl.FileName = ""
  10.     comDl.ShowSave
  11.     'If comDl.Cancel Then
  12.     '    Exit Sub
  13.     'End If
  14.     strFileName = comDl.FileName & ".xls"
  15.     If strFileName = ".xls" Then
  16.         Exit Sub
  17.     End If
  18.     If Right(strFileName, 8) = ".xls.xls" Then
  19.         strFileName = Left(strFileName, Len(strFileName) - 4)
  20.     End If
  21.  
  22.     SQL = "qryExcelAuditTrail"
  23.  
  24.     DoCmd.OutputTo acOutputQuery, SQL, acFormatXLS, strFileName, False
  25.  
  26.     MsgBox strFileName & " created successfully", vbInformation
  27.  
  28.  
  29. Exit_cmdExcelAudit_Click:
  30.     Exit Sub
  31.  
  32. Err_cmdExcelAudit_Click:
  33.     MsgBox "cmdExcelAudit_Click - Error Number = " & Err.Number & ", Error Description = " & Err.Description
  34.     Resume Exit_cmdExcelAudit_Click
  35.  
  36. End Sub
  37.  
  38.  
Thanks,
Nelson
Hello NelsonP!

I Googled your error and came up with this:

http://support.microsoft.com/kb/254798

This should handle some of the problems you are facing. I wondered if you could tell us a little more about these problems though.

I will check in a bit, do some reading see what I come up with. Do your users get the error straight away or does the code run for a bit. I would also suggest seeing what your code is doing through Debug mode (Debug, Step Into)

Dököll
May 1 '07 #2

P: 2
Thanks for the info but I found the solution.

I had to register comdlg32.ocx in the system32 folder.

Thanks again,
Nelson
May 1 '07 #3

Dököll
Expert 100+
P: 2,364
Thanks for the info but I found the solution.

I had to register comdlg32.ocx in the system32 folder.

Thanks again,
Nelson
Excellent, and nice of you to post your solution for all to see. Others will likely benefit:-)
May 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.