Is there a way to generate an Excel file directly from a MS Access form?

[3c4j5d]

I currently have Access form that lists a series of reports that the users can run. Users click a command button to create these reports, and a.pdf is generated. I am wondering if it is possible to generate an .xlsx file instead of the .pdf? Also, I have an issue in coding the location to save the files, whether they be .pdf or .xlsx, because the users access the Access app through Citrix. This perhaps should be entered as a separate question? Thank you..

[NeoPa]

3C4J5D:
This perhaps should be entered as a separate question?

Absolutely.

As for generating Excel files the answer to that is it's certainly possible. With so little detail and no signs of any attempt to start this yourself though, I can only point you towards the DoCmd.TransferSpreadsheet() function at this stage. There are more options, but I don't have any question framework within which to share those.

[3c4j5d]

Currently, the code is using the DoCmd.OutputTo. I was trying to use this command, when I need to use the TransferSpreadsheet command is what you're saying.

DoCmd.OutputTo acOutputReport, "rpt_AuditCompletion", "PDF Format (*.pdf)", AdataLoc & "\MAAR-AuditCompletion.pdf", True, , , acExportQualityPrint

I think because I was able to change it from a .doc to a .pdf file, I thought it would be possible to somehow use the same command to create the .xlsx type file.

[NeoPa]

3C4J5D:
when I need to use the TransferSpreadsheet command is what you're saying

Indeed. If you were using the other before, then look at this one now (as OutputTo won't help here).

[3c4j5d]

Okay, thank you very much!

[3c4j5d]

I now have the following code producing a spreadsheet, but I cannot open the resulting Excel file. Here is the code I am using:

Private Sub AuditCompletionDetailCountManagers_Click()
 
   AdataLoc = "N:\My Documents"
 
    Mo = Format(Now(), "mm")
    Dy = Format(Now(), "dd")
    Yr = Format(Now(), "yyyy")
 
    ExcelFilePath = AdataLoc & "\AuditCompDetailCountMgrs" & Yr & Mo & Dy & ".xlsx"
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_AuditCompletionDetailCountMgrs", ExcelFilePath, True
    DoCmd.SetWarnings True
 
End Sub

It looks like the Excel file is created, with correct filename, extension, location. The size is 1790 MB. But when trying to open it, this message is generated: "Excel cannot open the file 'AuditCompDetailCountMgrs20120222.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." Can you see any reason this may be caused? Thank you!

[JayF]

I can't help, sorry, I came to this topic for an answer myself. But did you actually mean to say it's producing a 1.7Gb xlsx? Or did you mean 1,790Kb?

[3c4j5d]

Jay,
Sorry, I did mean to say 1790Kb. Thanks!

[Mihail]

Try that (of course you must adapt to your needs):

Public Sub CreateExcelWorkbook()
' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
' In VBE, goto Tools->References... and select it from the list
On Error GoTo ErrorHandler
    ' If Excel is open, use GetObject, otherwise create a new Excel object
    Set objExcel = GetObject(, "Excel.Application")
 
    With objExcel
        ' Adds a new workbook to the Excel environment
        .Workbooks.Add
        .ActiveWorkbook.SaveAs ("C:\TestareExcel.xlsx")
        ' Excel VBA Code goes here
 
        ' Causes the Excel window to become visible
        .Visible = True
    End With
Ex:
Exit Sub
 
ErrorHandler:
    Select Case Err.Number
        Case 429 'Application not runing
            Resume Next
        Case Else
            MsgBox (Err.Number & " " & Err.Description)
            Resume Ex
    End Select
End Sub

This is an adaptation from a NeoPa's article (Application Automation).

[3c4j5d]

Mihail,
Thanks for your reply. That generated a file that could be opened. Thank you!

[3c4j5d]

I am still having trouble getting the Access query to generate the Excel spreadsheet and open it up. With the code below, the Excel application must be running to work; a blank workbook called ExcelFilePath is created, and it is necessary to go to the location of the saved file and open it there.

Private Sub AuditCompletionDetailCountManagers_Click()
'AdataLoc = Environ("APPDATA")
   AdataLoc = "N:\My Documents"
 
On Error GoTo ErrorHandler
Set objExcel = GetObject(, "Excel.Application")
With objExcel
    .Workbooks.Add
    .ActiveWorkbook.SaveAs ("ExcelFilePath.xlsx")
    '.ActiveWorkbook.SaveAs ExcelFilePath
 
    Mo = Format(Now(), "mm")
    Dy = Format(Now(), "dd")
    Yr = Format(Now(), "yyyy")
 
'Export audit completion detail report counts of managers in Excel
 
    ExcelFilePath = AdataLoc & "\AuditCompDetailCountMgrs" & Yr & Mo & Dy & ".xlsx"
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QRY_AuditCompletionSummaryDetailMgrs", ExcelFilePath, True
    DoCmd.SetWarnings True
    .Visible = True
End With
Ex:
Exit Sub
 
ErrorHandler:
    Select Case Err.Number
        Case 429 'Application not running
            Resume Next
        Case Else
            MsgBox (Err.Number & " " & Err.DESCRIPTION)
            Resume Ex
    End Select
 
End Sub

[NeoPa]

I would suggest that the DoCmd.TransferSpreadsheet() should be run before you try to open the workbook up in Excel. This thread should be focused on getting this part to work, and not on any surrounding issues. As previously discussed, that is a topic for another thread.

[Mihail]

Maybe you'll find useful information here:
http://bytes.com/topic/access/answer...el#post3705220
even this thread is yet under work.

[NeoPa]

That's certainly possible, but nothing really relevant to this thread, which is about getting the DoCmd.TransferSpreadsheet() call to work correctly.