Trying to export to Excel

[BigCat]

I have this following code for my Export button, and right now I keep getting the error that says Access can't find the object 'Monthly Production Summary' since that is the one I am trying to check and see if this works since it is first in the list.

Private Sub Export_Click()
Dim reportName As String
Dim myFilePath As String
Select Case Me!Frame27.Value
Case 1
reportName = "Monthly Production Summary"
 
Case 2
reportName = "Monthly C-O-S Summary"
 
Case 3
reportName = "Monthly Sales Summary"
 
Case 4
reportName = "Monthly Backlog Summary"
 
Case Else
MsgBox ("Please select a report first.")
End Select
 
myFilePath = myFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, myFilePath, True
MsgBox "Your report should be on the desktop now."
 
End Sub

[alansidman]

Do you have the spelling of the report/query exactly the same as the object name. Spaces, etc.

Also, I noticed that you don't have your path to where you want the report exported to in your code.

myfilepath = to something similar to

 C:\Documents and Settings\Alan M Sidman\Desktop\

It should be inserted just above the line myfilepath.

[BigCat]

I think I might have found the real problem, but I am not sure how to fix it either.

In the code, the reportName is pointing at a stored procedure that then needs to execute which will produce a query and that is what I need exported. So I need to tell it to execute the procedure and then store the query into something and then export that.

[alansidman]

You probably need to add the procedure to your code in advance of the export code.

Alan

[BigCat]

I am focusing still just on the first case and trying to get it to work and then code the rest. Here is what I am trying now and it gives me an error and tells me it cannot find the stored procedure.

Private Sub Export_Click()

Dim reportName As String
Dim myFilePath As String
Dim rst As ADODB.Recordset
Dim strSproc As String

Select Case Me!Frame27.Value
    Case 1
    strSproc = "execute dbo.spMonthlyProductionSummary"
    reportName = "MonthlyProductionSummary"
    Set rst = New ADODB.Recordset
    rst.Open strSproc, Accounting - cs_2007.Connection
    
    Case 2
    reportName = "Monthly C-O-S Summary"
    
    Case 3
    reportName = "Monthly Sales Summary"
    
    Case 4
    reportName = "Monthly Backlog Summary"
    
    Case Else
    MsgBox ("Please select a report first.")
    End Select
    
myFilePath = "C:\Documents and Settings\ALuhman\Desktop\"
myFilePath = myFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
      
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, myFilePath, True

MsgBox "Your report should be on the desktop now."
    
End Sub

[alansidman]

This latest event is a little beyond me. I hope that someone else can step in here and advise on this situation, but I think that you need to set your process execution in advance of the Case select statement or probably after the End Select Line. After all the Case Select is only identifying which report to run. I don't think you want to have the process as part of the select statement.
Alan

[khalid]

myFilePath = "C:\Documents and Settings\ALuhman\Desktop\"
myFilePath = myFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"

End Sub

Hi,
check the excel file extension you want to export to.... it should be .xlsx not .xls for microsoft excel 2007.

Also please check if you have already have already these queries for opening your reports. You can export query data to excel, not a report.

This might help.

[BigCat]

I have now just made the queries instead of trying to run a stored procedure that would produce the query, but I am still having a problem where when I run the code it pops up and tells me it cannot find the object.

Private Sub Export_Click()
Dim reportName As String
Dim myFilePath As String
Select Case Me!Frame27.Value
    Case 1
    reportName = "Monthly Production Summary"
    DoCmd.OpenQuery "Monthly Production Summary"
    
    Case 2
    reportName = "Monthly C-O-S Summary"
    DoCmd.OpenQuery "Monthly C-O-S Summary"
    
    Case 3
    reportName = "Monthly Sales Summary"
    DoCmd.OpenQuery "Monthly Sales Summary"
    
    Case 4
    reportName = "Monthly Backlog Summary"
    DoCmd.OpenQuery "Monthly Backlog Summary"
    
    Case Else
    MsgBox ("Please select a report first.")
    End Select
    
myFilePath = "C:\Documents and Settings\ALuhman\Desktop\"
myFilePath = myFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
      
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, myFilePath, True
MsgBox "Your report should be on the desktop now."
    
End Sub

[nicknameoscar]

Just a total shot in the dark:

acSpreadsheetTypeExcel9 - Access help indicates that means you are telling Access to export in Excel 2000 format. I would think you could NOT use the .xlsx file extension but maybe I am wrong.

[NoellaG]

Hi,

I'm a bit confused with the use of stored procedures: stored procedures can produce a dataset, but don't create access queries.
If you want to use a stored procedure, I have an example of code that uses stored procedures to produce an output to Excel:

                If strReport = "rpt_StockList" Then
                    strReport = "exec p_exportStockList " & Me!subCriteria.Form.getWarehouse()
                ElseIf strReport = "rpt_StockListDangerCodes" Then
                    strReport = "EXEC p_exportDangerStockList " & Me!subCriteria.Form.getWarehouse()
                ElseIf strForm = "rpt_StockListRClauses" Then
                    intDGO = Nz(Me!subCriteria.Form!chkDangercodes, 0)
                    strForm = "EXEC p_exportDangerClauseStockList " & Me!subCriteria.Form.getWarehouse() & ",'R'," & intDGO
                ElseIf strReport = "rpt_StockListCust" Then
                    strReport = "EXEC p_exportCustStockList " & Me!subCriteria.Form.getCustomer()
                ElseIf strForm = "rpt_Moves_PO" Then
                    strForm = "p_exportMovesPO"
                ElseIf strForm = "rpt_Moves_IO" Then
                    strDate1 = Me!subCriteria.Form.getCritDate(1)
                    strDate2 = Me!subCriteria.Form.getCritDate(2)
                    strCrit = CStr(Me!subCriteria.Form.getCustomer()) & ",'" & strDate1 & "','" & strDate2 & "'"
                    strReport = "EXEC p_exportMovesIO " & strCrit
                ElseIf strReport = "xlsEnvironmentRpt" Then
                    strReport = "EXEC p_EnvironmentRpt"
                End If
                strFilename = gfgSaveFile("Save as", "Excel files(*.xls)|*.xls")
                If Len(strFilename) = 0 Then
                    MsgBox "No valid file"
                    Exit Sub
                End If
                If Right(strFilename, 4) <> ".xls" Then strFilename = strFilename & ".xls"
                DoCmd.OutputTo acOutputStoredProcedure, strReport , "MicrosoftExcel(*.xls)", strFilename, True

Of course to view the procedures you need an ADP project and not an mdb database.
Greetings
NG

[nicknameoscar]

Again, totally out of my realm but look at post #10 in the below thread:

http://www.mrexcel.com/forum/showthread.php?t=264481

That appears to allow you to export in .xlsx format.

I tried the below code and it worked.

Dim reportName As String
Dim myFilePath As String
reportName = "qryJudges"
myFilePath = "C:\Documents and Settings\MyName\My Documents\Magic Briefcase\Access\"
myFilePath = myFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
MsgBox myFilePath
DoCmd.TransferSpreadsheet acExport, 10, reportName, myFilePath, True
MsgBox "Your report should be on the desktop now."

[BigCat]

I have gotten rid of what I was trying before and now I am just giving each query its own button. I have now gotten rid of the errors I was getting before but I can't tell if anything is exporting and I don't think it is actually is. Anyone have suggestions?

Private Sub ExportMPS_Click()
Dim stDocName As String
Dim theFilePath As String
stDocName = "Monthly Production Summary"
DoCmd.OpenQuery stDocName
 
MsgBox "Look on your desktop for the report."
 
theFilePath = "C:\Documents and Settings\ALuhman\Desktop\"
theFilePath = theFilePath & stDocName & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel10, reportName, theFilePath, True
 
End Sub

[nicknameoscar]

Monthly Production Summary is in fact an Access query - right?

You should have a file named Monthly Production Summary.xlsx in the C:\Documents and Settings\ALuhman\Desktop\ directory.

The query I used was an actual Access query and the xlsx file contained the recordset of that query.

Maybe I am misunderstanding what you trying to do because I am not familiar with stored procedures.

I tried using acSpreadsheetTypeExcel10 in my code and it said Variable not defined. Try using just the number 10.

[nicknameoscar]

I can't remember if you said earlier but does your query return a recordset if you run it normally through the Access user interface?

[BigCat]

I tried to comment out the code below my openQuery line and tried running it and I am not sure if that statement is even working, because when I click the buttong I don't see my query opening anywhere. Please help I have been banging my head against this wall for much to long now.