Export query to Excel


,

I have a button on a form and when I press it I would like this to happen:
Some code will be run that makes a copy of an templatefile (Excel), puts the values that comes from my query into the document and the saves it with a name that comes from two textboxes. I would also like to be able to put in some other text in the document, like two dates that I have on my form. Does anyone know how to do this \t
Posted On: Monday 22nd of October 2012 05:54:20 AM Total Views:  308
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Excel - export with color

I need to export a data from Access to Excel, but with assigned text color. Any ideas
VIEWS ON THIS POST

147

Posted on:

Wednesday 17th October 2012
View Replies!

How do you suppress exporting if there are no records in the table?

I am new to Access and am using Access 2003 to export tables via a macro using the Transfer Text Action and a Transfer Type of Export Delimited. I am not using a specification file. Currently, I get a resulting csv file whether or not there are records in the source table. I would like to perform the export only when records are present in the table. Can anyone describe a way to do this
VIEWS ON THIS POST

137

Posted on:

Wednesday 17th October 2012
View Replies!

Best way to export data to HTML pages

Access experts! I've worked with Access frequently in the past (Access 97) and now need to work on a project that I think Access 2007 will be able to tackle (at least I hope so!). I have imported two tables similar to the following: Car Info Table --------------------- Car ID (PK) Make Model Color Service Record Table --------------------------- Service ID (PK) Car ID (FK) Service Type (Oil change, tune-up, body work, etc.) Date Each car (record) in the car info table corresponds with multiple service records entries in the service table. What I need to do is format the data for export as HTML where each HTML page contains the car info at the top and the service record details listed below. Any ideas on how I should approach this I tried creating a test report linked only to the service record table and grouped by the service ID and sorted on service date. However, this is incomplete as I need to include the car details at the top and the export puts info for one car on multiple HTML pages. I need all info and records for a car on the same HTML page. Any suggestions are greatly appreciated.
VIEWS ON THIS POST

165

Posted on:

Wednesday 17th October 2012
View Replies!

access query - continued

I sent message before I finished explaining my problem! The query to get the totals includes queries with the employee, vehicle, material, and barricade costs all sorted by activity code but for it drops all activities that don't have costs in each column--if there weren't any costs entered for material or barricades for the month, they get dropped. How do I get everything even with some fields are null
VIEWS ON THIS POST

360

Posted on:

Monday 22nd October 2012
View Replies!

Update query help

I have a table with trailer numbers currently on the yard. I also have a prompt to enter trailers as they are leaving. I would like an update query to then remove the record from the OnYard table. Is this possible
VIEWS ON THIS POST

352

Posted on:

Monday 22nd October 2012
View Replies!

Lookup value from a table to input in a query

I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions Thank you! KellyJo
VIEWS ON THIS POST

351

Posted on:

Monday 22nd October 2012
View Replies!

Automatically update value in the subform from Crossstab query

, I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student. Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work. I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update. Please help! I would really appreciate if you could help me find a solution.
VIEWS ON THIS POST

411

Posted on:

Monday 22nd October 2012
View Replies!

Requery value of combo to listbox

, I have a form with a text box, a combo and a listbox. The textbox stores the name of the user. The combobox gets its data from a query. I would like that when I open my form, by default the list box will show all the records of a table named Master and then, I would use the combo to requery the list by selecting another user. records under that criteria, will have to show up in the list box.
VIEWS ON THIS POST

365

Posted on:

Monday 22nd October 2012
View Replies!

Concatenation of query text

Can anyone please give me an Access 2000 VB module that will concatenate [Portia - 1 query.text1] with [Portia - 1 query.text2] and type the result in textbox [text]
VIEWS ON THIS POST

301

Posted on:

Monday 22nd October 2012
View Replies!

Listbox selections requery subfrm

I have a form that has names with a subform with information. The contacts are in a list box and the subform (in datasheet view) shows the phone number/email/etc.. of the selected contact person. I am using the form as a quick look up of a persons information. Before changing it to a list box, it was previously a combo box, and everything worked fine. i was able to requery the subform and the cooresponding info for the person would come up. However, its now a list box and i am having problems with the code to make this happen. I have a different button sending the names selected from the list box to a report...and that works fine, but i am obvisouly missing something to make it work with the subfrm requery. Below is the code i got so far. Any help would greatly be appreciated. its driving me nuts! Private Sub QuickLookup_Click() Dim varItem As Variant Dim strWhere As String strWhere = "[memberID] = " For Each varItem In Me.MemberID.ItemsSelected strWhere = strWhere & Me.MemberID.ItemData(varItem) & " OR [memberID] = " Next varItem strWhere = Left(strWhere, Len(strWhere) - 17) DoCmd.Requery "subfrmqryindividual"
VIEWS ON THIS POST

338

Posted on:

Monday 22nd October 2012
View Replies!

Locking a table based on query results

I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field Something like, if field1 = "expired" then lock the table I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
VIEWS ON THIS POST

405

Posted on:

Monday 22nd October 2012
View Replies!

Help on query

i have this query Code: SELECT Group, ((Progress.DaysSpanned/(SELECT SUM(Progress.DaysSpanned) FROM Progress))*100)*((Progress.PercentCompleted*Weight)*100) AS WeightedProgress FROM Progress ORDER BY Group; it should reutrn the progress as a percent based on two weights (days spanned on task, and percent completed) the prob i have is if the percent is 100% complete it does not show in the progress, a value of less then 100% (due to the weights) how would i rewirte the query so that if percent completed is 100% it displays that, basically if the percent completed is already 100% it should ignore all other calculations and just return 100%
VIEWS ON THIS POST

437

Posted on:

Monday 22nd October 2012
View Replies!

Form using dynamic query results

hope this is a reasonable thing for semi-beginner to intermediate person to do. Our MS Access 2000 db shared on a server stores projects, categories of projects, clients, contractors doing the projects. Contractors are offered projects within their chosen categories based on their order on the list (last company who was offered a contract goes to bottom of list, like that). Currently we print out a phone list of the contractors (in desc. date order of the most recent offer accepted or refused), then we phone down the list until someone accepts. Later we enter all those offers in order on frmOffers to store the date & time stamp of that offer. Problem is, several contracts can be on offer on a given day, so when a company accepts/declines Project A, that company is still showing as high on the (paper) list for Project B. So when we have 3 people phoning they don't know they've offered multiple contracts to the same company this morning. Is it possible for a form "Offers" to be based on a query that keeps updating like that For example, you open frmProject to display details of a certain Project, cmd button to open frmOffers (continuous form, showing all offers so far with company, phone #, "accept"/"decline" & date/time stamp). Then, when you tab to the next record, can you have it show which company is next in line (even as the person beside you has updated their frmOffers in the meantime) Would you need to have a separate table storing only the most recent offer for each company If so, how would you get that updated all the time Anything you could offer to point me in the right direction would be very much appreciated!!
VIEWS ON THIS POST

325

Posted on:

Monday 22nd October 2012
View Replies!

Open query or form

access 2000 have a form with a list box some items in the list box after i click on it i want to open a query and the others i want to open a form help
VIEWS ON THIS POST

350

Posted on:

Monday 22nd October 2012
View Replies!

Append query to different table

Friends, I would like some help with a code that allows me to run an append query only if a specific field is not already stored into another table. Example. I add a new customer to my database using table1. I type his SSN. The code should check if this SSN is already present in another table (table2). If yes, so the code should skip the append query and just update any modified record included the SSN. If not, the code should run the append query. this should be done in the background while working in a form with its recors source to the table1. Can anyone help me
VIEWS ON THIS POST

338

Posted on:

Monday 22nd October 2012
View Replies!

export as csv file

: I have a query with Student ID numbers. These numbers are listed vertically. I wish to export these numbers as a csv file where there are commas after every Student ID number while keeping the vertical format. Any ideas Thanking in advance. Dion
VIEWS ON THIS POST

155

Posted on:

Monday 22nd October 2012
View Replies!

How to calculate cumulative values of this query fields?

Hi everybody. I got a access 2000 query that lists : 1)weekno 2)year 3)project (project number ) 4)QweekylyReportHeader (project description ) 5)customer (customer that requested this project) 6)department (department number and name that implements this project) 7)Projectleader ( project leader name and number that is responsible for this project) 8)Task (Task number that is done for this project ) 9)task description (description of task ) 10)employee ( employee number who is working in this project ) 11)name (Employee name and initial and last that works for this project ) 12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this 13)salary (amount of salary given to this employee) ===>i want cumulative for this I want to create another query that lists : A)cumulative value of hours worked on particular project task up that point. b)cumulative value for wages given for that project task up that point. http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample) The above query ONLY lists hours worked and wages gives for particular project task only during each week.But i want hours worked and wages give for particle project task up to that point in week. For example a project task might have implemented last week but not this so i want to take that in calculation as well. I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular project task. Notes: - There is a possibility that during a particular week no task been implement for particular project. - One employee can work in more then one project - One employee can have more then one salary (amount) for the same project because he might get raise in salary! - Only tasks carried this week will be printed in the weekly report http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database) http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population) http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view) query that display hourly wages of certain project during each week Code: SELECT querythisweek.weekno, querythisweek.Year, querythisweek.Project, QweeklyReportHeader.Customer, QweeklyReportHeader.Department, QweeklyReportHeader.description, QweeklyReportHeader.ProjectLeader, querythisweek.Task, dbo_Task.description, querythisweek.Employee, [lastname] & ' ' & [initials] & ' ' & [insertion] AS Name, querythisweek.hours, querythisweek.Salary FROM dbo_Task INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task; code for querythis week( calcualte the salary and hours worked) Code: SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours from dbo_Hourly_wages a where dbo_Hours_worked.Employee = a.Employee and dbo_Hours_worked.Project = a.Project and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno) from dbo_Hourly_wages b where b.Year < dbo_Hours_worked.Year or (b.Year = dbo_Hours_worked.Year and b.weekno
VIEWS ON THIS POST

282

Posted on:

Monday 22nd October 2012
View Replies!

hardcoded parameters in querys--can I find all?

We have a tbldepartments. At this point (has been a few years)...many dept. have merged or changed names. We want to 'change' the names of some and delete or add others. Ok on the delete/add. However, in many of our queries, we have the dept. name/number hardcoded in. And trust me, there are tons and tons of them...this is a big database. Short of going through each query, is there a way to 'search/replace' either in each query or in all To update these easily suggestions
VIEWS ON THIS POST

288

Posted on:

Monday 22nd October 2012
View Replies!

exporting to excel

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.
VIEWS ON THIS POST

115

Posted on:

Monday 22nd October 2012
View Replies!

export single field to text file

Anyone have any idea how i would be able to export a single cells content (from a query) into a txt document without headers or extras What I'm essentially trying to do is view the cells content (which is in html) in a web-browser.. Any Ideas Suggestions
VIEWS ON THIS POST

133

Posted on:

Monday 22nd October 2012
View Replies!