By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

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

P: 26
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..
Feb 22 '12 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 22 '12 #2

P: 26
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 22 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
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).
Feb 22 '12 #4

P: 26
Okay, thank you very much!
Feb 22 '12 #5

P: 26
I now have the following code producing a spreadsheet, but I cannot open the resulting Excel file. Here is the code I am using:
Expand|Select|Wrap|Line Numbers
  1. Private Sub AuditCompletionDetailCountManagers_Click()
  2.  
  3.    AdataLoc = "N:\My Documents"
  4.  
  5.     Mo = Format(Now(), "mm")
  6.     Dy = Format(Now(), "dd")
  7.     Yr = Format(Now(), "yyyy")
  8.  
  9.     ExcelFilePath = AdataLoc & "\AuditCompDetailCountMgrs" & Yr & Mo & Dy & ".xlsx"
  10.  
  11.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_AuditCompletionDetailCountMgrs", ExcelFilePath, True
  12.     DoCmd.SetWarnings True
  13.  
  14. 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!
Feb 22 '12 #6

P: 45
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?
Feb 22 '12 #7

P: 26
Jay,
Sorry, I did mean to say 1790Kb. Thanks!
Feb 22 '12 #8

100+
P: 759
Try that (of course you must adapt to your needs):

Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateExcelWorkbook()
  2. ' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
  3. ' In VBE, goto Tools->References... and select it from the list
  4. On Error GoTo ErrorHandler
  5.     ' If Excel is open, use GetObject, otherwise create a new Excel object
  6.     Set objExcel = GetObject(, "Excel.Application")
  7.  
  8.     With objExcel
  9.         ' Adds a new workbook to the Excel environment
  10.         .Workbooks.Add
  11.         .ActiveWorkbook.SaveAs ("C:\TestareExcel.xlsx")
  12.         ' Excel VBA Code goes here
  13.  
  14.         ' Causes the Excel window to become visible
  15.         .Visible = True
  16.     End With
  17. Ex:
  18. Exit Sub
  19.  
  20. ErrorHandler:
  21.     Select Case Err.Number
  22.         Case 429 'Application not runing
  23.             Resume Next
  24.         Case Else
  25.             MsgBox (Err.Number & " " & Err.Description)
  26.             Resume Ex
  27.     End Select
  28. End Sub
This is an adaptation from a NeoPa's article (Application Automation).
Feb 23 '12 #9

P: 26
Mihail,
Thanks for your reply. That generated a file that could be opened. Thank you!
Feb 23 '12 #10

P: 26
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub AuditCompletionDetailCountManagers_Click()
  2. 'AdataLoc = Environ("APPDATA")
  3.    AdataLoc = "N:\My Documents"
  4.  
  5. On Error GoTo ErrorHandler
  6. Set objExcel = GetObject(, "Excel.Application")
  7. With objExcel
  8.     .Workbooks.Add
  9.     .ActiveWorkbook.SaveAs ("ExcelFilePath.xlsx")
  10.     '.ActiveWorkbook.SaveAs ExcelFilePath
  11.  
  12.     Mo = Format(Now(), "mm")
  13.     Dy = Format(Now(), "dd")
  14.     Yr = Format(Now(), "yyyy")
  15.  
  16. 'Export audit completion detail report counts of managers in Excel
  17.  
  18.     ExcelFilePath = AdataLoc & "\AuditCompDetailCountMgrs" & Yr & Mo & Dy & ".xlsx"
  19.  
  20.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QRY_AuditCompletionSummaryDetailMgrs", ExcelFilePath, True
  21.     DoCmd.SetWarnings True
  22.     .Visible = True
  23. End With
  24. Ex:
  25. Exit Sub
  26.  
  27. ErrorHandler:
  28.     Select Case Err.Number
  29.         Case 429 'Application not running
  30.             Resume Next
  31.         Case Else
  32.             MsgBox (Err.Number & " " & Err.DESCRIPTION)
  33.             Resume Ex
  34.     End Select
  35.  
  36. End Sub
Feb 24 '12 #11

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 24 '12 #12

100+
P: 759
Maybe you'll find useful information here:
http://bytes.com/topic/access/answer...el#post3705220
even this thread is yet under work.
Feb 24 '12 #13

NeoPa
Expert Mod 15k+
P: 31,494
That's certainly possible, but nothing really relevant to this thread, which is about getting the DoCmd.TransferSpreadsheet() call to work correctly.
Feb 24 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.