[MS_AccessPros] Re: Runtime Error 3011 "Cannot find the object..."

 

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.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Doyce - While it's complaining about the path it just might be the excel version argument. Replace 10 with the actual enum type. In my case using Office 2010 it's acSpreadsheetTypeExcel12

Using the literal argument instead of its numeric value is usually a safer bet because those stay the same through version changes while numerics could change.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

---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)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___