How to Create a Macro to Run an Access Query Paste the Result Into Excel

1. In Access, create a table of sample data: enter the following data in a new table:the accidental tourist,12/1/2009,$6.01
the accidental tourist,12/3/2009,$7.98
iron john,12/5/2009,$4.98
iron john,12/6/2009,$5.98
2. Double-click the column headers (e.g. 'Field1') and replace each with these headers, in this order:book,datesold,netsaleSave the table ('control-s') with the name 'books.'
3. Create a query from the table, and press the 'Esc' key in the 'show table' dialog box. Right-click on the query's tab and select 'SQL view.' Enter the following in the code window:SELECT books.* INTO queryresults
FROM books
WHERE (((books.book) Like '*acc*'));Save the query ('control-s') and name it 'vbaquery.'
4. Open Excel and press the toolbar's 'Data>From Access' icon. Select the 'queryresults' table in the 'Select Table' dialog box. Click 'OK' on the 'Import Data' dialog box and notice the query's results: only the 'iron john' books are shown. Save the Excel file with any name, and close it.
5. Reopen the 'books' database in Access. Open the 'vbaquery' and revise its 'Criteria:' field to read 'Like '*acc*'' (Don't type the double quotes. Do type the inner, single quotes.) Resave the query.
6. Create a new query. Type the following SQL statement in the 'SQL view' window, then save the query as 'dropqueryresults':DROP TABLE queryresults;
7. Enter the Visual Basic integrated development environment (IDE) by pressing 'alt-f11,' then select 'Insert>Module.' Paste the following code into the new module's blank code window:Public Sub runquery()
'delete the results table first
On Error GoTo DO_QUERY
RunQueryForExcel ('dropqueryresults')DO_QUERY:
RunQueryForExcel ('vbaquery')
End SubPublic Sub RunQueryForExcel(qName As String)
DoCmd.SetWarnings False
CurrentDb.Execute qName
DoCmd.SetWarnings True
End Sub
8. Position the cursor anywhere in the 'runquery' subroutine and press 'F5' to run the query. Reopen the Excel workbook you previously opened and notice the updated data: your macro has replaced the 'iron john' rows with the 'accidental tourist' rows. (Access 2007 or later users can stop here.)
9. (For users of Access 2003 and earlier). Use step 7 to paste the following code into a new module in the Visual Basic IDE:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub pasteToExcel()Const qName = 'vbaquery'
Dim db As DAO.Database
Dim recset As DAO.Recordset
Dim s As String
Dim appXL As Excel.Application
Dim ro, co'''''''''''''''''''''''''
Set appXL = CreateObject('Excel.Application')
appXL.Workbooks.AddSet db = CurrentDb
Set recset = db.OpenRecordset(qName)
s = 'book' ', ' 'dateddsold' ', ' 'netsale' vbCr
appXL.ActiveSheet.Cells(1, 1) = s
ro = 2
co = 1
s = ''
Do While Not recset.EOF
s = s recset![book] ', ' recset![datesold] ', ' recset![netsale] vbCr
appXL.ActiveSheet.Cells(ro, co) = s
recset.MoveNext
ro = ro 1
s = ''
Loop
recset.Close
db.Close
appXL.ActiveWorkbook.SaveAs ('c:\dataFromAccess.xls')
appXL.QuitEnd Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Select 'Tools>References' and check the 'Microsoft Excel Objects Library.'
10. Return to Access and do steps 1 to 3. However, for step 3, paste this SQL code into the SQL code window:SELECT books.*
FROM books
WHERE (((books.book) Like '*acc*'));
11. Return to the Visual Basic IDE. Place the cursor inside the 'pasteToExcel' function and press 'F5' to run the function. Open the Excel file 'c:\dataFromAccess.xls' to view the results.

Read more ►

How to Create a Newsletter in Microsoft Office

1. Open Microsoft Word, and select a newsletter template by clicking the drop-down menu File > New Works Template > Tasks. Additional templates may be downloaded from the Microsoft Office Online website.
2. Create a nameplate for your newsletter. Using Word's 'header' is the simplest way to do this. Select View > Header and Footer from the drop-down menu to edit text boxes within the header and footer. Center your newsletter's title and add clip art, if desired, selecting Insert > Picture > Clip Art from the drop-down menu. If the software is available, you may use Copy and Paste to import clip art from Publisher.
3. Add content to your newsletter. Write or solicit articles from family members or co-workers, create a list of upcoming events or feature one-line quotes from members of the team.
4. Create a calendar to import using Excel. Select the Tools drop-down menu, and select CalendarMaker. The calendar may be copied and pasted into your Word newsletter.
5. If the software is available, create a mailing list using InfoPath.
6. Print enough copies for distribution. The easiest and most cost-efficient format for your newsletter is one page, front and back, using black ink.

Read more ►

How to Use VLookup in Excel 2003

1. Open the Excel 2003 file that contains the data table that you want to work with.
2. Select the top-left cell of the data table and hold down the mouse button. Move the mouse to the bottom-right cell and select it. Click the 'Data' menu at the top of the screen and select 'Sort.' Select 'Ascending' to sort the information from smallest to largest. The first column has to be sequential in order for VLookup to work.
3. Select any cell outside of the data table. Press the '=' key to start your formula. Type 'vlookup('. The open parenthesis is required to begin entering arguments.
4. Type in the value that you want to search for, or a reference to a particular cell in the first column. If you are searching for a text string, wrap the text in double quotes. You may include '?' and '*' as wildcards. '?' will replace any one value, while '*' will replace any string of values. If you need to search for a question mark or an asterisk specifically, precede the character with a tilde. Remember that VLookup will only search in the first column of your data table. Press the comma key when you are done.
5. Click on the top-left cell of the table and hold down the mouse button. Drag your mouse to the bottom-right cell of the table and release the button. You will see those two cells appear in your formula bar. Press the comma key.
6. Press the number key that corresponds to the column where your desired result resides. Columns in the table are numbered starting from the left and counting up by one for each column. If you enter '3' here, VLookup will find your value in the first column, then give you whatever is in that same row in the third column. While you may enter '1' here, VLookup will return the value that you searched for. Press the comma key.
7. Enter the word 'false' if you want VLookup to find an exact match for your search value in the first column. If you only want to find an approximate match, erase the last comma that you entered, as this command is optional and searching for an approximate match is the default setting. If you search for an approximate match, VLookup will use the next largest value if it cannot find an exact match. Type in a close parenthesis and press the Enter key to complete the process.

Read more ►

How to Remove Checkboxes in Excel 2007

1. Open Microsoft Excel 2007.
2. Click the 'File' tab, click 'Open' and browse to the spreadsheet with the check boxes to delete. Double-click the name of the file to open it.
3. Click once on the first check box to remove. Press the 'Delete' key to remove it. Scroll throughout the spreadsheet or page tabs at the bottom of the work area and continue clicking and pressing the 'Delete' key to remove the check boxes.

Read more ►

How to Make a Pivot Table in Excel 2007

1. Open the Microsoft Excel spreadsheet program. Click 'Start' on the main operating system menu. Then choose 'Programs' and from the program menu select 'Microsoft Excel.'
2. Choose a cell in a Microsoft Excel spreadsheet. Make certain the call range has a column heading. From the main tab menu, select the 'Insert' tab, then in the table menu click on 'Pivot Table.'
3. Select the data range to be analyzed. A user can either select a range of data in the current spreadsheet or choose data from an external source. Also, determine if the pivot will appear in the current worksheet or in a new worksheet.
4. Create the pivot table in Microsoft Excel. With the correct data source selected and each of the columns has a heading, click 'OK.' The pivot table will appear either on a new or existing worksheet. Now check the data columns to display in the pivot table.
5. Make sure the data in the pivot table displays correctly. Sometimes the pivot table will have formatting issue if the original table had labeling or data problems. Check the drag field boxes if there are errors in appearance or formatting. Experiment with the pivot table data by dragging the data into different fields until the pivot table display is correct.

Read more ►

How to extend the Microsoft Office 2010 trial

1. Install the Office 2010 trial of your choice. Wait until the end of the initial 30 days to complete the rest of the steps. Office will have a pop-up reminder when the trial is about to expire.
2. Run the command prompt as an administrator. To do this in Windows 7, go to the Windows orb logo (formerly the 'Start' menu). In the search field, type 'cmd' and then press the 'Ctrl' 'Shift' 'Enter' keys together. Click 'Yes' if prompted by the User Account Control.
3. Enter 'C:\Program Files\Common Files\Microsoft shared\OfficeSoftwareProtectionPlatform\ OSPPREARM.exe' into the command prompt window. To paste the address into the command prompt right-click and select 'Paste' in the command prompt window. Press 'Enter.' If your main hard drive is not 'C,' or if you have a different operating system, OSPPREARM.exe could be located somewhere else. In Windows 7, you can search your hard drive for the file by opening your main hard drive folder and typing 'OSPPREARM' in the search box in the upper right-hand corner. Once you have located OSPPREARM.exe, copy the location into the command prompt and press 'Enter.'
4. Close the command prompt if the message 'Microsoft Office rearm successful.' is displayed. Your trial period was extended 30 days. If you do not see the message, try repeating the steps above. When you have reached the maximum rearms, the command prompt will produce an error message.

Read more ►

How to Delete Duplicate Rows in Excel Based on One Column

1. Select the entire column of data by left clicking on the top cell and dragging the cursor to the bottom cell.
2. Click on the 'Data' tab and select 'Filter > Advanced.' Click the 'Filter the list, in-place' radio button and check the box next to 'Unique records only.' Click the 'OK' button.
3. Open the Office Clipboard by pressing the 'Home' tab and choosing 'Clipboard.'
4. Hold down the Crtl and C keys at the same time to copy the cells into the clipboard.
5. Click on the 'Filter' tab to restore the original cell data.
6. Paste the modified cell contents from the clipboard into the cells by clicking on the contents in the clipboard.

Read more ►

How to Make a Gantt Chart Using Microsoft

1.
Open a new Excel worksheet. Enter column headings into Row 1, such as 'Start Date,' 'Amount Completed' and 'Amount Remaining.' Enter your data into the columns for each heading.
2.
Select the cells that contain data. If you are using Excel 2003 or earlier, click the 'Chart Wizard' button on the 'Standard' toolbar. In Excel 2007, go to the 'Chart' section on the 'Insert' tab. Select 'Bar' as the chart type, and then select 'Stacked Bar' as the subtype. Click 'Finish.'
3.
Double click the first color in the chart bars to open the 'Format Data Series' dialog box. Go to the 'Patterns' tab. Select 'None' for both 'Area' and 'Border,' and then click 'OK.'
4.
Double click the 'Category Axis,' go to the 'Scale' tab and select 'Categories in Reverse Order.' Go to the 'Font' tab and change the font size to '8.' Click 'OK.'
5.
Double click the 'Value Axis' and go to the 'Alignment' tab. Enter '45' in the 'Degrees' box. Go to the 'Font' tab and select 'Bold' under 'Font Style' and '8' for the font size. Click 'OK.'

Read more ►

How to Change Columns From Numbers to Letters in Excel 2007

1. Open Microsoft Excel 2007 to open a blank worksheet. Locate the 'Office' button in the top left hand corner next to the exit button. Click on the 'Office' button.
2. Click on 'Excel Options.' A new menu window will appear in which you need to click on the 'Formulas' tab.
3. Deselect he 'R1C1 Reference Style' check box. Click 'OK'to close the window and save your selections. This will change the columns from numbered to lettered immediately. To change the R1C1 reference style again simply revisit the excel options again and check the R1C1 box.

Read more ►

How to Reassign a Macro Button in Excel 2003

1. Open Excel 2003 and locate the macro button on your toolbar. Click 'Tools' on the menu bar and click 'Customize.' The Customize dialog box will appear. Click the 'Commands' tab. Click the macro on the toolbar and click the 'Modify Selection' button in the Customize dialog box.
2. Select 'Assign Macro.' The Assign Macro dialog box appears. Select a macro from the list and click 'OK.' If necessary, you can update the macro image by clicking the 'Modify' button in the Customize dialog box and select 'Change Button Image.'
3. Update the macro description by clicking the 'Modify' button and selecting 'Name.' Type a new name over the previous macro description. Click 'Close.' View the newly assigned macro on your toolbar.

Read more ►

How to Make Box Plots in Excel

1. Open the Excel file that contains the data you want to represent as a box plot.
2. Scroll to the bottom of the data set and type in five new row headers on the left-hand side of the screen. These headers, from top to bottom, are: “First Quartile,” “Minimum,” “Median,” “Maximum” and “Third Quartile.”
3. Select the cell to the right of “First Quartile.” Type in “=Percentile (XXX, 0.25),” where XXX is the field of data for that sample. Write out the field by typing in the column and row of the first cell in the field, then add a colon, and then type in the column and row of the last cell in the field.
4. Type in “=min (XXX)” next to the “Minimum” cell. Then type in “=median (XXX)” next to the median cell and “=max (XXX)” next to the maximum cell. Finally type in “=percentile (XXX, 0.75)” next to the “Third Quartile” cell. The “XXX” in all of these will be the same data field.
5. Copy and paste these formulas to the cells to their right, if you need to create a box plot for more than one data sample. If your second sample is located in the cells directly to the right of your first sample, Excel will automatically change the formulas to reflect this.
6. Select all of the cells in the chart you just made, including the row headers in the first column. Click “Insert” on the top of the window, then click the “Line” button in the “Charts” area. Choose the “Line with Markers” button, and your chart will appear.
7. Click the “Switch Row/Column” button at the top of the screen.
8. Right-click on any of the data points on the chart. Select “Format Data Series” from the menu that pops up. Then choose “Line color” on the left side of the new window, and click the radial button next to “no line” before clicking “Close.” Repeat this for the other four data lines.
9. Click the Layout tab at the top of the screen, then click the “Analysis” button. Choose “Lines” from the menu and finally click on “High-Low Lines.” Then click “Analysis” and “Lines” again, but this time select “Up/Down Bars” to reveal a menu, and select the “Up/Down Bars” button from this new menu.

Read more ►

How to Insert a Countdown Timer in an Excel Worksheet

1. Open the Excel 2010 worksheet where you want to add a countdown timer. Right-click the cell where you want the timer to go and choose 'Format Cells.' Click 'Custom' on the left side of the window that appears and then select 'h:mm:ss' from the list on the right. Click 'OK' to continue.
2. Hold down 'Alt' and press 'F11' to open up the VBA console. Select your current worksheet from the list on the left side of the console. Click the small arrow next to the 'Insert Userform' button at the top of the screen, which is the second button from the left side, and choose 'Module' from the list of options that appear. Double-click 'Module 1' when it appears in the list.
3. Click the white space on the right side of the screen and enter the following code:Sub Countup()Dim CountDown As DateCountDown = Now TimeValue('00:00:01')Application.OnTime CountDown, 'Realcount'End SubThis code will allow the rest of the code to process once each second.
4. Press enter to access a new line on the page. Enter to following code:Sub Realcount()Dim count As RangeSet count = [E1]count.Value = count.Value - TimeSerial(0, 0, 1)If count
5. Click the 'X' in the upper right corner of the console to close it.
6. Select the cell that you are using for your countdown and enter your desired countdown time. Be sure that you enter the hours, minutes and seconds, even if the hours and minutes are zero. '2:30' is two hours and thirty minutes; '0:2:30' is two minutes and thirty seconds.
7. Click the 'Developer' tab at the top of the screen, and then click the 'Macros' button on the left side of the ribbon. Select 'Countup' from the list and click 'Run.' Your countdown will start and it will continue until it gets to zero, when you will see a message box explaining that the countdown is complete.

Read more ►

How to Create a Standard Deviation Graph in Excel

1. Open Excel. Open a data file or type the data into a series of columns with one value per column.
2. Click 'Insert' and select 'Scatter.'
3. Select the type of scatter graph you wish to insert.
4. A blank area should be inserted and you should see new tabs as options on the menu bar. Click the 'Design' tab and choose 'Select Data.' Hold 'Shift' while selecting the data and click 'OK' in the pop-up window.
5. Click on the 'Layout' tab and select 'Error Bars.' Select 'Error Bars with Standard Deviation' from the drop-down window.

Read more ►

How to Calculate Mean in Excel 2007

1. Enter your the data in column A. For example, if you have four numbers to enter, you would enter them in cells A1 through A4.
2. Determine the range for your data. For example, if you entered four data points, your range would be A1:A4.
3. Enter the formula '=AVERAGE(Range)' into cell B1 to have Excel automatically calculate the average of your data. In this example, since your range equals A1:A4, you would enter '=AVERAGE (A1:A4)' into cell B1 and the average will appear.

Read more ►

How to Embed a PDF File in Microsoft Excel 2003

1. Open the Excel document you're editing if it's not open already.
2. Select the location where you want to embed the PDF. When you insert the PDF, the top-left corner of the document will be flush against the top-left corner of whatever cell you have selected.
3. Click the 'Insert' menu at the top of the screen and select 'Object' to open the 'Object' window.
4. Select 'Adobe Acrobat Document' from the 'Object type' section and then click 'OK.' Excel will display an 'Open' window.
5. Navigate to and select the PDF that you want to embed into Excel.
6. Click 'Open.' Excel will embed the PDF into your document and open the PDF in Adobe Reader.

Read more ►