Hi
If I open a query in Access and uses the "Export to Excel" button everything works out just fine.
But when I want to make the export with a button using the code:
DoCmd.OutputTo acOutputQuery, "qryToExcel", acFormatXLS
I only get 255 chars from the PM-fields to excel.
Can anyone help me solve this problem?
I also want to make some filtering, with code string, to the query before I export it, could use some suggestions there also.
Thanks in advance
I am having a problem getting Access to output to an excel file.
I want to take a form that I have that has 20 or so records with 6 or 7 fields for each and put it into an excel file that is formated the way I need everythign to look.
I also need it to make the file name such as CCCAAAMMDDYYYY (3 letter company abbreviation,3 letter initials and then todays date) I am thinking that the company abbreviation will be pulled from a query, then initials will be a form that opens, and the date can be pulled from access' date function.
I tried using vb code and doing an output to comand but that did not work.
I am confused someone please help if possible.
I have written some code that will output to a spreadsheet in a given location:
how can I rework this code so that the excel output displays on screen rather than saving to a specified location:
Code Written:
Private Sub outputToExcel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C4C Period Final", "\uk.michaelpage.localdfsGroupDataNSCHElite Database ReportsGross Cash For Car.xls"
MsgBox "C4C Period Final Exported"
End Sub
Windows XP Pro/Office 2000
I was able to output to excel before fine, now all of a sudden I cannot. I keep getting a message when I go Tools>Office Links>Analyze with MS Excel:
"Cannot access "xxxvac_Inv_DBQuery.xls"
It's wierd. It won't output the data, but it still creates the spreadsheet file.
Any Idea's?
Thanks to all in advance, your help is greatly appreciated! :(
Hello first of all I have learned a lot from this forum so thanks you all who post questions & solutions. Ok I have a table which I use to query info and export to excel. My question is there a way to add sum totals to a few of the columns once it is in excel. I was thinking it would be easier to do a sum statement then just add it as the bottom row while it was exporting. Or would it be easier to do it once it is in excel. Here is my code:
Private Sub Command63_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.CUSTOMER_TYPE, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.E_MAIL, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.REP_NUMBER, tblCONSOLIDATED.PROMOCODE, tblCONSOLIDATED.SALESCODE, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
strWhere = "WHERE"
strOrder = "ORDER BY CURRENT_YTD DESC"
If Not IsNull(Me.txtCSONME) Then
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND"
End If
If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If
If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If
If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If
If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If
If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND"
End If
If Not IsNull(Me.txtCSOM1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PROMOCODE) Like '*" & Me.txtCSOM1 & "*' AND"
End If
If Not IsNull(Me.txtSLCYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN " & Me.txtSLCYYD1 & " And " & Me.txtSLCYYD2 & " AND"
End If
If Not IsNull(Me.txtSLLYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) BETWEEN " & Me.txtSLLYYD1 & " And " & Me.txtSLLYYD2 & " AND"
End If
If Not IsNull(Me.txtSLPYR11) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_TOTAL) BETWEEN " & Me.txtSLPYR11 & " And " & Me.txtSLPYR12 & " AND"
End If
If Not IsNull(Me.txtSLPYR21) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR2_TOTAL) BETWEEN " & Me.txtSLPYR21 & " And " & Me.txtSLPYR22 & " AND"
End If
If Not IsNull(Me.txtSLPYR31) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR3_TOTAL) BETWEEN " & Me.txtSLPYR31 & " And " & Me.txtSLPYR32 & " AND"
End If
If Not IsNull(Me.txtSLPYR41) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR4_TOTAL) BETWEEN " & Me.txtSLPYR41 & " And " & Me.txtSLPYR42 & " AND"
End If
If (Me.PROSPECTBX) = True Then
strWhere = strWhere & " (tblCONSOLIDATED.CUSTOMER_TYPE) Like 'P' AND"
End If
If Not IsNull(Me.txtSLCLS) Then
strWhere = strWhere & " (tblCONSOLIDATED.SALESCODE) Like '*" & Me.txtSLCLS & "*' AND"
End If
If strWhere = "WHERE" Then
strWhere = ""
Else
strWhere = Trim(Left(strWhere, Len(strWhere) - Len("AND")))
End If
Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
DoCmd.OutputTo acQuery, "qrysalesdata", "MicrosoftExcel(*.xls)", "QUERY RESULTS.xls", True, ""
End Sub
Sorry for adding the whole thing but I'm not sure it should be done after "Query Results.xls" is written or during the select statement.
Thanks in advance for your help.
Bryan
Hi Guys,
I have 2 tables
tblXRFResults
ResultID
SampleName
Date
SampleDate
a few others not necessary in this
and tblXRFResultsConcentration
ResultID (many records linked to 1 record in tblXRFResults)
Concentration
CompoundName
What I need to set up is a query that will allow me to generate a report that will give an excel like format, with the column headings being the SampleName, followed by the CompoundName(s) from the other table and the "rows" will be corresponding sample name and concentrations.
I am sure this is pretty easy, but I am stumped!
Any help would be greatly appreciated.
hi
Would be glad if somebody could help me on this issue that been nagging a set a databases that we use.
The Access databases that we use are around 200 MB in size..
Its multiuser say maybe around 40-60 users.The database is split to enhance performance.
We use DoCmd.OutputTo ...statement to output the query into Excel sheet.
Example :
DoCmd.OutputTo acOutputQuery, "qryAllPrems", acFormatXLS, , False
The problem that we are facing is that the after the execution of the statment the query ( qryAllPrems) gets wiped out....i.e the entire sql in the query gets wiped out clean ..
It's driving us crazy as to what could be the cause of the problem...and I would appreciate it if somebody could help us..in this regard.
I have a query that contains Invoices(usually 2 line items per invoice) Taxable and NonTaxable. I need to able to basically export it to excel on multiple worksheets when the Amount of unique invoice number reaches 15.
From all my scouring on the internet I can't find anything with this criteria.
I guess you've all heard this one before, but I'm relatively new to Access, and was wondering if there was a way to export data from a form to an already existing Excel spreadsheet via a form button. Is this even possible in MS Office 2007, or even at all?
View 2 Replies View RelatedBeen hunting a round looking for some code that will output my access report to excel but more inportantly add a unique field from the report to the name of the file. example would be [FileName] & [ReferenceNo].xls.
View 2 Replies View RelatedI have another request for you. I did all the analysis. My final query looks as follows
Jan_N-1, Feb_N-1, Mar_N-1, Jan_N-2, Feb_N-2, Mar_N-2
0.98......... 0.90..... 0.88....... 0.95....... 0.88...... 0.70
but I need Access to create a excel table like output. which looks as like
.......Jan. Feb. Mar
N-1 0.98 0.90 0.88
N-2 0.95 0.88 0.70
I'm working with Access 2010 and am trying to use the transferspreadsheet command to output data in a query to an Excel 2010 format file. Here is the line of code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "q_calldetails_tmp", "c: emp estoutput.xlsx"
It works fine and produces the output file but when I try and open it with Excel I get an error saying the format is incorrect. If I change the extension to .xls it opens with no problem but I need it to be an Excel 2010 format with correct extension.
I'm using Access 2007 SP3.
Whenever I export reports to PDF, the output appears zoomed and clipped. No extra pages are generated as they would be if I'm going over margins, it's just the report is clipped.
The report looks perfect in preview mode, and it looks perfect when going to an actual printer. However, when using OutputTo to save it as a PDF, this is when the report content is clipped.
Here is the code I'm using:
DoCmd.OpenReport MyReport, acViewPreview
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "MyReport.pdf"
DoCmd.Close acReport, MyReport
I open the report in preview mode first so events are fired that show/hide various objects based on fields in the recordset.
I've tried reinstalling, and I've tried this on two different machines, one running Windows 7 and one running Windows Server 2008...both with the same results.
I have a sql string that is used to populate a form and now I'd like to export that set of records to a spreadsheet but I can't get 'OutputTo' to work. It wants a predefined query name - !? Any suggestions?
View 5 Replies View Relatedguys the below works a treat on a single qry -(pinched from samples) however I will have 20-30 qry that I want in this 1 workbook all on seperate tabs .
Now I have also tried getting this done cross refer to "qryto excel automation" and have had some particual succes - but does any one know how to do this
DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLS, "Tbl1XLS.xls", True
background
I have multiple currencies and multiple taxes on these currrencies
so Sterling
may have 5% tax , 17.5% and Tax n/A (and others) Dollars same, Euros same
so I need each currency and each tax in a seperate sheet int he same workbook
i am happy to write 1 qry for each currecny/tax
inthe exampel above Sterling 5%
sterling 17.5% , sterling Tax/Na - would be 3 qry
so I will end up with about 20-30 qry depending on how many tax's and currency I end up with
unless soemone can come up with a better solution :eek:
I've created code to export data from a query to a spreadsheet and it works fien. My question is, is it possible to set up an SQL statement in code and export the results of the SQL to a spreadsheet instead of using the saved query?
View 1 Replies View RelatedHi,
Not sure if this is right forum, but here goes.
I am looking for advice on sending data to some electronic equipment via a data cable using a port on the computer.
The access form is complete and just needs to be able to output the data to the port.
The electronics are waiting for data in the following format:
8 Bits - Address (1-41)
followed by:
8 Bits - Data (0-9)
What route would you advise I take for accomplishing this task.
Can it be done direct from VBA or do I need to enrol a third party DLL or VB program?
Any advice is welcome.
Regards
Daz....
I have a table with a field for gender and iit is populated with "m" for male and "f" for female.
I have created a report and would like it to display the word male or female for the m or f.
Is there a way I can do this in stead of doing a find and replace on over 10.000 records.
Thank You
Input/Output
I’m reading “Selecting the Data” from Running Microsoft Access 2000, pp. 87-94. The confusing part is the input/output explanation. When I think of input I think of typing, keying, or entering raw data into the computer. Output to me is information displayed or printed.
Okay, here is what was printed:
“A data item is an input for a task if you need to read it from the database (but not update it) to perform the task. For example, a customer name and address are some of the inputs needed to create an order. Likewise, data is an output for a task if it is new data that you enter as you perform the task or that the task calculates and stores based on the input data. For example, the quantities of items you enter for an order are outputs; the shipping address and phone number you provide for a new order are outputs as well.”
The above is confusing me. Can anyone explain it better so this dummy can get it? Thanks!
Hi,
I have a select query that selects certain customer email addresses and
I want to output the results as csv, which can be used directly in a "To:" field to create a group email.
The SQL of my query is:
SELECT [Customer Details].[Email Address]
FROM [Customer Details] INNER JOIN [Orders] ON [Customer Details].[Customer ID] = [Orders].[Customer ID]
WHERE ((([Orders].[Licence Declaration])=No) AND (([Orders].[Licence Status])="Unlicensed"));
I have found some SQL code on the internet as follows:
"SELECT Field1, field2 FROM table WHERE field1='" & [FORM]![PARAMETER] & "';"
but adapting my code with that is a little bit beyond me...can anyone help?!
Cheers,
Dave
Hi,
I have a query which pulls key fields from a Table and exports to an excel file through the Output to cmmd.
In the table, i have defined a few fields with Look-up criteria and Combo / LIsts. When the Excel sheet is created the Lists are not downloaded. The purpose is to scrub the data and provide users an excel sheet for update of the Combo box for upload in another MS access table.
Please help, this feature is extremely critical for our work. Thanks in advance.
Hi guys,
Attached is a real simple database. What I want to be able to do is insert new payment details, and I have a couple of issues.
I have two clients and need to be able to select which client I want, select the payment details from the drop down list and enter in some appropriate amount.
Its been a while since I've created a db from scratch so this is obviously where I am having my difficulties. However I think I need to use a sub form.
How can I input the details as above, and how can I always get the combobox to default to the first entry in the list rather than defaulting to zero?
Thanks for the pointers.
I've got a huge problem. Lots of questions I need answers to, but I'll start with this. I have a client I was doing some web design and basic Access work for, and now they want this sorta-complicated database and I'm getting lost.
It's a big customer complaint database for a big manufacturing company, a local branch. I have all this data in one table because that's what they want.
One of the items is whether or not the company has a debit with a particular complaint, and, if so, how much it is.
The thing is, this debit number (currency) might change (update), but they want a YEARLY and MONTHLY report on "how much money we've lost, altogether."
Where do I start with that?
Big thanks to anyone who can lead me in the right direction. I have some experience with Access, but I'm by no means an expert or programmer.
Hi Everyone
Does anyone know how to disable the vdu output to the screen when loading multiple forms etc.
I have an annoying flicker when my main form is reloading.
Thanks in advance for any help,
Kindest regards
Tony
I have a control button on a form that outputs the current record to disk using an on click event. The user currently has to select the format (rtf) the path and the filename and I would like to be able to automate this process.
The format should be rtf, the path will always be the same and I would like the file name to comprise the unique record number (4 digits) followed by the current date string (format ddmmyy).
I think is just a matter of setting some parameters after the DoCmd code event that saves the report to file but I have had a go but can't get the syntax right.
Any ideas how to automate this process to eliminate the risk of user errors?
Thanks
Andy
I have a report and wish to convert the output of this report into a .csv file.
Can anyone help?
Thanks
Paul