I have export code in Access 2003 that creates an Excel export.
It works fine in XP pro - does not work in Vista business
I get the message:
The Microsoft Jet database engine cannot open the file c:\Users\Ansley\Documents\05_08_08.xls. It is already opened exclusively by another user, or you need permission to view it's data.
Here is the code:
Public Function ExportFile(ExportType As String) As Boolean
Dim strSQL As String
Dim DPath As String
Dim sl As String
Dim response As String
Dim Complete As Boolean
DPath = "C:\Users\Ansley\Documents\"
DoCmd.SetWarnings False
strSQL = "DELETE tblExport_1.* FROM tblExport_1;"
DoCmd.RunSQL (strSQL)
response = MsgBox("You are going to create an export in Excel. Do you want to continue?", vbYesNo, "Export")
If response = vbYes Then
FName = InputBox(Prompt:="What file name do you want to use?", _
Title:="File Name", Default:=Format(Date, "MM_DD_YY") & ".xls")
If FName = "" Or IsNull(FName) Then
MsgBox "Exiting - no file name given.", vbCritical
Exit Function
End If
Path = InputBox(Prompt:="Where do you want to save the file?", _
Title:="File Path", Default:=DPath)
If Path = "" Or IsNull(Path) Then
MsgBox "Exiting - no path given.", vbCritical
Exit Function
End If
DoCmd.SetWarnings False
If Nz(ExportType, "") = "Standard" Then
If Me.chkGift = -1 Then
DoCmd.OpenQuery "qryExport_3"
Else
DoCmd.OpenQuery "qryExport_1_NoGift"
End If
Else
If ExportType = "Entered" Then DoCmd.OpenQuery "qryGiftsEnteredToday"
If ExportType = "Received" Then DoCmd.OpenQuery "qryGiftsReceivedToday"
End If
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, , "tblExport_1", Path & FName, True
MsgBox "Table " & FName & " " & "Exported to " & Path, vbInformation, "Export Information"
Else
MsgBox "Did not create export.", vbCritical
End If
DoCmd.SetWarnings True
ExportFile = Complete
Exit_cmdExport_Click:
Exit Function
Err_CmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click
DoCmd.SetWarnings True
End Function
Any suggestions? I do not think it has anything to do with security or rights.
Thanks
It works fine in XP pro - does not work in Vista business
I get the message:
The Microsoft Jet database engine cannot open the file c:\Users\Ansley\Documents\05_08_08.xls. It is already opened exclusively by another user, or you need permission to view it's data.
Here is the code:
Public Function ExportFile(ExportType As String) As Boolean
Dim strSQL As String
Dim DPath As String
Dim sl As String
Dim response As String
Dim Complete As Boolean
DPath = "C:\Users\Ansley\Documents\"
DoCmd.SetWarnings False
strSQL = "DELETE tblExport_1.* FROM tblExport_1;"
DoCmd.RunSQL (strSQL)
response = MsgBox("You are going to create an export in Excel. Do you want to continue?", vbYesNo, "Export")
If response = vbYes Then
FName = InputBox(Prompt:="What file name do you want to use?", _
Title:="File Name", Default:=Format(Date, "MM_DD_YY") & ".xls")
If FName = "" Or IsNull(FName) Then
MsgBox "Exiting - no file name given.", vbCritical
Exit Function
End If
Path = InputBox(Prompt:="Where do you want to save the file?", _
Title:="File Path", Default:=DPath)
If Path = "" Or IsNull(Path) Then
MsgBox "Exiting - no path given.", vbCritical
Exit Function
End If
DoCmd.SetWarnings False
If Nz(ExportType, "") = "Standard" Then
If Me.chkGift = -1 Then
DoCmd.OpenQuery "qryExport_3"
Else
DoCmd.OpenQuery "qryExport_1_NoGift"
End If
Else
If ExportType = "Entered" Then DoCmd.OpenQuery "qryGiftsEnteredToday"
If ExportType = "Received" Then DoCmd.OpenQuery "qryGiftsReceivedToday"
End If
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, , "tblExport_1", Path & FName, True
MsgBox "Table " & FName & " " & "Exported to " & Path, vbInformation, "Export Information"
Else
MsgBox "Did not create export.", vbCritical
End If
DoCmd.SetWarnings True
ExportFile = Complete
Exit_cmdExport_Click:
Exit Function
Err_CmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click
DoCmd.SetWarnings True
End Function
Any suggestions? I do not think it has anything to do with security or rights.
Thanks
