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"
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.
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.
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()
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
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!
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?
Been 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.
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:
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.
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.
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'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.
outputFileName is a variable I've created for file path / name etc...When I run this command, I get a popup box asking what format I'd like to export / save the form as. I'd like to disable this and just have it save as a PDF.
:confused: I've created a form within our company database which will track hardware/software requests. I'd like to create a macro, or add code to output the data entered into a notepad file. Nothing extraordinary. I'd like for it to be like this:
Ticket: XXXXX Employee: xxxxx Reason for Request: xxxxx Quantity:xxxxx Part Number:xxxxx Price:xxxxx Shipping:xxxxx Total:xxxxx
Those are the headings of the fields and x's denote entered data. I'd like to keep the headers and have the entered data as well. Is it possible?
I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments ???
Using 2010.Would like to open a new form as a popup to display the results of a query. Trying to avoid eliminating objects in my DB, so not wanting to create Query Objects, but just creating the SQL on the fly.
The purpose of opening the popup form is to display the results of the Query based on a Button selection that the user chooses. Not sure if I can pass the Query from Form to Form, or if the called Form should run the Query. The Query output is a single record which is a Memo field, which is why I want to open the new form to display the result. The Memo text can be a page long or more.
I am not getting any errors and can open the form. The problem is that I am not getting the Query results displayed on the new form. The code below is from my first form that has the buttons. The popup form is Results_frm.
Code: Private Sub InScope_bt_Click() Set dbs = CurrentDb strSQL = "SELECT In_Scope FROM Project_Scope_Deliverables" Debug.Print strSQL ResultType = "I" DoCmd.OpenForm "Results_frm" Forms!Results_frm.RecordSource = strSQL End Sub
Hi Have scoured the forum and have found people with a similar problem, but I just can't seem to get my head round this.
Basically I have a table with various fields, one of these fields is a sign off field where the user enters their name once the record has been reviewed.
I want to count all the records that do not have a name entered in this particular field thus are null/blank. I then want this figure to appear on a form thus representing the total number of records still to be reviewed.
I have tried doing this with various methods with no joy.
I've been working on creating a database for a Physical Therapist that will store patients information and information on past appointments (including the amount of money paid per appointment). I created a form that he can then easily select the patient and their D.O.B. and insurance carrier will show up.
But I need to create a way that when he then selects the treatment received for the appointment that the correct payment amount will show up and be recorded as well. The reason this is an issue is because for each insurance there is a different cost per treatment.
I have created a form with 9 cascading combo boxes. There are 59 total results or scenarios (which are based upon the completion of the form and these 9 combo boxes). Currently, upon completion of the form and the 9 combo boxes, a text box displays the singular result/scenario.
Here's my question: Rather than utilizing a text box to display the result/scenario, is it possible to output a PDF or Word document? If so, is there a general VBA code for this that I could manipulate?
I searched the archive and didn't find quite what I was looking for, so..
I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.
I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.
Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!
A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.
I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.
Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject
Including check boxes (representing the query fields) in an interactive access form, in order to decide which fields should be visible.
I think the solution is building an invent in VBA for each check box, however I'm not an expert in Access VBA and don't know how to write the code.
In the example that I've uploaded, in the Form1, when I run the query, all fields are displayed, i.e. VENDOR, REGION, CUSTOMER and MATERIAL are displayed. How can I manage it in the form with a listbox to display only the REGION or MATERIAL for instance.
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code: SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay FROM [Tble-wcDelays] GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
What is the best way to accomplish this? I have a database that split into the Front end and the Back end. The form that the users entering the information uses the queries from two sources. One is the a table within Access and the other is an excel spreadsheet that I link to it. How can I update /replace the Excel spreadsheet while users are using the form of the front End?
I have the form (PROJECT DETAILS) that displays a specific record's information, one record at a time.I would like to click a button to export 5 specific fields (name, phone, address) of that form ("Project Details"), based on the PK "AssignNr", and then insert those fields in an already created Excel form that has those fields blank, but other fields filled in.
And then, as part of the same macro, I want to have the Print pop-up come up in the Excel form to select where/how that file will get printed.I've tried exporting from the Project Details form, but it exports all the records. And then I am not sure how / where to code the vlookup formula (if that's even necessary) to take the data from the exported file, into the existing Excel form that needs to be filled in.
Can this even be done in one macro, or do I need to create two - one to export data from Access to Excel. And then another in Excel to vlookup the details from the exported file, into the existing Excel file.
It would be nice if I didn't have to put in what the vlookup criteria is. (i.e. AssignNr (which is the PK)) - if it could just be taken from the PROJECT DETAILS form's current record selection.
