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..
13 2100 NeoPa 32,556
Expert Mod 16PB 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.
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 32,556
Expert Mod 16PB 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).
Okay, thank you very much!
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!
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?
Jay,
Sorry, I did mean to say 1790Kb. Thanks!
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).
Mihail,
Thanks for your reply. That generated a file that could be opened. Thank you!
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 32,556
Expert Mod 16PB
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.
NeoPa 32,556
Expert Mod 16PB
That's certainly possible, but nothing really relevant to this thread, which is about getting the DoCmd.TransferSpreadsheet() call to work correctly.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: LBT |
last post by:
Hi experts,
With response.contenttype = "application/vnd.ms-excel", I can easily export
my data to Excel which is embeded and shown within a browser.
If I'm going to save the Excel file...
|
by: Infinito |
last post by:
It's possible to encapsulate Excel in a Access Form so that I can write on
the EXCEL sheet?
Thanks
|
by: John Bailo |
last post by:
I want to write an Excel file (.xls format) from some database data.
I don't want to use Excel.exe because of all the automation and security
issues.
Does Microsoft document the .xls file...
|
by: cgrider |
last post by:
I have an access databae that I am creating to import data from the end user from an excel file. And from that generate a report for him to send to a customer. The issue that I am having the report...
|
by: radraq |
last post by:
Hello,
I have just completed a survey in excel which I've uploaded to my website.
The propblem is I would like the excel file to open directly online without asking for paswords and save the...
|
by: radraq |
last post by:
Hello,
I have just completed a survey in excel which I've uploaded to my website.
The propblem is I would like the excel file to open directly online without asking for paswords and save the...
|
by: Supermansteel |
last post by:
I have created an Form and Input sheet to be able to input Pricing informtion for 1st and 2nd Mortgages. However, I have recently come accross that there are about 13 states that have different...
|
by: brunpam |
last post by:
Hello,
I'm somewhat new to Access and I'm trying to open an Excel file from an Acess form command button. The only problem is the Excel filename includes a date and the date changes on a weekly...
|
by: Soniad |
last post by:
Hi,
I want to open an excel file from ASP Page, direclty.
Means, Now I can open excel file on onclick by giving its path. But,it's prompting a dialog box with "Open/Save/Cancel" Option.
...
|
by: Kartheepan |
last post by:
How to import the excel file in vb form ?
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |