Bill,
You are on the right track. I went back and looked at another procedure I wrote that is working and I changed the line to this:
DoCmd.TransferSpreadsheet acExport, 10, "qryRebrandExport", strFileName
I left out the acExport in the previous one, I think that was causing the 3011 error. The version affected what error messages I was getting when I tried to open the spreadsheet but this one works. Thanks for pointing me in the right direction.
---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :
Hello Pros,
I am trying to export a query to a shared network folder. My procedure errors with error 3011 "The Microsoft database engine could not find the object \\RSIPRFP001\Accounting\Accnting\2016 Month End\2016 H August\LTL Rebrand Trailers.xlsx which is the file name I'm trying to save. The procedure errors on the line that says "DoCmd.TransferSpreadsheet.. The file path is correct. Here is the procedure:
Private Sub btnLTLRebrandExport_Click()
'Exports Rebranded trailer info to Excel
Dim Spath As String
Dim SMonth As String
Dim StrMonthValue As String
Dim strFileName As String
Dim strSQL As String
Dim qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("qryRebrandExport")
strSQL = "SELECT ReconPRIMARYTABLE.CUSTID, ReconPRIMARYTABLE.UNITID, ReconPRIMARYTABLE.Door, ReconPRIMARYTABLE.CompletionDate, IIf([CUSTID]=""CTS"",334,378) AS Price, IIf([CUSTID]=""CTS"",125,0) AS Delivery, IIf([Door]=True,275,0) AS DoorPrice, [price]+[Delivery]+[DoorPrice] AS TotalPrice, Round([TotalPrice]*0.095,2) AS SalesTax, [TotalPrice]+[SalesTax] AS Total " & vbCrLf & _
"FROM ReconPRIMARYTABLE " & vbCrLf & _
"WHERE (((ReconPRIMARYTABLE.CUSTID)=""CTS"") AND ((ReconPRIMARYTABLE.CompletionDate) Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#));"
qd.SQL = strSQL
' Creates LTLRebrandSMonth.xlsx in n:\Accnting\month end folder
'Check for EOM Year folder if not there, create it.
If Len(Dir("\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End", vbDirectory)) = 0 Then
'Make directory
MkDir "\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End"
End If
'Check for EOM Month folder, if not there, create it.
'First create SMonth which is the variable name of the monthly folder
'Calculate SMonth
StrMonthValue = DatePart("m", Me.Start_Date)
Select Case StrMonthValue
Case 1
SMonth = "A January"
Case 2
SMonth = "B February"
Case 3
SMonth = "C March"
Case 4
SMonth = "D April"
Case 5
SMonth = "E May"
Case 6
SMonth = "F June"
Case 7
SMonth = "G July"
Case 8
SMonth = "H August"
Case 9
SMonth = "I September"
Case 10
SMonth = "J October"
Case 11
SMonth = "K November"
Case Else
SMonth = "L December"
End Select
'check for monthly folder, if not there, create it.
If Len(Dir("\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End\" & DatePart("yyyy", Date) & " " & SMonth, vbDirectory)) = 0 Then
'Make directory
MkDir "\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End\" & DatePart("yyyy", Date) & " " & SMonth
End If
Spath = "\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End\" & DatePart("yyyy", Date) & " " & SMonth & "\"
'strFileName = Spath & "LTL_Rebrand_" & Mid(SMonth, 3, 3) & DatePart("yyyy", Me.Start_Date) & ".xlsx"
strFileName = Spath & "LTL Rebrand Trailers.xlsx"
'Debug.Print strSQL
'DoCmd.TransferSpreadsheet , acExport, 10, strSQL, strFileName
Debug.Print Spath
Debug.Print strFileName
If FileExists(strFileName) = True Then Kill strFileName
'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, "qryRebrandExport", strFileName
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, "qryRebrandExport", "\\rsiprfp001\Accounting\ACCNTING\2016 Month End\2016 H August\LTL Rebrand Trailers.xlsx"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, "qryRebrandExport", "C:\databases\LTL Rebrand Trailers.xlsx"
Set qd = Nothing
End Sub
The part that checks for the path and creates the folder if needed works fine. I've tested it and used it in other procedures. I just can't get the query to transfer.
Doyce
Posted by: winberry.doyce@con-way.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
