Hi all, when exporting a query to Excel is it possible to perform page set up controls to the Excel sheet? Or will I need to create VBA page set up controls with in a Set objXL = New Excel.Application??:confused: :confused: :confused:
View RepliesI've been searching for a good solution for this and can't find it. Hopefully something like this is possible.
I need to produce a report in excel from my database that needs to include information from 5 different tables. The relationship between two of these tables is a many-to-many relationship connected by a junction table. (I hope that is the right terminology)
My report needs to be in a certain format and needs to include all fields in the same order for every record.
The problem arises because of the many-to-many relationship. I have a tblOrder and a tblAgent linked by the junction table tblAgentOrder. For every order there can between 1 and four different agents associated with the order which are stored in the junction table. In addition these agents will be of different types 1-4.
Ex: One order could have an agent of type 1 and an agent of type 4. While another order may only have one agent of type 2.
I need the query to give me the result of OrderID, DateOpened, AgentType1, AgentType2, AgentType3, AgentType4. Regardless of if the order actually had an AgentType3 or 4 associated with it. In this case the fields would be blank.
Right now I'm getting:
OrderID, DateOpened, AgentType1
OrderID, DateOpened, AgentType2
I hope this makes sense, but let me know if I need to clarify anything.
Thanks in advance.
I have a form that allows users to specify the information they want to be displayed on a subform. I have used the below code (simpilified for example purposes) to create the SQL string and sent it to the subform for display. I would like to have an export to excel button to export the recordSource to a spredsheet. is this possible?
Me.frmsubClients.Form.RecordSource = "SELECT DISTINCT New_Client_Table.Account_Number" & BuildFilter
Thank you in advance.
I am exporting queries to Excel using a Form with command buttons. The code for this effort follows:
Private Sub Command8_Click()
Dim reportName As String
Dim theFilePath As String
Select Case Me.Frame1.Value
Case 1
reportName = "qryPriorMnth"
Case 2
reportName = "qryNewRequests"
Case 3
reportName = "qryNoApprovals"
End Select
theFilePath = Me.txtFilePath.Value
theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True
MsgBox "Done."
End Sub
The code works great, however, I will be making this available to several users who will use access from differenct PCs. In order for them to get the data passed to their respective desktops, they would have to change the Me.txtfilePath.Value in the properties manaually. This is currently in the Row.Source for an unbound text box. I would prefer them not messing with that. The current value is
="C:Documents and Settings’User_ID’Desktop"
where the User_ID (varies from user to user) would need to be changed. Can this be done with a variable setting that would prompt the user for their User_ID and then execute to the appropriate desk top. I am a VBA novice and would appreciate any help at all.
Thanks
Alan
I am using the DoCmd.TransferSpreadsheet statement that successfully exports a query result set into excel. I was wondering is there a way to get this result to populate certain fields of an existing excel spreadsheet that I have created and need the information for. Right now the query just creates a new worksheet in the specified workbook.
View 2 Replies View RelatedHi folks, I need your help.
Did any one get a error message like the one below:
“
Run-time error ‘3061’:
Too few parameters. Expected 1.
“
Basically I have a form with a command button and a combo box. The combo box's row source property is set to the following sql:
SELECT DISTINCTROW tblProjts1.intProjectID, tblProjts1.chrProjectName FROM tblProjts1 ORDER BY tblProjts1.chrProjectName;
I have a command button which runs the following procedure (Thanks, credit goes to madrav72):
Private Sub cmdSendToExcel_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
'Set rs = db.OpenRecordset("qryOne", dbOpenSnapshot)
Set rs = db.OpenRecordset("qryTwo", dbOpenSnapshot)
'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With
oApp.Visible = True
oApp.UserControl = True
'Close the Database and Recordset
rs.Close
db.Close
End Sub
qryOne:
SELECT tblProjts1.intProjectId, tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblMaxLoad.*
FROM tblProjts1 INNER JOIN tblMaxLoad ON tblProjts1.intProjectId = tblMaxLoad.intProjectId;
qryTwo:
SELECT tblProjts1.intProjectId, tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblMaxLoad.*
FROM tblProjts1 INNER JOIN tblMaxLoad ON tblProjts1.intProjectId=tblMaxLoad.intProjectId
WHERE (((tblMaxLoad.intProjectId)=Forms!frmReprtSelen!cb oProj));
My problem: When I run cmdSendToExcel_Click procedure with “ Set rs = db.OpenRecordset("qryOne", dbOpenSnapshot) “ it loads the excel and export the data to Excel without any problem. The Excel spreadsheet will show the rows as follows:
ProjtIdProjName Column3Column4 Column5, etc
8Bechtel xxxyyy
9WOPS aaabbb
11Spring zzzttt
12AgP mmmnnn
But when I run with Set rs = db.OpenRecordset("qryTwo", dbOpenSnapshot), I get the error
“Run-time error ‘3061’:
Too few parameters. Expected 1.”
I even tested the qryTwo using a separate command button with the following code on the click event:
stDocName = "qryTwo"
DoCmd.OpenQuery stDocName, acNormal, acEdit
and it produces the single row based on the projectId selected on combo box (as shonw below in datasheet view):
ProjtIdProjName Column3Column4 Column5, etc
8Bechtel xxxyyy
Does any one have any clue what this error 3061 is? Please help. I am struggling almost a week to figure this out, NO LUCK.
Thanks for your time and help in advance.
Shan.
Hi all,
I'm not very experienced with Access. I'm looking for a very simple sample of how to export Query results to an Excel Template document. Especially to specific cell references. Would anyone have one to share. I'm trying to get my head around the concept first.
I may have to use Access 2000 and later versions to run the database, so do I have to cater for both DAO and ADO?
Thanks in advance for any help
rgs
Ginny
Hi,
I have some queries in MS-Access, that I run twice every day.
I store the query result in excel, with specific name and slot-code like HSC_25-Dec-04_S2.xls
( Exmple : High_Score_Customer_25-Dec-04_Slot-2) .
First few letters of the file name is static, but I want to select the Date, Slot_Code from the controls.
I have designed the form, 'CmdExport' is the command-button name, on clicking the button I need to export query result in MS-Excel.
I need help to proceed.
Can anyone guide me....................please help me...
Regards,
I am trying to export a query to an pre-existing spreadsheet. I am new to VBA and when I implement the following code nothing happened.
Option Compare Database
Public Function Sheet(strTQName As String, strSheetName As String)
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
[Code].....
Am trying to export an excel (97-03) file from a query I have. Some of the columns have exported as number values (as its linked via unique id's). I want it to export certain columns as the text columns. I have tried the lookup route but it doesn't seem to make a difference.
View 4 Replies View RelatedI wrote a SQL query that works fine. But when I call a custom function to export it to Excel, my query SQL will get deleted (not consistent). All that is left of the script is SELECT;
MS Access 2007, Windows 7.
Here's how I call the function:
Code:
Private Sub exportQryCustStatusAnalysisLite_Click()
Call ExcelSmsOutput("Query", "qry_custStatusAnalysisExport", "qry_custStatusAnalysisExport", Forms![frm_main].[filePath])
End Sub
Here's the custom function:
Code:
Public Function ExcelSmsOutput(objectType As Variant, prefixFileName As Variant, objectName As Variant, filePath As Variant)
Dim outputFileName As String
outputFileName = prefixFileName & " " & Format(Now(), "yyyy-mm-dd hhh mmm sss")
[Code] .....
What I am trying to do is EXPORT a query to an Excel Workbook which is read only and I need the data to land in a specific sheet in the excel workbook.
I have tried transferspreadsheet vbcode etc. but nothing works quite right and the fact the spreadsheet is Read Only does not work.
The Name of the Query is "Accrual"
The Name and location of the Excel Spreadsheet is "c:register.xls"
The Sheet inside the workbook is called "data".
I need the data to land in the "data" sheet over the top of the previous data and not create a new sheet or mess with the formulas that are linked to the "data" sheet.
Hello all,
I have always been wondering why this happens, and now I have a problem with it.
When I export a Table/Query from Access to Excel (be it with Right CLick -> Export...or TransferSpreadsheet) there is an apostrophe character (') appended to the front of some/each cell in Excel. You cannot see it immediately, but when you click on the cell, there is this character. WHY? Does anyone else have experience in this? :confused:
Thanks in advance.
All, using access 2010. I am using this code to make a folder upon exporting a query to excel:
Code:
sPath = "C:Directorymainfolder" & Format(Date, "yyyy mm dd") & "folder"
MkDir sPath
It works however I need a space between the date and the folder. But when I use "", it tells me path doesn't exist. get a space in the name when creating the folder.
I have got an existing query which is exported to excel through DoCmd.Transferspreadsheet. In simple terms the query looks like below:
Group | Client
Group1 Client1
Group1 Client2
Group1 Client3
Group2 Client4
Group2 Client5
Group2 Client6
etc.
As some of the groups contain large number of clients, I am trying to find the code to split groups by pre-specified number of clients and export to excel, as follows:
Group1 - clients 1 to 300.xlsx
Group1 - clients 301 to 600.xlsx
Group2 - clients 1 to 300.xlsx
etc.
I a trying to search some product from a search button and two combo boxes text values ,and on serch the vba code is :
Code:
Private Sub Command4_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varRecords As Variant
[code]...
I just dont know,the fuile is created on click of button but with headers only,dtaa is not coming but wheni debug ,in immediate window,data is oming but just not coming in excel file.
I know Access can setup an Outlook Task to auto-export query to Excel, but it requires the Outlook to be always open on the user's computer.
Is there an easy way to setup a schedule that can automatically export a query to Excel and this schedule will then auto-email the exported Excel file to an email address every Monday at 5AM for example?
If this can only be done in VBA, any reference I may start with?
I want some guidance in regards to how should I export access form as a report to excel. I already have fields with formulas in it and here I have fields where I have written down the numbers, so when these numbers get exported to excel. Excel will be populated with with these numbers and all the calculation will be automatically done.
I hope I am making sense here.
I have fields in access that should export through a button and populate on excel sheet.
is there any vb scripting for this, I rem seeing something in the same context few months back, but I can't seem to find it rite now.
Thanks in adv
Wondered if someone could help.
I am trying to Export an access report from an .mde (File>Export) and receive an error message "Overflow". the report itself is only 17 pages long.
Can anyone advice?
Thanks
Paul
Hi guys,
Im having a bit of trouble exporting some information to an excel file. ordinarily im sure the data would have been exported straight to an Excel Spreadsheet when the query is run.
However, it is exporting it as a generic file - that can then be opened with Excel (open with) and all the data is in there correct.
Im sure its something simple but i cant think what im missing.
Any help would be really appreciated !
Thanks
UdP
Good day to all:
I have created a form in access 2000 with last names, first names, and other information. I wish to export specific fields to excel by using a command button.
Any ideas on how to get started?
Many thanks in advance.
Regrds,
Dion
I'm trying to export some information out of Access into an excel spreadsheet in a paticular format. When I run the code it will give me the following error about every other time, Application-defined or object-defined error. I only happens on the one line of code and it will only happen every other time. If I click the "END" button then close the excel spreedsheet and re-run the code it will work. I am at a complete loss as to what is happening and any help would be greatly appreciated.
Dim rst As New ADODB.recordSet
Dim cnnLocal As ADODB.connection
Dim strSQL As String
Set cnnLocal = CurrentProject.connection
Dim objExcel As Object ' Excel application
Dim objBook As Object ' Excel workbook
Dim objSheet As Object ' Excel Worksheet
Dim i As Integer
Set objExcel = CreateObject("excel.application") 'Starts the Excel Session
Set objBook = objExcel.Workbooks.Open _
("CFile location")
Set objSheet = objBook.Worksheets.Item(1)
objExcel.Application.Visible = True
strSQL = "SQL Statement"
rst.Open strSQL, cnnLocal, adOpenKeyset, adLockPessimistic
i = 7
With rst
While Not .EOF
objExcel.Application.Cells(i, 1).Value = !QtyToBuild
objExcel.Application.Cells(i, 2).Value = !SegDescription
objExcel.Application.Cells(i, 7).Value = !DisplayTotal1 / !QtyToBuild
objExcel.Application.Cells(i, 8).Value = !DisplayTotal1
objExcel.Application.Cells(i, 9).Value = !InstallTotal1b
objExcel.Application.Cells(i, 10).Value = !other
objExcel.Application.Cells(i, 11).Value = "=SUM(H" & i & ":J" & i & ")"
.MoveNext
***This is the code that gives me the following error
***Application-defined or object-defined error
Range("B" & i & ":F" & i & "").Select
***
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
'.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
i = i + 1
Wend
.Close
End With
End Sub
Thank you for any help.
Hi,
I have a query embedded into a report... when i run the report it produces the data as it should and looks ok.... now i export that data to Excel and instead of displaying the data the query pulls off it just shows the query name instead.. if that makes sense ?
anyone know what i have done wrong ?
Access/Exel 2000
I've got a project that takes MS Access data and puts it into an Excel worksheet. I am interested in havig a dialog window open up that will allow me to specify the folder and Excel file name to save my data into. If anyone has a way of doing ths, I wold love to hear from them.
Incidentally, I do not want to use the TransferSpreadsheet function, since I am reporting data that would normally go into a report, and I am instead exporting my data into an Excel worksheet that looks like my report, with bolding, underlining, subtotals and totals.
Thanks in advance!
Hi there,
Once I have an Access report in the preview on the screen, I "export" it to Excel, using the built in Menu Symbol Option (analyse in Excel). It exports all the columns of the report perfectly, except for Dates. Here it doesn't just change the format - but shows ######## in the Excel column. When I click in the cell,it says "Negative Dates and Numbers are shown as ####". Any way I can stop this happening? I don't mind what format the date is in, in Excel, as long as it's readable!
Thanks a lot
Marion
Hi all, I use a d'base that exports a table via a query to Excel. Once the user hits the button to run this command it can take between 30 sec to 60 sec until the data is displayed in excel.
Does anyone know of anyway to speed this process up. Currently there are around 12,000 records with the table.
DoCmd.OutputTo acOutputQuery, "qryArchiveReport", acFormatXLS, "Archive.xls", True
thanks for looking:)